Skip to content

Instantly share code, notes, and snippets.

@tanaikech

tanaikech/submit.md

Last active Sep 30, 2020
Embed
What would you like to do?
Updated Specification of Google Spreadsheet: Multiple Hyperlinks to a Cell

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

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.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.