CODEWITHSUNDEEP
javascriptgoogle-apps-script
Zach
Zach

Reputation: 35

How to add a variable to the hyperlink function using Google Apps Script

I am using Google Apps Script to build custom reports with Google Sheets. One of my columns is a column of numbers and I want to make each number a hyperlink. The link I need to use for each number is almost the same, with the only difference being the number in the cell itself. That is the last part that needs to be included in the link. I am not sure how to do this using the hyperlink formula.

The whole hyperlink formula is surrounded in quotes and I am not sure how to move the quotes around so that it accepts the variable I am trying to include.

function hyperlinkTest() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheets()[0];
  var cell = sheet.getRange("B5");
  cell.setFormula('=HYPERLINK("http://www.google.com/","Google")');
}

The above code is how to include a hyperlink in Google sheets, but the formula needs to be edited in a way where I can insert a variable. At the end of 'google.com' for example. When I try to do this it is just a string, I need it to recognize the variable.

Upvotes: 0

Views: 2067

Answers (1)

ross
ross

Reputation: 2774

All you need to do is escape your single-quotes and use + to add your variable, then single-quotes again to finish off the rest of your formula.

cell.setFormula('=HYPERLINK("http://www.google.com/'+variablehere+'","Google")');

Upvotes: 2

Related Questions