David Stockdale's Scrapcode

Replace Links With URL In Active Google Sheet

A simple bit of Google Apps Script that converts all links within an active Google Sheet into full URL links instead.

This is convenient for when you need to copy and paste thousands of links out of a Google Sheet in plain text but find a large amount of your links end up as something generic like “Google” when stripped of their hyperlink formatting.

Script

First in your spreadsheet click “Extensions” and open “Apps Script”.

This will open a new Apps Script project connected to this spreadsheet.

Now input this code and save the project.

function replaceLinksWithUrls() {
  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 newText = '';
      var lastIndex = 0;

      for (var k = 0; k < text.length; ) {
        var url = richTextValue.getLinkUrl(k, k + 1);
        if (url) {
          var start = k;
          while (k < text.length && richTextValue.getLinkUrl(k, k + 1) === url) {
            k++;
          }
          newText += text.substring(lastIndex, start) + url;
          lastIndex = k;
        } else {
          k++;
        }
      }
      newText += text.substring(lastIndex);

      if (newText !== text) {
        sheet.getRange(i + 1, j + 1).setValue(newText);
      }
    }
  }
}

Be sure to run this script once before you actually test it.

This will trigger the authorisation popup where you need to give permission for your project to access your data.

Hopefully this will make someone’s google search a bit quicker.

Leave a like or a comment if it helped!

Leave a Reply