5

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

user3321164
  • 61
  • 1
  • 1
  • 2
  • did you get the triggers to work? – davegson Aug 14 '15 at 12:16
  • Add the time trigger should work as shown in this *[tutorial](http://www.computerworld.com/article/2469616/business-intelligence/business-intelligence-79661-how-to-create-an-automatically-updating-spreadsheet.html#slide12)* – eQ19 Nov 17 '15 at 00:01

4 Answers4

3

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.

pirho
  • 11,565
  • 12
  • 43
  • 70
PCoughlin
  • 153
  • 10
2

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

printminion
  • 2,988
  • 1
  • 26
  • 30
  • that doesn't work unfortunately, as it calls ImportJson without the url specified in the cell – samwa Jul 07 '23 at 13:18
0

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.

Mike J
  • 1
-2

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! :)

nahtnam
  • 2,659
  • 1
  • 18
  • 31