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

问题描述

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

在此之前，如果您将一个单元格链接为值"X".它被转换为公式=HYPERLINK(*link*,’X’)

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

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

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

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

解决方案

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

因此，作为一个示例案例，它假定如下.

• X的文本放在单元格"A1"中.
• 此单元格已手动链接到类似https://www.google.com的URL.

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

示例脚本:

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与每个链接链接.

  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时，您可以使用以下示例脚本.

  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月13日更新:

通过 2020年6月12日上的更新， getLinkUrl() 和 解决方案

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.

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

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.

Sample script:

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

• In this case, the URL is linked to whole text in a URL. So above script can be used.

Note:

• 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);
  

• 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);
  

References:

• getRichTextValue()
• getRichTextValues()
• Class RichTextValue

Updated at June 13, 2020:

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

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