20

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.

enter image description here

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

Kos
  • 4,890
  • 9
  • 38
  • 42
Irfan Alam
  • 476
  • 3
  • 9
  • 25

4 Answers4

22

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

Community
  • 1
  • 1
ask2rg
  • 262
  • 2
  • 7
11

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:

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

Tanaike
  • 181,128
  • 11
  • 97
  • 165
  • 2
    This is what I wanted. But it shouldn't be a part of Advance API. There should have been some method in Class Range of Spreadsheet to get this easily. Anyway, Thanks! – Irfan Alam Dec 20 '18 at 10:12
  • 1
    @Irfan Alam Thank you for replying. I'm glad your issue was resolved. When I found the methods for this situation at Class SpreadsheetApp, I would like to add it. – Tanaike Dec 21 '18 at 00:35
  • @IrfanAlam check [another answer](https://stackoverflow.com/a/61921034/555121) inspired by Tanaike's another snippet that uses `SpreadsheetApp` – Kos Aug 25 '21 at 16:56
1

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

Mann1ng
  • 43
  • 6
-1

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) ;
Lexcel Atmadata
  • 83
  • 4