13
votes

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?

3

3 Answers

12
votes

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

9
votes

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.

-1
votes

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) ;