How to extract the link from a cell now that links are not reflected as HYPERLINK?

Question

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

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?

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

Answer 1

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.