Is there a way to optimize this script that add entries to Google Calendar from Google Sheets

I have a basic script that creates all-day events in Google Calendar from Google Spreadsheets.

function updateCalendar() {
 var ss = SpreadsheetApp.getActiveSpreadsheet();
 var sheet = ss.getSheetByName("Entries");
 var lastRow = sheet.getLastRow();
 var cal = CalendarApp.getCalendarById("xxxxxxxxxx");

 var data = ss.getRange("A2:E" + lastRow).getValues();

 for(var i = 0;i<data.length;i++){
  {

    cal.createAllDayEvent(data[i][0], new Date(data[i][2]), { description: data[i][1] }).setColor(CalendarApp.EventColor[data[i][4]]);

 }
 }
}

I just ran a test to process over 500 entries into Google Calendar. As you can see from the screenshot, however, the script takes almost ten minutes to execute. Is there a way to speed this process up? I was thinking if I can get all the data as an array, then I could write it to Google Calendar in one call, but I am not sure if that is possible.

I believe your goal is as follows.

• You want to reduce the process cost of your script.

In this case, how about using the batch request? Ref When this is reflected in your script, it becomes as follows.

Usage:

1. Install a Google Apps Script library

In order to use the batch request, it is required to create a request body. Ref But, I thought that it might be a bit difficult. So, I created it as a Google Apps Script library. Ref In this answer, this library is used.

Please install BatchRequest of the Google Apps Script library. You can see how to install it here.

2. Enable Calendar API

Please enable Calendar API at Advanced Google services. Ref

3. Modified script

When your script is modified using the above library and Calendar API, it becomes as follows.

function updateCalendar() {
  var calendarId = "###"; // Please set your calendar ID.

  // Retrieve values from Spreadsheet.
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName("Entries");
  var lastRow = sheet.getLastRow();
  var data = ss.getRange("A2:E" + lastRow).getValues();
  
  // Create a request body for the batch request.
  var colorObj = { "PALE_BLUE": 1, "PALE_GREEN": 2, "MAUVE": 3, "PALE_RED": 4, "YELLOW": 5, "ORANGE": 6, "CYAN": 7, "GRAY": 8, "BLUE": 9, "GREEN": 10, "RED": 11 };
  var requests = data.map(([summary, description, dateObj, , color]) => {
    const date = Utilities.formatDate(dateObj, Session.getScriptTimeZone(), "yyyy-MM-dd");
    return {
      method: "POST",
      endpoint: `https://www.googleapis.com/calendar/v3/calendars/${calendarId}/events`,
      requestBody: { end: { date }, start: { date }, description, colorId: colorObj[color], summary }
    }
  })
  
  // Request the batch request using the created request body.
  var result = BatchRequest.EDo({ batchPath: "batch/calendar/v3", requests });
  console.log(result);
}

• When this script is run, the events are created to the calendar with the batch request.

References:

• Send batch requests
• BatchRequest of Google Apps Script library (Author: me)
• Events: insert