Google-sheets – How to use triggers with functions that require parameters from the spreadsheet

google sheetsgoogle-apps-script

I found a simple and great solution to retrieve JSON into a Google spreadsheet.

I want the function ImportJSON to update regularly via a trigger (event or time-based, I still haven't decided what I will use). The problem with using triggers, be it an onChange or time based trigger, is that it runs the script directly – it does not tell the spreadsheet to run the script. Since the script depends on input from the spreadsheet (the function requires at least one parameter – an URL), any type of trigger will fail with:

13-11-17 18:15:50:940 PST] SpreadsheetApp.getActiveSpreadsheet() [0 seconds]
[13-11-17 18:15:51:011 PST] Starting execution
[13-11-17 18:15:51:020 PST] UrlFetchApp.fetch([[object Object], null]) [0 seconds]
[13-11-17 18:15:51:065 PST] Execution failed: Invalid argument: http://[object%20Object] (line 167, file "Code") [0.044 seconds total runtime]

I could attach a trigger to a wrapper function, which takes no arguments, and have that wrapper function pass the parameter to ImportJSON. But since I have dozens of sheets within the document (dozens of URLs to retrieve JSON from), I'd have to create one wrapper function per sheet. This looks like a clumsy solution.

I wish I could make the spreadsheet run the script and update the values, but I don't think this is possible. There has to be a better way to use triggers with functions that require parameters from the spreadsheet.

Best Answer

How to use triggers with functions that require parameters from the spreadsheet?

AFAIK, you cannot. The solution is to create one single function, for example called RefreshAll, inside which you call all the wrapper functions (GetValue) containing the actual parameters which need to be passed to the function that actually does the job (RealFunctionThatDoesTheJob).

Note: Sorry if this sounds complicated. If you only have one set of parameters (as opposed to 3, in the example below), you can do away with GetValue and call RealFunctionThatDoesTheJob directly from within the function that is being triggered (RefreshAll).

Lastly, to have your triggers send to your spreadsheet those values that are dependent on parameters, you need to have the script push the values to the spreadsheet using (currentSheet.getRange(Row,Column).setValue(Value);)

Here is the example:

function RefreshAll() {

  var currentSheet = SpreadsheetApp.openById("ID").getSheetByName("MySheet");

  var Value1 = GetValue1();
  currentSheet.getRange(Row,Column).setValue(Value1);

  var Value2 = GetValue2();
  currentSheet.getRange(Row,Column).setValue(Value2);   

  var Value3 = GetValue3();
  currentSheet.getRange(Row,Column).setValue(Value3);

}

function GetValue1() {
  return RealFunctionThatDoesTheJob("Parameter1","Parameter2","Parameter3");
}

function GetValue2() {
  return RealFunctionThatDoesTheJob("Parameter4","Parameter5","Parameter6");
}

function GetValue3() {
  return RealFunctionThatDoesTheJob("Parameter7","Parameter8","Parameter9");
}
Related Topic