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?
Add a comment
|
5 Answers
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")');
3 Comments
iamyojimbo
Is there no way to add this without using a formula? When adding a hyperlink via the UI, or pasting from the web, it's still hyperlinked but with no formula
JJLL
That doesn't seem possible at the moment. If you record "insert link" via macro recording and examine the script in the macro, hyperlink action is not recorded. That to me says UI's "insert hyperlink" is not exposed to Google Script
macorreag
@iamyojimbo since June 2020 have added a functionality to solve this check the function of Guilherme Tod
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 */
}
Comments
You can create a generic app script that transforms the highlighted range of cells from text to hyperlinks.
Compared to the other solutions, this one allows you create the script once and use it anytime you needed it by applying it to the selected range.
Extensions => App Scripts
Clear the placeholder code and put this
function convertToHyperlinks() {
var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
var range = sheet.getActiveRange();
var values = range.getValues();
// Loop through each cell in the selected range
for (var i = 0; i < values.length; i++) {
for (var j = 0; j < values[i].length; j++) {
// Convert cell value to string and trim any whitespace
var url = values[i][j].toString().trim();
if (url) {
// Get the current cell
var cell = range.getCell(i + 1, j + 1);
// Create a RichTextValue with the URL as both text and hyperlink
var richText = SpreadsheetApp.newRichTextValue()
.setText(url)
.setLinkUrl(url)
.build();
// Set the RichTextValue to the cell
cell.setRichTextValue(richText);
}
}
}
}
Save the app script (ctrl+s)
Extensions => Macros => Import Macro
Add the function that was created, give all the permissions needed
Select the cells you want to convert from text to hyperlinks
Extensions => Macros => convertToHyperlinks
Comments
Create a function to generate a RichTextValue from text and url:
function link(text, url) {
let richTextValue = SpreadsheetApp.newRichTextValue()
.setText(text)
.setLinkUrl(url)
.build();
return richTextValue;
}
Then use like this:
sheet.getRange('A1').setRichTextValue( link('text', 'url') );
