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.