I'm writing code to track my hours, retrieving calendar items from Google Calendar and writing it into Google Sheets.

I'm already using a batch array to speedup the process, but I feel I could make it even faster. I've tried to use the map-function (see dashed-out code below, created from this tutorial) but that did not work out.

My code:

function getCalendarEvents() {
  
  var ss = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Data"); 
  var cal = CalendarApp.getCalendarById("[email protected]"); 
  var eind = new Date;
  eind.setDate(eind.getDate() 1);
  var events = cal.getEvents(new Date("1/1/2022"), eind);
  
  var lr = ss.getLastRow();
  ss.getRange(3, 1, lr,14).clearContent();
  
//  var sd = events.map(function(r){ return [r[0],[2]]} );
//  ss.getRange(3, 2, sd.length,sd[0].length).setValues(sd);

  for(var i = 0;i<events.length;i  ) {
    
    var sd = events[i].getStartTime();
    var title = events[i].getTitle();
    var ed = events[i].getEndTime();
    var des = events[i].getDescription();
    var rec = events[i].isRecurringEvent();
    var cre = events[i].getDateCreated();
    var upd = events[i].getLastUpdated();
    var own = events[i].getCreators();
    
    ss.getRange(i 3, 2).setValue(sd);
    ss.getRange(i 3, 2).setNumberFormat("ddd   d-m-yyyy");
    ss.getRange(i 3, 5).setValue(ed);
    ss.getRange(i 3, 5).setNumberFormat("h:mm");
    ss.getRange(i 3, 9).setValue(title);
    ss.getRange(i 3, 10).setValue(des);
    ss.getRange(i 3, 11).setValue(cre);
    ss.getRange(i 3, 12).setValue(upd);
    ss.getRange(i 3, 13).setValue(rec);
    ss.getRange(i 3, 14).setValue(own);

  }  
}

How can I put the code with the "setValue" snippets (within the loop) in a batch array?

CodePudding user response：

Try this (not tested but could work)

function getCalendarEvents() {
  var ss = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Data"); 
  var cal = CalendarApp.getCalendarById("[email protected]"); 
  var eind = new Date;
  eind.setDate(eind.getDate() 1);
  var events = cal.getEvents(new Date("1/1/2022"), eind);
  var lr = ss.getLastRow();
  ss.getRange(3, 1, lr,14).clearContent();
  var result = []
  events.forEach(e => {
    result.push([e.getStartTime(),
                e.getTitle(),
                e.getEndTime(),
                e.getDescription(),
                e.isRecurringEvent(),
                e.getDateCreated(),
                e.getLastUpdated(),
                e.getCreators()])
  })
  ss.getRange(3,1,result.length,result[0].length).setValues(result)
}