Update ImportJSON automatically

Question

I setup importing JSON to Google Spreadsheets by following this sweet Medium tutorial.

It does it's job well - taking this API call

[
  {"day":"2015-08-05","new_users":103},
  {"day":"2015-08-06","new_users":255},
  {"day":"2015-08-07","new_users":203},
  {"day":"2015-08-08","new_users":198},
  {"day":"2015-08-09","new_users":273},
  {"day":"2015-08-10","new_users":373},
  {"day":"2015-08-11","new_users":189},
  {"day":"2015-08-12","new_users":228},
  {"day":"2015-08-13","new_users":167}
]

and transforming it into:

However, as you can see, this API get's updated daily. So I'd like this API Call to refresh when I open the sheet - not only when editing a cell.

I've researched a ton, and also tried adding Triggers, but my attempts so far have failed. This should not be this hard... Any ideas? Am I missing something?

I will also add & reward a bounty (50) lateron, since this is very important to me.

You can use [flush](https://developers.google.com/apps-script/reference/spreadsheet/spreadsheet-app?hl=en#flush()) with an [onOpen](https://developers.google.com/apps-script/guides/triggers/#onopen) trigger. — Akshin Jalilov, Aug 17 '15 at 12:38

score 3 · Accepted Answer · edited May 23 '17 at 11:48

3

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.

edited May 23 '17 at 11:48

Community

1
1

answered Aug 18 '15 at 11:59

davegson

8,205
4
51
71

Man, but it **does** work with `CONCATENATE`: [Screenshot](https://yadi.sk/i/iAMvdXBRiWnUe). I just opened my test spreadsheet and it was updated a moment after I saw it. – EugZol Aug 18 '15 at 12:17
Ye, I believe it's a great solution - added that it didn't work specifically for me - so ye, hope others will profit even more from your answer. – davegson Aug 18 '15 at 12:54
Well, given Spreadsheets is cloud software it's unlikely that exact the same thing work for one spreadsheet and doesn't work for another. Why don't you provide some error details for debugging purposes? – EugZol Aug 18 '15 at 12:57
Thanks, @davegson! Forcing a "?" overlooks API endpoint URLs which may already be passing parameters using a "?". I believe that circumstance calls for a "&". I have made a modification to detect whether "?" is used and add "&" accordingly - https://jsfiddle.net/therobertandrews/kp5r80to/1/ This solves a problem that has taken a couple of hours. – Robert Andrews Aug 22 '22 at 12:45

EugZol · Answer 2 · 2015-08-18T12:39:41.070

1

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.

edited Aug 18 '15 at 12:39

answered Aug 17 '15 at 17:20

EugZol

6,476
22
41

Nope I played around with this for a while - didn't succeed. When reopening the document it *did* try recalling the API, but it only returned `Load Error`. I always had to manually refresh the function to get it to work – davegson Aug 18 '15 at 09:28
But it is a great suggesting, got me further than my previous attempts! – davegson Aug 18 '15 at 09:58
It definitely works for me. Well, at least no error on opening the sheet. What does the error details say? – EugZol Aug 18 '15 at 10:02
Hmm, I changed the Javascript to add the current time as a parameter to the url, and after reopening the file after 10 minutes it works. A quick reopen does not - but this should actually be enough. – davegson Aug 18 '15 at 10:15
I'm still doing some tests – davegson Aug 18 '15 at 11:36
I added my solution - your answer was an important hint in the right direction - but I won't accept it - I'll think about the bounty though ;) – davegson Aug 18 '15 at 12:01
@TheCha͢mp Surely implementation of concatenation function in JS was your main struggle, not finding the means to bust the cache itself :) – EugZol Aug 18 '15 at 12:25
At this time, custom functions require deterministic parameters (now() is not allowed). Reference: https://developers.google.com/apps-script/guides/sheets/functions – Rubén Oct 24 '16 at 11:21

Update ImportJSON automatically

2 Answers2