Update ImportJSON automatically

The Solution is adding a parameter to the URL as EugZol pointed out. However, it did not work via Google Spreadsheet's CONCATENATE - this led to loading errors - at least for me.

In the end I had to adjust the JavaScript function ImportJSONAdvanced

function ImportJSONAdvanced(url, query, options, includeFunc, transformFunc) {  var url = url + "?" + (Math.ceil(new Date().getTime() / 1000));  var jsondata = UrlFetchApp.fetch(url);  var object   = JSON.parse(jsondata.getContentText());  return parseJSONObject_(object, query, options, includeFunc, transformFunc);}

I added this line:

var url = url + "?" + (Math.ceil(new Date().getTime() / 1000));

It get's the current time via JavaScript and attaches it to the url - I divided it by 1000 and used .ceil to get the seconds rather than the milliseconds.

This way, the Spreadsheet finally auto-updates the API Call.

Sidenote: It does this every 15 minutes - I assume a time limit is implemented - but in my case, this is totally fine.

Regular cache busting URL parameter technique will work here:

=ImportJSON(CONCATENATE("http://dwh-platogo.herokuapp.com/q/zn4m?", YEAR(NOW()),MONTH(NOW()),DAY(NOW())))

This will add ?20150817 (as it's 17th of August today) parameter to URL, thus preventing Google from caching it (it will be updated daily).

You can also add current hour to force it to update hourly.