Apps Script:如何从没有公式的单元格中获取超链接 [英] 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.

我在网上搜索过，但到处都是，信息是使用 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 电子表格时，也会出现这种情况.就我而言，我使用 Sheets API 检索 URL.示例脚本如下.我认为可能有几种解决方案.因此，请将此视为其中之一.

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.您可以在此处了解如何启用 Sheets 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 的每个元素都对应于行的索引.values 的每个元素都对应于列的索引.

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.
• 方法:电子表格.get

如果这不是您想要的，请告诉我.我想修改一下.

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

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