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

Question

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.

ross · Accepted Answer · 2019-06-13 09:01:50Z

2

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

answered Jun 13, 2019 at 9:01

ross

2,7842 gold badges15 silver badges22 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

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

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related