It is possible to add a "link" to a cell in a google spreadsheet through the user interface. I want to do the same using a Google Apps Script. I was expecting there to be a method on the Range class for this but cannot see one. Does anyone know how to do this?
14
votes
3 Answers
12
votes
RANGE.setFormula plus HYPERLINK Formula should get you what you want.
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getSheets()[0];
var cell = sheet.getRange("B5");
cell.setFormula('=HYPERLINK("http://www.google.com/","Google")');
13
votes
As of June 2020, there's the option to set the rich text link url. So now you can do the following:
const sheet = SpreadsheetApp.getActiveSheet();
const rangeToBeLinked = sheet.getRange('A2');
const rangeToAddLink = sheet.getRange('D32')
const richText = SpreadsheetApp.newRichTextValue()
.setText('Click to go to ' + rangeToBeLinked.getA1Notation())
.setLinkUrl('#gid=' + sheet.getSheetId() + '&range=' + 'A' + rangeToBeLinked.getRow())
.build();
rangeToAddLink.setRichTextValue(richText);
The result is a cell with a text that contains a link to the web that you establish. Very different from the HYPERLINK function that ultimately leaves a formula inside the cell that allows redirecting to another page.
2
votes
I ran into this problem today. Here is my solution. This is ES6, so you need to be running the new V8 Engine for Apps Script. This function is specifically for a selected range of cells.
function hyperlink(){
var activeSheet = SpreadsheetApp.getActiveSheet();
var a1 = activeSheet.getSelection().getActiveRange().getA1Notation();
var values = activeSheet.getRange(a1).getValues();
var hyperVal= values.map(row=> row.map(col=> `=HYPERLINK("${col}","${col}")` ));
activeSheet.getRange(a1).setValues(hyperVal);
activeSheet.getRange(a1).setShowHyperlink(true); /* I initially just tried doing this, but without setting the HYPERLINK formula, it did nothing */
}