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 usinggetFormula. -
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
setLinkUrlandgetLinkUrl, 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.