Apps Script: how to get hyperlink from a cell where there is no formula

Hey all,

I hope this helps you save some dev time, as it was a rather slippery one to pin down...

This custom function will take all hyperlinks in a Google Sheets cell, and return them as text formatted based on the second parameter as either [JSON|HTML|NAMES_ONLY|URLS_ONLY].

Parameters:

cellRef : You must provide an A1 style cell reference to a cell. Hint: To do this within a cell without hard-coding a string reference, you can use the CELL function. eg: "=linksToTEXT(CELL("address",C3))"

style : Defines the formatting of the output string. Valid arguments are : [JSON|HTML|NAMES_ONLY|URLS_ONLY].

Sample Script

/** 
 * Custom Google Sheet Function to convert rich-text 
 * links into Readable links.
 * Author: Isaac Dart ; 2022-01-25
 * 
 * Params
 *  cellRef : You must provide an A1 style cell reference to a cell. 
 *            Hint: To do this within a cell without hard-coding
 *            a string reference, you can use the CELL function. 
 *            eg: "=linksToTEXT(CELL("address",C3))"
 * 
 *  style   : Defines the formatting of the output string.
 *            Valid arguments are : [JSON|HTML|NAMES_ONLY|URLS_ONLY].
 * 
 */

function convertCellLinks(cellRef = "H2", style = "JSON") {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = SpreadsheetApp.getActiveSheet();
  var cell = sheet.getRange(cellRef).getCell(1,1);
  var runs = cell.getRichTextValue().getRuns();
  var ret = "";
  var lf =  String.fromCharCode(10); 
  runs.map(r => {
      var _url = r.getLinkUrl();
      var _text = r.getText();
      if (_url !== null && _text !== null) {
        _url = _url.trim(); _text = _text.trim();
        if (_url.length > 0 && _text.length > 0) {
          switch(style.toUpperCase()) {
            case "HTML": ret += '<a href="' + _url + '">' + _text + '}</a>' + lf; break;
            case "TEXT": ret += _text + ' : "' + _url + '"' + lf; break;
            case "NAMES_ONLY" : ret += _text + lf; break;
            case "URLS_ONLY" : ret += _url + lf; break;
            //JSON default : ...
            default: ret +=  (ret.length>0?(','+ lf): '') +'{name : "' + _text + '", url : "' + _url + '"}' ; break; 
          }
          ret += lf;
        }
      }
  });
  if (style.toUpperCase() == "JSON") ret = '[' + ret + ']';
  //Logger.log(ret);
  return ret;
}

Cheers,

Isaac

When a cell has only one URL, you can retrieve the URL from the cell using the following simple script.

var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet1");
var url = sheet.getRange("A2").getRichTextValue().getLinkUrl(); //removed empty parentheses after getRange in line 2

Source: https://gist.github.com/tanaikech/d39b4b5ccc5a1d50f5b8b75febd807a6

When Excel file including the cells with the hyperlinks is converted to Google Spreadsheet, such situation can be also seen. In my case, I retrieve the URLs using Sheets API. A sample script is as follows. I think that there might be several solutions. So please think of this as one of them.

When you use this script, please enable Sheets API at Advanced Google Services and API console. You can see about how to enable Sheets API at here.

Sample script:

var spreadsheetId = "### spreadsheetId ###";
var res = Sheets.Spreadsheets.get(spreadsheetId, {ranges: "Sheet1!A1:A10", fields: "sheets/data/rowData/values/hyperlink"});
var sheets = res.sheets;
for (var i = 0; i < sheets.length; i++) {
  var data = sheets[i].data;
  for (var j = 0; j < data.length; j++) {
    var rowData = data[j].rowData;
    for (var k = 0; k < rowData.length; k++) {
      var values = rowData[k].values;
      for (var l = 0; l < values.length; l++) {
        Logger.log(values[l].hyperlink) // You can see the URL here.
      }
    }
  }
}

Note:

• Please set spreadsheetId.
• Sheet1!A1:A10 is a sample. Please set the range for your situation.
• In this case, each element of rowData is corresponding to the index of row. Each element of values is corresponding to the index of column.

References:

• Method: spreadsheets.get

If this was not what you want, please tell me. I would like to modify it.