Skip to content

Instantly share code, notes, and snippets.

@tanaikech
Last active February 2, 2024 09:54
Show Gist options
  • Save tanaikech/d39b4b5ccc5a1d50f5b8b75febd807a6 to your computer and use it in GitHub Desktop.
Save tanaikech/d39b4b5ccc5a1d50f5b8b75febd807a6 to your computer and use it in GitHub Desktop.
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