Auto refresh on sheet reload?

[TriMoon]

Is there anyway to make this script load fresh data when the spreadsheet is reloaded by user instead of modifying the cell where the function is called?
I tried #57 but no luck on spreadsheet reload...

[TheRealBobi]

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. ;)

[snipeTR]

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);

[TriMoon]

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...

[sivaprabug]

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

[sivaprabug]

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 ?

[sivaprabug]

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")

[Vadorequest]

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.

[Vadorequest]

@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