Is there a faster way to fetch JSON data to a Google Sheet using Apps Script?

Question

I'm trying to pull data from a JSON source into a Google Sheet, specifically the "element" value under "picks".

Data source: https://fantasy.premierleague.com/api/entry/2935529/event/8/picks/

After some trial and error, I managed to get exactly what I needed. However, the execution is too slow, and I was wondering if there's a code modification that could speed it up? I need to run a 1000 iterations of the above script, hence speed is an issue.

function pullJSON() 
{
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getActiveSheet();
  
  var url="https://fantasy.premierleague.com/api/entry/2935529/event/8/picks/"; 
  var response = UrlFetchApp.fetch(url); 
  var dataAll = JSON.parse(response);
  
  for (var i = 0; i < 15; i++)
  {
  var row = [dataAll.picks[i].element];
  sheet.appendRow(row); 
  }
}

Use fetchall> create a array > use setValues. See duplicates — TheMaster, Nov 22 '20 at 11:18

score 0 · Answer 1 · answered Nov 22 '20 at 09:11

See this example, using UrlFetchApp.fetchAll

https://tanaikech.github.io/2018/04/19/benchmark-fetchall-method-in-urlfetch-service-for-google-apps-script/

Documentation on .fetchAll

https://developers.google.com/apps-script/reference/url-fetch/url-fetch-app#fetchAll(Object)

Is there a faster way to fetch JSON data to a Google Sheet using Apps Script?

1 Answers1