Updated Specification of Google Spreadsheet: Multiple Hyperlinks to a Cell

submit.md

Updated Specification of Google Spreadsheet: Multiple Hyperlinks to a Cell

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

• getRichTextValue()
• getRichTextValues()
• Class RichTextValueBuilder
• Class RichTextValue
• How to extract the link from a cell now that links are not reflected as HYPERLINK?
  • I have answered above to this question.

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.