Import JSON refresh with Sheet Trigger

[RonKon]

Hi, the script works perfectly and is really helpfull for me. It is possibile you implement the function sheet trigger to create a automatic refresh. I have tested so many variants of refresh but all not working with your script. Thank`s in Advance for help

[RonKon]

it`s done

[xyzzy529]

How did you do it @RonKon? How is this used? Is there an example?

I just put a PR #51 in to make a "recalc" parameter. Which when the referenced cell is changed (manually for my purposes) then the ImportJSON() is re-loaded and the sheet recalcs.

example: =ImportJSON("https://api.fixer.io/latest?base=INR&symbols=USD","","rawHeaders",B1)

Changed: function ImportJSON(url, query, parseOptions, recalc) {

[Vadorequest]

See https://github.com/bradjasper/ImportJSON/issues/59#issuecomment-474439295

I guess we can close this and only use one issue, seems to be a duplicate.

[akonyar]

I've had same problem, first I tried to clear the cell that contains formula then set the formula again, but it doesn't work. When I add SpreadsheetApp.flush() between these steps, it worked perfectly.

function reCalculate() {
//reCalculate Data Import sheet by empty cell that includes ImportJSON function (B8), flush then set code again.
var ss = SpreadsheetApp.getActiveSpreadsheet ();
var sheet = ss.getSheetByName("DATA IMPORT");
var cell = sheet.getRange('B8')
cell.setValue('')
SpreadsheetApp.flush()
cell.setValue('=ImportJSON("YOUR-JSON-LINK","","noHeaders,noTruncate")')
}

[0xRampey]

@xyzzy529 If the referenced cell is set to an auto updating value like =GOOGLEFINANCE("Currency:USDINR"), all the ImportJSON() reload and the sheet recalcs every few minutes too! Thanks for the hack

[eemreyesil]

I've had same problem, first I tried to clear the cell that contains formula then set the formula again, but it doesn't work. When I add SpreadsheetApp.flush() between these steps, it worked perfectly.

function reCalculate() {
//reCalculate Data Import sheet by empty cell that includes ImportJSON function (B8), flush then set code again.
var ss = SpreadsheetApp.getActiveSpreadsheet ();
var sheet = ss.getSheetByName("DATA IMPORT");
var cell = sheet.getRange('B8')
cell.setValue('')
SpreadsheetApp.flush()
cell.setValue('=ImportJSON("YOUR-JSON-LINK","","noHeaders,noTruncate")')
}

@akonyar Thank you so much for this hint. Indeed it works. thanks again

[zenminimalist]

@akonyar @eemreyesil Is there a way I can change this code to get a sheet to only refresh/update when say cell A1 is TRUE (e.g. checkbox is ticked), so I can prevent API calls via importJSON from being made in that sheet? Similar to a "kill switch". It only updates when the check mark is FALSE If so, how would the code have to look like?

[eemreyesil]

Somewhere in the spreadsheet write True and in the below cell, False. Then select A1. Under Data tab, click data validations, for select from range box, select the cells you wrote down True and False. In the code, define a variable for A1 cell. Put a condition such as if this variable is True {...} else {}. If you want auto refresh (in case it is True), you may use recursive functions. You can call the same function under the first if statement(if True{return reCalculate()}) and under the else statement, you can type return something else.

I hope I may help you.