Apps Script: 如何从没有公式的单元格中获取超链接

Question

我有一个单元格中设置了超链接，但不是通过公式设置的。当点击该单元格时，在“fx”栏中仅显示值。

我在网上搜索过，但无论哪里，都是使用getFormula()来提取超链接的信息。

但是在我的情况下，根本没有设置任何公式。

我可以看到超链接，就像您在图像中看到的那样，但它并不存在于“公式/fx”栏中。

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

- Irfan Alam

Answer 1

当单元格只有一个URL时，您可以使用以下简单脚本从单元格中检索URL。

var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet1");
var url = sheet.getRange("A2").getRichTextValue().getLinkUrl(); //removed empty parentheses after getRange in line 2

来源：https://gist.github.com/tanaikech/d39b4b5ccc5a1d50f5b8b75febd807a6

- ask2rg

Answer 2

将包含超链接单元格的Excel文件转换为Google电子表格时，也可能出现此类情况。在我的情况下，我使用Sheets API检索URL。以下是一个示例脚本。我认为可能会有几种解决方案。所以请把这个作为其中之一。

当您使用此脚本时，请在高级Google服务和API控制台中启用Sheets API。您可以在这里了解如何启用Sheets API。

样本脚本：

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的每个元素都对应于列索引。

参考资料：

• 方法：spreadsheets.get

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

- Tanaike

---

这正是我想要的。但它不应该是Advance API的一部分。在电子表格的范围类中应该有一些方法可以轻松地获得它。 不管怎样，谢谢！ - Irfan Alam

@Irfan Alam 谢谢您的回复。我很高兴您的问题已经解决。当我找到这种情况下 Class SpreadsheetApp 的方法时，我会添加它。 - Tanaike

@IrfanAlam，请查看另一个答案，它受到Tanaike的另一个代码片段的启发，该代码片段使用SpreadsheetApp。 - Kos

Answer 3

大家好，

我希望这篇文章能帮助你节省一些开发时间，因为这个问题比较棘手...

这个自定义函数将获取 Google Sheets 单元格中的所有超链接，并根据第二个参数返回格式化后的文本，格式可以是 [JSON|HTML|NAMES_ONLY|URLS_ONLY]。

参数：

cellRef：必须提供一个 A1 样式的单元格引用。 提示：在不硬编码字符串引用的情况下，在单元格内执行此操作，您可以使用 CELL 函数。 例如："=linksToTEXT(CELL("address",C3))"

style：定义输出字符串的格式。 有效的参数是：[JSON|HTML|NAMES_ONLY|URLS_ONLY]。

示例脚本

/** 
 * 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;
}

祝福您，

艾萨克

- Mann1ng

Answer 4

我尝试了解决方案2：

var urls = sheet.getRange('A1:A10').getRichTextValues().map( r => r[0].getLinkUrl() ) ;

我得到了一些链接，但大多数都为空。 我做了解决方案1的缩短版，它返回了所有链接。

  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