Script triggers

Question

I have been having problems with running out of my quota using Google Sheets and an ImportJSON function as the ImportJSON was running everytime the sheet refreshes.

To get around this, I recorded 3 separate macros. The first places the ImportJSON formula into a cell:

function ApplyImportJSON() {
  var spreadsheet = SpreadsheetApp.getActive();
  spreadsheet.getRange('A1').activate();
  spreadsheet.getCurrentCell().setFormula('=ImportJSON("https://fantasy.premierleague.com/api/bootstrap-static/")');
  spreadsheet.getRange('A2').activate();
};

The second, then copy & pastes values from that sheet into a second sheet in the workbook:`

  var spreadsheet = SpreadsheetApp.getActive();
  spreadsheet.getRange('A1').activate();
  var currentCell = spreadsheet.getCurrentCell();
  spreadsheet.getSelection().getNextDataRange(SpreadsheetApp.Direction.NEXT).activate();
  currentCell.activateAsCurrentCell();
  spreadsheet.getRange('A1:FG1').activate();
  currentCell = spreadsheet.getCurrentCell();
  spreadsheet.getSelection().getNextDataRange(SpreadsheetApp.Direction.DOWN).activate();
  currentCell.activateAsCurrentCell();
  currentCell = spreadsheet.getCurrentCell();
  spreadsheet.getSelection().getNextDataRange(SpreadsheetApp.Direction.DOWN).activate();
  currentCell.activateAsCurrentCell();
  spreadsheet.setActiveSheet(spreadsheet.getSheetByName('Sheet22'), true);
  spreadsheet.getRange('A1').activate();
  spreadsheet.getRange('\'JSON BSS\'!A1:FG1000').copyTo(spreadsheet.getActiveRange(), SpreadsheetApp.CopyPasteType.PASTE_VALUES, false);
};

'Then the third, removes the ImportJSON formula:`

function DeleteImportJSON() {
  var spreadsheet = SpreadsheetApp.getActive();
  spreadsheet.getRange('A1').activate();
  spreadsheet.getActiveRangeList().clear({contentsOnly: true, skipFilteredRows: true});
};

I've been trying to set up each to run in sync by setting up triggers for each but it just takes one to fall out of sync and it then doesn't do what I want. I have a very limited knowledge of scripts but I'm sure there must be a way (for someone smarter than me!) to combine all three into one script that runs with a 60 second delay between each part that I can then use just one trigger to run the script every 15 minutes?

I'm pretty sure this is a very longwinded way of getting it to do what I'd like it to do but I can't seem to find a way of getting the original ImportJSON script to just refresh every 10 minutes.`

Answer 1

I don't think that using a trigger to add a custom function every 10 minutes is a good idea, instead, use the trigger to execute the IMPORTJSON function and write the data directly to the required sheet.

Example:

function every10minutes(){
  const data = ImportJSON("https://fantasy.premierleague.com/api/bootstrap-static/");
  const spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  const sheet = spreadsheet.getSheetByName('Sheet22');
  sheet.clear();
  sheet.getRange(1,1,data.length,data[0].length).setValues(data);
}