Is there anyway to speed up this batch update code using google script?-CodePudding

I am currently attempting to update 1600-2000 rows of data in google sheets. I am parsing data from an API Fetch but it keeps timing out because the update is taking way too long. I don't really know how to post reusable code without giving out the API which I can't do. Does anyone know how I can speed up this process?

    function logDataInEveryCell3() {
  const ss = SpreadsheetApp.getActive();
  const vs = ss.getRange("A2:A1669").getValues().flat();
  const osh = ss.getSheetByName("Sheet1");
  let row = 2;
  vs.forEach(e => {
    let res = UrlFetchApp.fetch("https://api”   e  token");
    let obj = JSON.parse(res.getContentText());
    let items = obj["dispatches"]["items"];
    let vo = items.map(itm => [itm.vehicle.driver.contact.name])
    vo2 = vo.slice(-1);//good idea
    osh.getRange(row,3).setValues(vo2);
    row  = vo2.length;
  });

}

CodePudding user response：

Try something like this:

function logDataInEveryCell() {
  const ss = SpreadsheetApp.getActive();
  const vs = ss.getRange("A2:A51").getValues().flat();
  const osh = ss.getSheetByName("Sheet1");
  osh.clearContents();
  let l = 1;
  let a = [];
  vs.forEach(e => {
    let res = UrlFetchApp.fetch("API"   e   "Token");
    let obj = JSON.parse(res.getContentText());
    let items = obj["dispatches"]["items"];
    let vo = items.map(itm => [itm.vehicle.driver.contact.name])
    a.push(vo.slice(-1));
  });
  a.flat(1);
  osh.getRange(l,1,a.length,a[0].length).setValues(a);
}