Change Colour Of All Links On Active Google Sheet

I recently found myself tasked with “fixing” the links in a Google Sheet full of rows of data copied from an excel sheet.

This turned out to be a simple problem of the links within the cells simply being the same colour and style as the strings (meaning that we had cells full of black text with black non-underlined links hidden within).

So I needed a way to change the colour of every link within the sheet all at once.

This is the simple Google Apps Script that I came up with to do it:

function formatLinks() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  var range = sheet.getDataRange();
  var richTextValues = range.getRichTextValues();

  for (var i = 0; i < richTextValues.length; i++) {
    for (var j = 0; j < richTextValues[i].length; j++) {
      var richTextValue = richTextValues[i][j];
      var text = richTextValue.getText();
      var textStyleBuilder = SpreadsheetApp.newTextStyle().setForegroundColor('#4285f4').build();
      
      // Build a new Rich Text Value with updated link colors
      var richTextBuilder = SpreadsheetApp.newRichTextValue().setText(text);
      
      for (var k = 0; k < text.length; k++) {
        var url = richTextValue.getLinkUrl(k, k + 1);
        if (url) {
          richTextBuilder.setLinkUrl(k, k + 1, url).setTextStyle(k, k + 1, textStyleBuilder);
        }
      }

      var newRichTextValue = richTextBuilder.build();
      sheet.getRange(i+1, j+1).setRichTextValue(newRichTextValue);
    }
  }
}

Obviously this only effects the current active sheet, so simply run the function after clicking whichever sheet tab you want to format.