How to add a link to a spreadsheet cell using Google Apps Script

Question

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?

Guilherme Tod · Answer 1 · 2021-04-29T09:39:16.543

22

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.

edited Apr 29 '21 at 09:39

answered Jul 14 '20 at 20:17

Guilherme Tod

301
4
6

I get a compiler error if I use `const richText`, as opposed to `var richText`. – robev Feb 14 '21 at 09:59
2

I just tested this solution using `const` and it works perfectly. Thanks @Guilherme – macorreag Apr 16 '21 at 00:45

score 19 · Accepted Answer · answered May 27 '16 at 16:18

19

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")');

answered May 27 '16 at 16:18

Gerneio

1,290
10
13

4

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 – iamyojimbo Mar 07 '19 at 08:50
1

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 – JJLL Jan 18 '20 at 09:14
@iamyojimbo since June 2020 have added a functionality to solve this check the function of Guilherme Tod – macorreag Apr 16 '21 at 00:55

score 5 · Answer 3 · answered Apr 10 '20 at 19:51

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 */
}

How to add a link to a spreadsheet cell using Google Apps Script

3 Answers3

Linked