如何从链接中提取URL（链接特征，而非HYPERLINK公式）在谷歌表格中？

Question

Google Sheets 允许以两种方式指定超链接：

• 通过使用 HYPERLINK公式/函数，例如：=HYPERLINK("http://example.com/", "Example.com")
• 通过使用“链接”功能 - 插入»插入链接

网络上有很多关于从第一个选项 - HYPERLINK公式中提取URL的解决方案，但我没有找到从第二个选项中提取它的任何方法。

示例表格

- Pavel 'Strajk' Dolecek

---

我认为这不可能用表格公式完成，但如果你感兴趣的话，可以通过一个简单的Apps Script自定义函数来实现。 - ziganotschka

@ziganotschka AppsScript会很棒！ - Pavel 'Strajk' Dolecek

Answer 1

如何使用 Apps Script 提取用 "Insert » Insert Link" 插入的链接 URL

• Apps Script 有一个名为 https://developers.google.com/apps-script/reference/spreadsheet/rich-text-value 的类，可以帮助您检索包含在单元格中的不仅是纯文本，还包括其属性，例如链接 URL。
• 要访问此属性，请使用方法getRichTextValue()结合使用getLinkUrl()

示例：在自定义函数中检索链接 URL：

function getLink(range){
  var link = SpreadsheetApp.getActive().getActiveSheet().getRange(range).getRichTextValue().getLinkUrl();
  return link;
}

编写并保存此代码后，只需从任何单元格调用它，将带有链接URL的单元格引用作为参数传递。

重要提示：

由于应该传递单元格的引用而不是值，因此您需要将A1表示法放在引号中。

示例：

=getLink("A1")

- ziganotschka

---

我已经在多个Google表格工作簿中尝试过这个方法，并使用了许多不同的链接。有时它有效，有时则无效。当它无效时，单元格会显示错误信息：“TypeError: Cannot read property 'getLinkUrl' of null (line 2)”。我无法弄清楚为什么会出现这种情况。但每次我打开一个新的工作簿时，它都能正常运行。 - Tony M

如果您直接从编辑器运行该函数或者不带参数调用它，那么这就是我所期望的错误信息——该函数需要一个单元格的A1表示法才能正常工作。无论如何，请尝试使用SpreadsheetApp.openById("XXX").getSheetByName("YYY").getRange(range).getRichTextValue().getLinkUrl();来避免歧义。 - ziganotschka

@ziganotschka，它起作用了！非常感谢你！我已经按照你的解决方案在我的问题所提到的示例电子表格中添加了一个新工作表。 - Pavel 'Strajk' Dolecek

我在尝试在脚本编辑器中运行此代码时一直收到“错误：发生未知错误，请稍后重试。”的提示。有任何想法为什么会出现这种情况吗？如果有影响的话，电子表格API是开启的。 - yys

Answer 2

我们可以改进ziganotschka的解决方案，避免使用引号。

在B1中输入以下内容（假设带有嵌入链接的文本在A1中）：

=getLink(ADDRESS(row(),column(A1)))

这样就可以将其拖动到整个列以快速获取数据。

- Sigurd

Answer 3

如果希望使用正常公式而不需要引号，

function GetURL(input) {
  var formula = SpreadsheetApp.getActiveRange().getFormula();
  var rx = /=geturl\((.*)\)/gi;
  var rng_txt = rx.exec(formula)[1]
  var rng = SpreadsheetApp.getActiveSheet().getRange(rng_txt);
  return rng.getRichTextValue().getLinkUrl()
}

- Rahul