Home > Enterprise >  How can I batch update Google Sheets Columns using Google Script instead of doing one at a time?
How can I batch update Google Sheets Columns using Google Script instead of doing one at a time?

Time:03-02

Below I have working code that fills in the correct column that I want with the information that I want, the issue is I am going to have 2,000 rows I need to fill, and this fills in one cell at a time in descending order. Is there a way to do a batch update instead of doing 1 cell at a time in the column?

function logDataInEveryCell() {

  const data = SpreadsheetApp.getActiveSpreadsheet().getRange("A2:A51").getValues();

let JOBID = [];
for (let a = 0; a < data.length; a  ) {
    JOBID = data[a]
    
   var res = UrlFetchApp.fetch("API"   JOBID   "Token")
   
  
  var content = res.getContentText();

  var json = JSON.parse(content);

  // Driver Name

  var dispatches = json["dispatches"]["items"];

  var lastItem = dispatches.pop();

  var dName = [lastItem.vehicle.driver.contact.name]
  
  Logger.log(dName)

   var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet1");
  var colValues = sheet.getRange("C2:C51").getValues();
  var count = colValues.filter(String).length
  sheet.getRange(count 2,3).setValue(dName);;

}}

CodePudding user response:

Try 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;
  vs.forEach(e => {
    let res = UrlFetchApp.fetch("API"   e   "Token");
    let obj = JSON.parse(res.getContentText());
    let items = obj["dispatches"]["items"];
    items.pop();//guess
    let vo = items.map(itm => [itm.vehicle.driver.contact.name])
    osh.getRange(l,1,vo.length,vo[0].length).setValues(vo);
    l  = vo.length;
  });
}
  • Related