Add links to a cell in Google Sheets using Apps Script
In this tutorial, you'll learn how to add links into a cell in Google Sheets using Google Apps Script. When inserting links in cells, you have to decide whether all of the text in the cell should become a link or only a portion of the text.
To add links to a cell in Google Sheets using Apps Script, we need to first create a Rich Text Value and then apply this value to the cell's range.
The function linkCellContents() below inserts the link More information in cell Link!A2.
Note
The code assumes that there is a sheet called Link in your Google Sheets spreadsheet. If your sheet has a different name, please replace "Link" in Link!A2 with the name of your sheet.
//OnlyCurrentDoc
function linkCellContents() {
var range = SpreadsheetApp.getActive().getRange("Link!A2");
var richValue = SpreadsheetApp.newRichTextValue()
.setText("More information")
.setLinkUrl("https://example.com")
.build();
range.setRichTextValue(richValue);
}Understanding the builder pattern
The code that creates the rich text value uses a builder pattern. The method newRichTextValue() returns a RichTextValue builder. This means that it returns an object that you can then customize. When you're done customizing it, you need to call the build() method of the object to create the rich text value.
var richValue = SpreadsheetApp.newRichTextValue()
.setText("More information")
.setLinkUrl("https://example.com")
.build();So, the method SpreadsheetApp.newRichTextValue() returns an object that you can then configure.
SpreadsheetApp.newRichTextValue()Then you customize this object. Here, we configure it by setting the text and the link URL.
.setText("More information")
.setLinkUrl("https://example.com")Finally, we create the rich text value using the build() method.
.build();The variable richValue will then contain the rich text value.
When you run the above function, a link will be inserted into cell A2.
You can also insert multiple links into a cell or link only a portion of the text by setting start and end offsets when configuring the link URL. When you set a start and end offset, only the text in between these positions will be linked.
The function insertMultipleLinksInCell() below inserts two links in the cell Link!A5 by specifying offsets. The resulting text has two links: article 1 which links to https://ex1.example.com and article 2 which links to https://ex2.example.com.
function insertMultipleLinksInCell() {
var range = SpreadsheetApp.getActive().getRange("Link!A5");
var richValue = SpreadsheetApp.newRichTextValue()
.setText("For more information, see article 1 and article 2.")
.setLinkUrl(26, 35, "https://ex1.example.com")
.setLinkUrl(40, 49, "https://ex2.example.com")
.build();
range.setRichTextValue(richValue);
}
Conclusion
In this tutorial, you learned how to add links to a cell in Google Sheets using Google Apps Script. To add a link, you need to create a RichTextValue object and then set this value to the cell's range object. You can insert multiple links into a cell by specifying start and end offsets when setting the linkUrl() of the rich text value. Only the text in between these offsets will be linked.
Master Google Sheets Automation
I'd appreciate any feedback you can give me regarding this post.
Was it useful? Are there any errors or was something confusing? Would you like me to write a post about a related topic? Any other feedback is also welcome. Thank you so much!