既然链接没有反映为超链接，如何从单元格中提取链接? [英] How to extract the link from a cell now that links are not reflected as HYPERLINK?

问题描述

插入链接"不再产生 =HYPERLINK('','').

"Insert Link" is not producing a =HYPERLINK(’’,’’) anymore.

之前，如果您使用值X"链接单元格.转换成公式=HYPERLINK(*link*,'X')

Before, if you linked a cell with a value ‘X’. It was converted into the formula =HYPERLINK(*link*,’X’)

两天前插入链接"改变了.

Two days ago "Insert Link" changed.

现在单元格的内容保持不变，只是加了下划线.

Now the content of the cell remains the same, it is just underlined.

既然单元格的值和公式都不包含此信息，我如何使用脚本从单元格中提取链接?

Using a script, how can I extract the link from a Cell now that neither its value nor its formula contains this information?

我搜索了文档，但我能找到的与链接相关的唯一方法是 setShowHyperlink(showHyperlink)

I searched the documentation but the only method related to links that I was able to find was setShowHyperlink(showHyperlink)

推荐答案

我可以确认你的情况.在这种情况下，似乎可以从 RichTextValue 对象中检索超链接.即，我认为规范已更改为使用 RichTextValue 为文本提供超链接.

I could confirm your situation. In this case, it seems that the hyperlink can be retrieved from RichTextValue object. Namely, I thought that the specification was changed to that the hyperlink is given to the text using RichTextValue.

作为一个示例，假设如下.

So as a sample case, it supposes as follows.

• X 的文本放在单元格A1"中.
• 此单元格手动链接到一个 URL，例如 https://www.google.com.

• A text of X is put in a cell "A1".
• This cell is manually linked to a URL like https://www.google.com.

在这种情况下，单元格没有 =HYPERLINK("https://www.google.com","X").用于从这种情况中检索 URL 的示例脚本如下.

In this case, the cell has no =HYPERLINK("https://www.google.com","X"). The sample script for retrieving the URL from this situation is as follows.

var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet1");
var url = sheet.getRange("A1").getRichTextValue().getLinkUrl();
console.log(url);

• 在这种情况下，URL 链接到 URL 中的整个文本.所以上面的脚本可以使用.

• 在当前阶段，可以将多个超链接添加到一个单元格中的文本中.例如，当将 2 个 URL 放入单元格中的文本时，您可以使用以下示例脚本.在本示例中，将url1, url2 的文本放入单元格A1"中，url1 和url2 与每个链接链接.

• In the current stage, the multiple hyperlinks can be added to the texts in one cell. For example, when 2 URLs are put to the text in a cell, you can use the following sample script. In this sample, a text of url1, url2 is put to a cell "A1", and url1 and url2 are linked with each link.

var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet1");
var RichTextValue = SpreadsheetApp.newRichTextValue()
  .setText("url1, url2")
  .setLinkUrl(0, 4, "https://url1/")
  .setLinkUrl(6, 10, "https://url2/")
  .build();
sheet.getRange("A1").setRichTextValue(RichTextValue);

• 当从单元格中的文本中检索到多个 URL 时，您可以使用以下示例脚本.

• When the multiple URLs are retrieved from the text in a cell, you can use the following sample script.

  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet1");
  var range = sheet.getRange("A1");
  var RichTextValue = range.getRichTextValue().getRuns();
  var res = RichTextValue.reduce((ar, e) => {
    var url = e.getLinkUrl();
    if (url) ar.push(url);
    return ar;
  }, []);
  console.log(res);
  

• getRichTextValue()
• getRichTextValues()
• RichTextValue 类

根据 2020 年 6 月 12 日的更新，getLinkUrl() 和 setLinkUrl(linkUrl) 已添加到官方文档中.

By the update at June 12, 2020, the documents of getLinkUrl() and setLinkUrl(linkUrl) were added to the official documents.

这篇关于既然链接没有反映为超链接，如何从单元格中提取链接?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！