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?

Finkle

Answer 1

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

Sumba

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 – Serapis

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 – Electrophoresis

@Serapis since June 2020 have added a functionality to solve this check the function of Guilherme Tod – Rating

Answer 2

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.

Hartebeest

I get a compiler error if I use const richText, as opposed to var richText. – Tyre

I just tested this solution using const and it works perfectly. Thanks @Guilherme – Rating

Answer 3

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

Mcleroy

Answer 4

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

Feral

Answer 5

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.

1. Extensions => App Scripts

2. 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);
            }
        }
    }
}

3. Save the app script (ctrl+s)

4. Extensions => Macros => Import Macro

5. Add the function that was created, give all the permissions needed

6. Select the cells you want to convert from text to hyperlinks

7. Extensions => Macros => convertToHyperlinks

Jewelry