IMPORTJSONAPI call from within script instead of formula

[LarryLRoberts]

This is more of a question than anything, but I was not sure where to place it.

One thing I am trying to figure out is if there is a way to not place the IMPORTJSONAPI formula within the sheet itself, but rather just make the JSON call 1 time from the script itself which then populates the Google Sheet and then stops. It would only execute the ImportJSON whenever the Google Sheet is opened.

The big issue with the current approach is that the formula is within a cell on the sheet. If you for example try to add a column, delete a row, or just about anything else, the sheet immediately refreshes everything resulting in it resetting the columns, rows, etc. back.

Any ideas? So far, I have had no luck trying to get it to work this way and I have not found any other version of this that tries that.

Thanks

[qeet]

In theory you should just be able to call IMPORTJSONAPI from the onOpen trigger so that the import is run everytime you open the sheet:

function onOpen(e) {
  // Code to import data and update spreadsheet
  ...
}

However there is a problem with this in that the IMPORTJSONAPI makes an external network request which is not allowed from a script called by the onOpen trigger. A better explanation is provided here:

https://stackoverflow.com/questions/55996556/google-apps-script-urlfetchapp-permission

The second option is to add a button to your spreadsheet which does the import when you manually click the button. To do this add the following function to your script:

function UPDATE_DATA() {
  var ROW = 1
  var COL = 1
  var values = IMPORTJSONAPI("http://data.nba.net/10s/prod/v1/2018/teams.json", "$.league.*[*]", "^.~, city, isNBAFranchise")
  var numrows = values.length
  var numcols = values[0].length
  SpreadsheetApp.getActiveSheet().getRange(ROW, COL, numrows, numcols).setValues(values) 
}

You will need to change the ROW and COL to where you want to insert the data and also update the IMPORTJSONAPI arguments to your own. Now read the following tuturial which describes how you add a button to your spreadsheet and hook it up to the above function:

https://www.benlcollins.com/apps-script/google-sheets-button/

Hope this helps.