Apps脚本：如何从没有公式的单元格中获取超链接 [英] Apps Script: how to get hyperlink from a cell where there is no formula

问题描述

我有一个工作表，其中在单元格中设置了超链接，但不是通过公式。当在单元格上单击时， fx栏中仅显示该值。

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.

我在网上搜索，但到处都是，信息是使用<$ c提取超链接$ c> getFormula（）。

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.

我可以看到超链接在图像中，但是在公式/ fx栏中不存在。

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

如何使用Apps脚本或任何公式获取该单元格的超链接？

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

推荐答案

当Excel文件包括具有超链接的单元格将转换为Google Spreadsheet，这种情况也可以看到。就我而言，我使用Sheets API检索网址。示例脚本如下。我认为可能有几种解决方案。因此，请考虑其中之一。

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.

使用此脚本时，请在高级Google服务和API控制台上启用Sheets API。您可以在此处中了解如何启用表格API。 >。

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.

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.
      }
    }
  }
}

注意：

• 请设置 spreadsheetId 。


• Sheet1！A1：A10 是一个示例。请根据您的情况设置范围。


• 在这种情况下， rowData 的每个元素都对应于行的索引。 值的每个元素对应于列的索引。



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.


• 方法：sheets.get


• Method: spreadsheets.get

如果这不是您想要的，请告诉我。我想修改它。

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

这篇关于Apps脚本：如何从没有公式的单元格中获取超链接的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！