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

Question

I have a sheet where hyperlink is set in cell, but not through formula. When clicked on the cell, in "fx" bar it only shows the value.

I searched on web but everywhere, the info is to extract hyperlink by using getFormula().

But in my case there is no formula set at all.

I can see hyperlink as you can see in image, but it's not there in "formula/fx" bar.

How to get hyperlink of that cell using Apps Script or any formula?

Answer 1

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

Answer 2

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

Answer 3

I tried solution 2:

var urls = sheet.getRange('A1:A10').getRichTextValues().map( r => r[0].getLinkUrl() ) ;

I got some links, but most of them yielded null. I made a shorter version of solution 1, which yielded all the links.

  const id = SpreadsheetApp.getActive().getId() ;
  let res = Sheets.Spreadsheets.get(id,
     {ranges: "Sheet1!A1:A10", fields: "sheets/data/rowData/values/hyperlink"});
  var urls = res.sheets[0].data[0].rowData.map(r => r.values[0].hyperlink) ;