Apps Script: how to get hyperlink from a cell wher

问题:

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?

回答1:

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.