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?
To add a hyperlink in a document use Body. appendParagraph with setLinkUrl, then merge. let doc = DocumentApp. create("My Document"); let body = doc.
You can link to any sheet in the document, any named ranges, or to a specific cell/range. Click on 'Select a range of cells to link'.
You do the proper thing - work on an array of the Range's values - as opposed to repeatedly call over the interface via getCell or similar just to check the value. To reference the cell whose value is values[r][c] , note that the Javascript array is 0-base and the Range is 1-base indexed. So A1 = [0][0].
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.
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")');
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 */
}
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With