Refresh Button / Auto Update GOOGLE SPREADSHEET

Question

I hope you can help me. I have been trying to get this working for quite some time.

I am taking the API from a website import it into google spreadsheets VIA importjson()

ImportJSON("http://api.796.com/v3/futures/ticker.html?type=weekly")

https://gist.github.com/chrislkeller/5719258

I have no problem importing the json data into google spreadsheet, now the hard part is making the data update, I would really like it to auto update but even a "update now" button would be great.

Thanks, This has been driving me nuts. BV

Answer 1

File > Spreadsheet settings > Calculation > Recalculation: On change and every minute

Then you can insert a =now() column and it will refresh the sheet every minute.

Apologies if you've tried this already, I have it setup now in a sheet but am waiting for a live event to test, so it might not even work.

Answer 2

You can just add the time trigger https://developers.google.com/apps-script/understanding_triggers

Answer 3

Im not really a javascript person, but I might have a little code that might help start you off. (Sorry if it does not help). What I use in my site is this (I changed it a little bit to incorporate your url).

$( document ).ready(function($) {
  setInterval(function(){
    $('.sell').load('http://api.796.com/v3/futures/ticker.html?type=weekly');
  }, 5000);
});

This just grabs the JSON every 5 seconds, so be sure you parse the JSON and display the data you need. Hope this helps! :)

Answer 4

So I was searching for a good way to do this as well. Here is what I have found so far.

If you change the function line of the importJSON like this:

function ImportJSON(url, query, parseOptions, recalc) {
}

Which just adds the recalc parameter to the function definition. This is a 'trick' because that parameter is not used for anything other than to make GSheet think something has changed. Then in the call to ImportJSON (in a GSheet cell), you add in the function call like this:

=ImportJSON("https://yourDomain.com/something","","rawHeaders",A1)

Then in the sheet you can manually change the value of A1 to anything different than it is. The Sheet and all the imports will get refreshed.

Another ... non-technical trick ... manually change A1 to 1 then 2 then 3. Each time the sheet will recalc. Now you can use the UNDO and REDO buttons to change the numbers and recalc the sheet.

A little of a kludge Hack, but it does make for a one-button "recalc" of a sort.