CODEWITHSUNDEEP
google-apps-scriptgoogle-sheets
dmb_acunim
dmb_acunim

Reputation: 427

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

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?

Upvotes: 28

Views: 30902

Answers (5)

Andrea Mauro
Andrea Mauro

Reputation: 842

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

  1. Save the app script (ctrl+s)

  2. Extensions => Macros => Import Macro

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

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

  5. Extensions => Macros => convertToHyperlinks

Upvotes: 0

vstepaniuk
vstepaniuk

Reputation: 868

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

Upvotes: 0

Guilherme Tod
Guilherme Tod

Reputation: 381

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.

Upvotes: 28

Andre Bradshaw
Andre Bradshaw

Reputation: 119

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

Upvotes: 5

Gerneio
Gerneio

Reputation: 1340

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

Upvotes: 22

Related Questions