# Updated Specification of Google Spreadsheet: Multiple Hyperlinks to a Cell

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")
.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.
{"extraction_info": {"found_math": true, "script_math_tex": 0, "script_math_asciimath": 0, "math_annotations": 0, "math_alttext": 0, "mathml": 0, "mathjax_tag": 0, "mathjax_inline_tex": 0, "mathjax_display_tex": 0, "mathjax_asciimath": 1, "img_math": 0, "codecogs_latex": 0, "wp_latex": 0, "mimetex.cgi": 0, "/images/math/codecogs": 0, "mathtex.cgi": 0, "katex": 0, "math-container": 0, "wp-katex-eq": 0, "align": 0, "equation": 0, "x-ck12": 0, "texerror": 0, "math_score": 0.5449589490890503, "perplexity": 2820.53718054402}, "config": {"markdown_headings": true, "markdown_code": true, "boilerplate_config": {"ratio_threshold": 0.3, "absolute_threshold": 10, "end_threshold": 15, "enable": true}, "remove_buttons": true, "remove_image_figures": true, "remove_link_clusters": true, "table_config": {"min_rows": 2, "min_cols": 3, "format": "plain"}, "remove_chinese": true, "remove_edit_buttons": true, "extract_latex": true}, "warc_path": "s3://commoncrawl/crawl-data/CC-MAIN-2023-06/segments/1674764499871.68/warc/CC-MAIN-20230131122916-20230131152916-00237.warc.gz"}