如何使用Google Apps Script从Google电子表格单元格中获取URL？

Question

我有一个从Excel复制的谷歌电子表格，它出现了一些问题。 这是我的文档: https://docs.google.com/spreadsheets/d/1Ok_phu5OXtvKHLj3MLa7N3WV2qBdMWRz8dLHnTqjHrc/edit?usp=sharing 此外，还有一个带有超链接但没有公式的单元格，请参见以下图片： 这是我的代码。

function myFunction() {
  cel = SpreadsheetApp.getActiveSpreadsheet().getActiveCell()
  Logger.log(cel.getFormula())
  Logger.log(cel.getValue())
}

这是我的日志：

[16-10-05 13:39:59:628 EEST] 
[16-10-05 13:39:59:629 EEST] RU: VTB Arena - Park and Hotel

我需要获取一个URL (https://na3.salesforce.com/0065000000a1b8p)，我该怎么做？

- GhostKU

---

相关：https://dev59.com/KWYr5IYBdhLWcg3wOX26 - Rubén

这个回答解决了你的问题吗？Apps Script：如何从没有公式的单元格中获取超链接 - Kos

Answer 1

目前，Google Apps Script的电子表格服务无法获取单元格丰富文本内容的属性，只能使用getValue()方法获取纯文本。其他方法如getFormula()、getNote()能够获取相应的单元格属性，但它们都不包括链接的URL。

在Google Sheets上的解决方法是手动获取URL。一种编程的替代方法是，在将文件转换为Google Sheets格式之前，在Excel文件中使用VBA获取超链接。有关详细信息，请参见此答案以及此问题。

- Rubén

Answer 2

我认为最好将Google电子表格发布到网络上，然后使用BS4解析HTML以获取URL。

- Kambrose

Answer 3

以下是我用于从单元格中获取链接的解决方法。显然，当您使用Ctrl + K添加链接时，它们会作为富文本而不是公式添加。我在Google表格中使用下面的函数将具有链接的富文本字段转换为HTML。如果字段中有链接，则返回链接的HTML代码。如果没有链接，则只返回传递的文本。当然，这可以更加优雅地完成...我使用了很多变量，以使其更容易理解正在发生什么。

//get link from a cell given by A1 notation
function getHtmlfromRichTextRange(theRange){
  var sheet = SpreadsheetApp.getActiveSpreadsheet();
  var range = sheet.getRange(theRange);
  var cell = range; 
  var cellValue = cell.getRichTextValue();
  var cellText = cell.getValue();
  var theThing = cellValue.getLinkUrl();

  if (theThing == null){
    return cellText;
  } else {
    return '<a href="'+theThing+'">'+cellText+'</a>'; 
  }
}

在电子表格中，您可以执行以下操作（请参见图像1）：

1. 在单元格A1中输入“链接到苹果”
2. 突出显示单元格A1，按ctrl+k添加超链接到http://www.apple.com
3. 在单元格B1中输入A1
4. 在单元格C1中输入公式=getHtmlfromRichTextRange(B1)

结果，您应该在单元格C1中看到值

<a href="http://www.apple.com/">链接到苹果</a>

在使用此示例的过程中，我注意到了一些事情：

• 如果C1中的函数只返回URL而不是带有一些字符串文本的URL，那么Google表格也会将C1转换为RichText。由于我使用它来创建HTML电子邮件，所以我让函数返回链接的HTML代码。
• 我本可以简化并在引号中传递字符串"A1"到函数中。我试图找到一种方法，使C1中的公式在更新A1中的richtext链接时更新。我的想法是，如果A1更改（例如，URL更改），则C1中的函数结果将自动更新。但实际上并不会。我没有继续追求。上面写的对我有用。
• 如果单元格中有一些文本包含超链接，而另一些文本不包含（例如，超链接只是单元格中文本的一部分），那么这段代码将其作为纯文本而不是URL返回。

我在AppScript中按原样使用该函数循环遍历包含链接的范围。在循环内，我将单元格的A1表示法传递给函数，其中一些单元格包含链接。它非常有效地工作，并节省了大量时间！

我相信有一种更简单的方法。通常情况下，我会先用较难的方式完成任务，因此以后就不需要再这样做了。

- Will in RVA

---

对我有用！谢谢。不过，如果没有奇怪的重定向会更好，但正如你所说，它能够工作，并且绕过那个奇怪的部分也不难。 - Steve Midgley

Answer 4

谢谢您提出这个问题！我自己也一直在寻找这个答案！我将Facebook好友列表复制到了电子表格中。它们被复制为富文本，意味着它们包含一个链接，但在=超文本格式中不可用。

我搜索了很久，似乎确定没有简单的方法可以解决这个问题。

然后我偶然发现了它。

只需选择包含富文本的单元格（假设您想在悬停时获取它包含的链接），然后按Command-K进行编辑链接。

它会弹出一个窗口，在顶部字段中输入单元格文本，在底部字段中输入URL。

无需输入任何内容-默认情况下已经有了。所以只需按[Enter]，BAM！它现在已经格式化为=HYPERLINK("URL","Text")

但是我有成百上千个这样的要转换，并且想找到更简单的方法。

于是我偶然发现了AppleScript（即使我正在完全转向苹果，但今天我从未使用过它）。

我创建了一个简单的AppleScript：

告诉“Google Chrome”应用程序激活 延迟0.5秒 告诉“系统事件” 延迟0.5秒 告诉进程“Chrome” 使用命令键输入“k” 延迟0.2秒 输入回车 延迟0.2秒 输入回车 延迟0.2秒 输入回车 延迟0.2秒 结束告诉 结束告诉

在您的电子表格中选择要编辑的单元格（假设您正在使用Google Chrome），然后运行此AppleScript。

我制作了几个脚本，所有金额都是以美元为单位。所以我有一个只运行一次的脚本，然后是5倍、10倍、20倍、50倍和100倍的脚本。

因此，对于我的143个名称列表，我刚刚运行了100倍的脚本，然后是50倍的脚本。我坐下来喝了一口咖啡，看着我的电脑快速为我完成工作。

如果这比人们想要的信息更多，那我很抱歉——这是我第一次在这里回答问题（我记得），如果我之前发现了我现在正在写的答案——那将是今年早些时候的救星！

愉快的编码！

- Honey LaBronx

Answer 5

这段代码可以从一列中获取URL。

function testForUrlGet(){

    const ss = SpreadsheetApp.getActiveSpreadsheet();
    const sheet = ss.getSheetByName("Zoom");
    const range = sheet.getRange('C1:C500');
    const values = range.getRichTextValues();

    for(i=0; i<=values.length ; i++){

     Logger.log(values[i][0].getLinkUrl());
     }

}

希望这能有所帮助，因为我自己找不到答案。

- mikeimp

Answer 6

试试这个：

var url = /"(.*?)"/.exec(cel.getFormulaR1C1())[1];
Logger.log(url);

当我手动添加链接时，它会提取URL。

请注意，我在测试中使用了以下内容：

  var dataSheet = CentralSS.getSheetByName(CENTRAL_DATAFILES_SHEETNAME);
  var range = dataSheet.getRange("E2");
  var url = /"(.*?)"/.exec(range.getFormulaR1C1())[1];

- Karl_S

---

当我运行调试并在设置变量后立即暂停时，URL包含了链接。我有什么遗漏吗？ - Karl_S

你的代码适用于带有超链接函数的公式单元格，但不适用于OP提出的情况。 - Rubén

我没有意识到右键单击将链接与文本关联会创建一个公式。现在我知道了。你是正确的，我能够从网站复制链接并粘贴它而不获取URL。但我无法从他的电子表格中复制带有链接的内容。对于错过了粘贴链接会更改单元格为公式这一点，我深感抱歉。 - Karl_S