setCellLink(range, text, url) writes a real clickable link with RichTextValue (newRichTextValue, setLinkUrl), not a =HYPERLINK() formula. It is the write-side partner to Get Cell Links.
When to use
- Putting an "Open ticket" label in a cell that should still have a URL
- Building a column of links from a script without leaving formulas behind
- Writing a link that
getCellLinkscan read back later
How to use this snippet
/**
* Write a clickable rich-text link into a single cell.
* This is not a HYPERLINK formula; the formula bar shows the label.
* If `text` is empty, the URL is used as the label.
*
* @param {GoogleAppsScript.Spreadsheet.Range} range a single cell
* @param {string} text
* @param {string} url
* @return {GoogleAppsScript.Spreadsheet.Range}
*/
function setCellLink(range, text, url) {
if (!range) throw new Error('setCellLink: range required');
var u = String(url == null ? '' : url).trim();
if (!u) throw new Error('setCellLink: url required');
if (range.getNumRows() !== 1 || range.getNumColumns() !== 1) {
throw new Error('setCellLink: range must be a single cell');
}
var t = String(text == null || text === '' ? u : text);
var rich = SpreadsheetApp.newRichTextValue().setText(t).setLinkUrl(u).build();
range.setRichTextValue(rich);
return range;
}
Example
function linkTicketCell() {
var cell = SpreadsheetApp.getActive().getRange('Tickets!C2');
setCellLink(cell, 'Open ticket', 'https://example.com/t/1842');
}
Notes
- The range must be a single cell. Loop if you are writing a column.
- This overwrites the cell's value, formula, and existing rich text.
getValues()afterwards returns the label, not the URL — use Get Cell Links to read the href. - A blank
textbecomes the URL so the cell is not an empty link. A blank URL throws. - URLs that are only typed as plain text (no link) are a different thing; this helper always creates a real link.
Happy Coding!
