Passing variable to Google Apps Script Deployed web application

Question

I am developing a spreadsheet where a user clicks a link in the sheet to call a deployed web app as described here Trigger Script in Spreadsheet in Similar Fashion to a Link

This works well although I need to somehow identify the row of the calling cell that the user clicked the link. So if the

=HYPERLINK("https://script.google.com/macros/s/ ... /exec"&params())

Is in cell E1, I need the functions called to know the calling row is "1". I tried GetActiveCell within the params function and I keep getting a range error. I also tried passing the row directly to the params() function through the calling hyperlink and it did not receive the value [I tried as (1) and ("1")]. Is there a way to pass a value to the params function?

=HYPERLINK("https://script.google.com/macros/s/ ... /exec"&params(1))

Can anyone offer any help / suggestions? It would be greatly appreciated!

Answer 1

You can modify the function params to accept the argument it's given, and use it in forming the string that it returns. Like so:

function params(row) {                                   // added row here
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var ssid = ss.getId();
  var sheet = ss.getActiveSheet();
  var sheetName = sheet.getName();
  return '?ssid='+ssid+'&name='+sheetName+'&row='+row;   // added row here
}

Now when you use ...exec" & params(1)) the value of 1 will be correctly appended to the URL used to invoke the web app: the URL will have &row=1 and consequently, the doGet function of the web app will receive this value as e.parameters.row.

However it's easier to just append 1 yourself: in the hyperlink function, put

...exec" & params() & "&row=1" )

or

...exec" & params() & "&row=" & row() )

There is no need to run a number through a custom function when you can just append it to the URL in the spreadsheet. The reason the custom function params is needed is to pass the values that spreadsheet functions cannot access: spreadsheet id and sheet name.