Google spreadsheet script cell parameters

Question

Hi im having trouble passing 2 cells in my scripts parameters:

function myFunction(c1, c2) {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getSheets()[1]; //This is the correct sheet
var valueFirstCell = sheet.getRange(c1).getValue(); 
var valueSecondCell = sheet.getRange(c2).getValue();
return valueSecondCell - valueFirstCell;
}

So i use this in cell A3:

=myFunction(A1, A2)

And lets say A1=10 and A2=15 i would like A3 to show 5 but,

var valueFirstCell = sheet.getRange(c1).getValue();

fails "Argument has to be a range" Any ideas?

One thing I know is you can't pass Javascript something like C1 without enclosing it in quotes. — Antony Carthy

Jan Kleinert Jan Kleinert · Accepted Answer · 2012-04-13T19:20:15

In the custom function, c1 and c2 in your script are actually going to be the value of what's in those cells, so you can just use them directly. You can see that by adding Logger.log(c1); and Logger.log(c2); to your script. Try this:

function myFunction(c1, c2) {
  Logger.log(c1);
  Logger.log(c2);
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheets()[1]; //This is the correct sheet

  return c2 - c1;
}

There's a tutorial here on building a simple custom function that shows a similar example.

Google spreadsheet script cell parameters

3 Answers