IMPORTJSONAPI call from within script instead of formula

[qeet]

IMPORTJSONAPI call from within script instead of formula

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