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

google-apps-script, google-sheets

Solution

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.

Problem

"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)`

Original source