What is the most efficient way to import event details from Google Calendar to Google Sheets? (Google Apps Script)

Question

I am working on a script to save Google Calendar events to a Google Sheets file. There were many other posts on the subject, but I needed a single row per event with the guest list split into multiple columns based on criteria and I was unable to find any scripts with this output format. I've built a similar function in a low-code app builder (Appsheet) but I am very new to Javascript.

I've tested the script below and it works well, but I wanted to ask if this was the most efficient way to arrange the for loops to get the information that I need.

function getEventsFromRangeGeneric() {

  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName("EventsRange");
  var employees = ['[email protected]','[email protected]','[email protected]'];
  
  //Get date range from cells

  var start_time = sheet.getRange("A2").getValue();
  var end_time = sheet.getRange("B2").getValue();
  
  //Get calendar ID and events

  var id_cal = '[email protected]';
  var cal = CalendarApp.getCalendarById(id_cal);
  var events = cal.getEvents(new Date(start_time), new Date(end_time));

  //Create headers and set to fifth row

  var header = [["Title","Name","Employee","Client","Start","End","Duration","Location"]]

  var range = sheet.getRange(5,1,1,8).setValues(header);

  //Loop through each event to get details

  var row = 6;

  for (var i = 0;i<events.length;i++){
    
    var title =  events[i].getTitle();
    var start_time =  events[i].getStartTime();
    var end_time =  events[i].getEndTime();
    var duration = '';
    var loc = events[i].getLocation();
    var guestList = events[i].getGuestList();
    var guestEmails = [];
    var employEmail = [];
    var clientEmail = [];
    var clientName = '';
    
//Loop through each guest object to get list of emails

    for (var j = 0;j<guestList.length;j++){
      guestEmails.push(guestList[j].getEmail());
    }

//Loop through each list of emails to determine which is employee and which is client
      
    for (var g = 0;g<guestEmails.length;g++) {
      if (employees.includes(guestEmails[g]) === true) {
      employEmail.push(guestEmails[g])
    } else {
      clientEmail.push(guestEmails[g])
    }
}

//Use details to fill in corresponding columns

    var details = [[title,clientName,employEmail, clientEmail, start_time, end_time, duration, loc]];
    var range2 = sheet.getRange(row+i,1,1,8);
    range2.setValues(details);

       
    var cell2=sheet.getRange(row+i,7); // go to column 7 (the placeholder) of the output data for duration column

  // Calculate the number of hours of the session

        cell2.setFormula('=(F' +row+ '-E' +row+ ')');
        cell2.setNumberFormat('hh:mm:ss')
  
  }
 
  row=row+i
  
}

Answer 1

Modification points:

• In your script, setValues and setFormula are used in a loop. In this case, the process cost will become high. Ref (Author: me)
  • In this case, I think that setValues can be used outside of the loop. By this, the process cost can be reduced a little.
• The unused variable is included.
• for (var j = 0; j < guestList.length; j++) {} and for (var g = 0; g < guestEmails.length; g++) {} can be written by one loop.
• I think that var start_time = sheet.getRange("A2").getValue(); and var end_time = sheet.getRange("B2").getValue(); can be written by one request.
• In your script, employEmail and clientEmail are an array. In this case, when the value of var details = [[title,clientName,employEmail, clientEmail, start_time, end_time, duration, loc]]; is put to the sheet, the 1st element is used. Please be careful about this.
• row=row+i is put outside of the loop. By this, row is always the initial value of var row = 6;.

When these points are reflected in your script, how about the following modification?

Modified script:

function getEventsFromRangeGeneric() {
  var employees = ['[email protected]','[email protected]','[email protected]'];
  var id_cal = '[email protected]';
  var header = ["Title", "Name", "Employee", "Client", "Start", "End", "Duration", "Location"];
  var row = 6;

  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName("EventsRange");
  var [start_time, end_time] = sheet.getRange("A2:B2").getValues()[0];
  var cal = CalendarApp.getCalendarById(id_cal);
  var events = cal.getEvents(new Date(start_time), new Date(end_time));
  var values = [header];
  for (var i = 0; i < events.length; i++) {
    var title = events[i].getTitle();
    var start_time = events[i].getStartTime();
    var end_time = events[i].getEndTime();
    var loc = events[i].getLocation();
    var guestList = events[i].getGuestList();
    var employEmail = [];
    var clientEmail = [];
    var clientName = '';
    for (var j = 0; j < guestList.length; j++) {
      var email = guestList[j].getEmail();
      if (employees.includes(email) === true) {
        employEmail.push(email);
      } else {
        clientEmail.push(email);
      }
    }
    values.push([title, clientName, employEmail.join(","), clientEmail.join(","), start_time, end_time, `=F${i + row}-E${i + row}`, loc]);
  }
  sheet.getRange(row - 1, 1, values.length, values[0].length).setValues(values);
}

References:

• Best Practices
• Benchmark: Reading and Writing Spreadsheet using Google Apps Script (Author: me)