Two way sync Google Calendar and Sheets.

[Corné van Son]

Hi there,

I'm trying to create a two-way sync between Google Sheets and Google Calendar. The sync from Google Sheets to Calendar is working perfectly, but the other way around seems to be hard.

I've got the code below, but the changeType seems to return as "null".

Can anyone steer me in the right direction?

Regards,

Corné

function onEventChanged(e) {

  // Log when the onChange event is triggered

  Logger.log("onChange event triggered");

  // Determine the type of change that was made to the event

  var changeType = e.changeType;

  Logger.log(e);

  Logger.log(changeType);

  // Process the event change

  if (changeType === "UPDATE" || changeType === "DELETE") {

    // Get the spreadsheet ID

    var spreadsheetId = 'myID'; // Replace with your spreadsheet ID or URL

    // Open the spreadsheet

    var spreadsheet = SpreadsheetApp.openById(spreadsheetId);

    // Get the sheet

    var sheet = spreadsheet.getSheetByName('Actiepunten'); // Replace with the correct sheet name

    // Get the event ID

    var eventId = e.calendarId;

    // Get the event

    var calendar = CalendarApp.getCalendarById(e.calendarId);

    var event = calendar.getEventSeriesById(eventId);

    // Get the headers

    var headers = sheet.getRange(2, 1, 1, sheet.getLastColumn()).getValues()[0];

    // Get the ID and Date column indexes

    var idColumn = headers.indexOf("ID");

    var dateColumn = headers.indexOf("ENDDATE");

    // Check if the ID or Date header is not found

    if (idColumn === -1 || dateColumn === -1) {

      // Log an error message and exit

      Logger.log("ID or Date header not found. Exiting...");

      return;

    }

    // Get the ID from the event description

    var id = event.getDescription();

    // Check if the ID matches the expected format (e.g., "TASK_0001")

    var idPattern = /^TASK_\d{4}$/;

    if (id && id.match(idPattern)) {

      // Get the matching rows

      var matchingRows = sheet.getRange(2, idColumn + 1, sheet.getLastRow() - 1, 1).getValues();

      // Find the row index of the matching row

      var rowIndex = matchingRows.findIndex(function (row) {

        return row[0] == id;

      });

      // If a matching row is found

      if (rowIndex >= 0) {

        // If the event was updated

        if (changeType === "UPDATE") {

          // Get the due date

          var dueDate = event.getAllDayStartDate();

          // Update the due date in the spreadsheet

          sheet.getRange(rowIndex + 2, dateColumn + 1).setValue(dueDate);

          // Log a message

          Logger.log("Updated due date in the sheet.");

        } else if (changeType === "DELETE") {

          // Delete the row in the spreadsheet

          sheet.deleteRow(rowIndex + 2);

          // Log a message

          Logger.log("Deleted the row in the sheet.");

        }

      } else {

        // Log a message

        Logger.log("Matching row not found.");

      }

    } else {

      // Log a message

      Logger.log("Event Description (ID) does not match the expected format.");

    }

  }

}