"Error: GoogleJsonResponseException: API call to calendar.events.import failed with error: Bad Request" while uploading events to calendar

Question

I am trying to import bulk events from the Google Sheet tab to Google Calendar using Google Apps Script. The following code snippet first converts the data into CSV format and then tries to import it:

function importSheetToCalendar() {

  var spreadsheetId = "YOUR_SPREADSHEET_ID";
  var sheetName = "✨ CUSTOMIZE";
  var calendarId = "YOUR_CALENDAR_ID";
  
  var spreadsheet = SpreadsheetApp.openById(spreadsheetId);
  var sheet = spreadsheet.getSheetByName(sheetName);
  var data = sheet.getDataRange().getValues();
  
  var csvData = convertToCSV(data);
  
  var calendar = CalendarApp.getCalendarById(calendarId);
  
  var events = Calendar.Events.import(calendarId, csvData);
  
  Logger.log(events);
}

function convertToCSV(data) {
  var csv = "";
  for (var i = 0; i < data.length; i++) {
    csv += data[i].join(",") + "\n";
  }
  return csv;
}

However, when I run this script, I receive the following error:

GoogleJsonResponseException: API call to calendar.events.import failed with error: Bad Request

This source mentions the data formatting before importing the event to the calendar and the CSV events which I am trying to import also follow the same formatting. Can you please guide where I am making a mistake, any help would be much appreciated.

Answer 1

Modification points:

• I thought that in your showing script, var events = Calendar.Events.import(calendarId, csvData); is required to be modified. The arguments of Calendar.Events.import methods are resource: Calendar_v3.Calendar.V3.Schema.Event, calendarId: string, optionalArgs: Object, and headers: Object, respectively. In your situation, I think that resource: Calendar_v3.Calendar.V3.Schema.Event and calendarId: string are used as the 1st and 2nd arguments. Unfortunately, CDV data cannot be directly used. I thought that this might be the reason for your current issue.

• And also, when I saw your Spreadsheet, I noticed that in order to use the values of the Spreadsheet as resource: Calendar_v3.Calendar.V3.Schema.Event, it is required to create the object.

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

Modified script:

Before you use this script, please check your timeZone of timeZone.

function importSheetToCalendar() {

  var spreadsheetId = "YOUR_SPREADSHEET_ID";
  var sheetName = "✨ CUSTOMIZE";
  var calendarId = "YOUR_CALENDAR_ID";

  var spreadsheet = SpreadsheetApp.openById(spreadsheetId);
  var sheet = spreadsheet.getSheetByName(sheetName);
  var [, ...data] = sheet.getDataRange().getValues();
  var timeZone = spreadsheet.getSpreadsheetTimeZone(); // or Session.getScriptTimeZone() or "### your timeZone ###".
  var reqs = data.flatMap(([summary, startDate, startTime, endDate, endTime, , description]) => {
    if (summary && startDate instanceof Date && startTime instanceof Date && endDate instanceof Date && endTime instanceof Date) {
      return {
        iCalUID: Utilities.getUuid(),
        start: { dateTime: `${startDate.getFullYear()}-${startDate.getMonth() + 1}-${startDate.getDate()}T${startTime.getHours()}:${startTime.getMinutes()}:${startTime.getSeconds()}`, timeZone },
        end: { dateTime: `${endDate.getFullYear()}-${endDate.getMonth() + 1}-${endDate.getDate()}T${endTime.getHours()}:${endTime.getMinutes()}:${endTime.getSeconds()}`, timeZone },
        summary, description
      }
    }
    return [];
  });
  const res = reqs.map(obj => {
    try {
      return Calendar.Events.import(obj, calendarId);
    } catch (e) {
      return e;
    }
  });
  console.log(res)
}

Note:

• When I tested this modified script using your provided Spreadsheet, no error occurs. The events could be created in the Google Calendar.

• This is a simple modification. So, please modify this script to your actual situation. But, if your actual Spreadsheet is different from your provided Spreadsheet, this script might not be able to be used. Please be careful about this.

Reference:

• Events: import