Updated Specification of Google Spreadsheet: Multiple Hyperlinks to a Cell

GasTips
Google Apps Script / Spreadsheet / hyperlink

Gists

Recently, it seems that the specification of Google Spreadsheet was updated. Before this, when a cell has only one hyperlink. In this case, the hyperlink was given to a cell using =HYPERLINK("http://www.google.com/", "Google") as following figure.

But by the recent update, a cell got to be able to have multiple hyperlinks as following figure. In this case, the hyperlinks are set by the RichTextValue object.

In this report, I would like to introduce the method for setting and retrieving the multiple URLs for a cell.

Set multiple URLs to a cell

In this section, I would like to introduce the method for setting the multiple URLs to a cell using the sample script. As the sample situation, above figure is achieved using a sample script.

Sample script

const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet1");
const RichTextValue = SpreadsheetApp.newRichTextValue()
  .setText("url1 and url2")
  .setLinkUrl(0, 4, "http://www.google.com/")
  .setLinkUrl(9, 13, "https://tanaikech.github.io/")
  .build();
sheet.getRange("A1").setRichTextValue(RichTextValue);
  • In this script, at first, the RichTextValue object is created. Then, it is put to a cell.

Get multiple URLs from a cell

In this section, I would like to introduce the method for retrieving the multiple URLs from a cell using the sample script. As the sample situation, the URLs are retrieved from above figure.

Sample script

const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet1");
const range = sheet.getRange("A1");
const RichTextValue = range.getRichTextValue().getRuns();
const res = RichTextValue.reduce((ar, e) => {
  const url = e.getLinkUrl();
  if (url) ar.push(url);
  return ar;
}, []);
console.log(res);
  • In this script, at first, the RichTextValue object is retrieved from a cell. Then, the URLs are retrieved from the object.

Note

  • In this case, when =HYPERLINK("http://www.google.com/", "Google") is put to a cell, the URL can be retrieved using above script. Of course, you can also retrieve this using getFormula.

  • When all text in a cell has an URL, you can also retrieved the URL from the cell using the following simple script.

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

  • In the current stage (May 15, 2020), at the official document, there are no methods of setLinkUrl and getLinkUrl, in Class RichTextValueBuilder and Class RichTextValue. But I believe that those will be added in the near future update.

References

 Share!