Auto refresh on sheet reload? about importjson HOT 8 OPEN

Thanks but none of the above solutions are generic enough to be used as a standard addition to the script itself...

I'm looking more in terms of a onLoad() function that will auto update ANY cell that references the function(s) used from this script...
Eg. without user modification and tailoring for use by ANY spreadsheet that uses this script...

from importjson.

@TriMoon @gsivaprabu I've written an article about how to refresh automatically a spreadsheet with ImportJSON.

@bradjasper It requires some manual steps within the sheet itself to do the setup, but I think the process can be officially documented in this repository, if you wish to do so.

Basically, it relies on a special sheet (in my example, but could be a special cell as well) to be auto updated using App Scripts and does a force refresh of all the ImportJSON referencing it.

It can be used both to refresh the sheet upon "open" event, and at a predefined interval.

https://medium.com/unly-org/how-to-consume-any-json-api-using-google-sheets-and-keep-it-up-to-date-automagically-fb6e94521abd

from importjson.

http://prntscr.com/hnaa45

code 👍
function importJSONupdate() {
var queryString = Math.random();

var cellFunction1 = '=IMPORTJSON(“' + SpreadsheetApp.getActiveSheet().getRange('A50').getValue() + '?' + queryString + '”,”'+ SpreadsheetApp.getActiveSheet().getRange('B50').getValue() + '”,”noInherit,noTruncate”)';
SpreadsheetApp.getActiveSheet().getRange('A1').setValue(cellFunction1);

from importjson.

For example to refresh one or more cells each minute I'm using this trick step by step :

1. Add a function in your script like :
   function UpdateData() { SpreadsheetApp.getActiveSheet().getRange('H22').setValue(importJson(ImportJSON(“http://date.jsontest.com", “/date”, “noInherit, noTruncate”); }

2. Go to Edit and apply a trigger 'time interval' on UpdateData( see https://developers.google.com/apps-script/guides/triggers/installable#managing_triggers_manually). Also you can set a trigger on document's opening or other event.

I hope it will help you. ;)

from importjson.

I am also facing same issue, When i am able to modify the cell that time only i can able to get the data. My data provider given every 1 min dynamic data . How can i handle any solutions or workarround

My Opensource lib:-

importJSON.gs

My API Provider:-

NSE India

Screenshot:-

Any one have idea means please suggest

regards,
@gsivaprabu

from importjson.

Thanks but none of the above solutions are generic enough to be used as a standard addition to the script itself...

I'm looking more in terms of a onLoad() function that will auto update ANY cell that references the function(s) used from this script...
Eg. without user modification and tailoring for use by ANY spreadsheet that uses this script...

Hi @TriMoon you got any solutions ?

from importjson.

http://prntscr.com/hnaa45

code
function importJSONupdate() {
var queryString = Math.random();

var cellFunction1 = '=IMPORTJSON(“' + SpreadsheetApp.getActiveSheet().getRange('A50').getValue() + '?' + queryString + '”,”'+ SpreadsheetApp.getActiveSheet().getRange('B50').getValue() + '”,”noInherit,noTruncate”)';
SpreadsheetApp.getActiveSheet().getRange('A1').setValue(cellFunction1);

I have this call how can i pass?

Every 1 minute need to update

=ImportJSON("https://www.nseindia.com/live_market/dynaContent/live_watch/stock_watch/foSecStockWatch.json")

from importjson.

I tried something hacky but ran into permission issues:

function importJSONAutoRefresh(url, query, parseOptions, cellSource) {
  var random = Math.random();
  url += '&random=' + random;
  console.log(SpreadsheetApp.getActiveSheet().getRange(cellSource).getValue())
  var refreshedImportJSON = 'importJSONWithRefresh("' + url + '", "' + query + '", "' + parseOptions + '", "' + cellSource + '")';
  console.log(refreshedImportJSON);

  SpreadsheetApp.getActiveSheet().getRange(cellSource).setValue(refreshedImportJSON);
}

I added this function but I get You do not have permission to call setValue (see https://stackoverflow.com/a/15936281/2391795) and I wonder how other people could make something like that work, I'm trying to update the cell that contains the call to the function.

from importjson.