Google Spreadsheet Apps Script to Create / Update Calendar Entries

Code.gs

// This is v2. v1 = https://script.google.com/home/projects/1is6UE2m5YKnRICdfgwwcsYbiDaeI2EzuA8QDe8iu2vsmCY_DVc1aLtfn/edit
/*
  This Apps Script allows you to bulk-add / bulk-edit Google Calendar events from a Google Spreadsheet.
  Usage:
  - Get or create a calendar in Google Calendar. Untested, but might work even if it's not shared.
    Inside its settings (under Integrate calendar section), find its Calendar ID
  - Create a blank sheet. Rename the sheet to the Calendar ID.
  - Open menu: Data > Script editor and paste this file in Code.gs
  - Still in the Script editor, open menu: Run > Run function > onOpen.
    This will ask you to trust this script and give it access to your calendar.
    Only do so after you've scrutinized it: I might be trying to hack you!
  - Refresh the sheet: there should now be a 'Calendar' menu
  - Open menu: 'Calendar' > 'Sync current sheet'
    This will fill the sheet with headers and events from the shared calendar (if there any).
    It will get all the future events up to 20 years in the future (you can edit the script to tweak this)
    If the calendar is too big, this will take forever... Oops!
  - Add new events: just leave out the 'id' column and fill in the fields of your choosing.
  - Edit events: modify any field of your liking. Edits will show up in bold.
  - Synchronize new events / edits by running the menu: 'Calendar' > 'Sync current sheet'.
    It will only apply to cells that are in bold.
  
  Some extra notes:
  - At any point, you can reload the whole calendar by clearing out the sheet and running the menu.
  - Event colors are using numbers: see the note on the column to understand their meaning.
  - You can reorder columns as you see fit.
*/

const defaultEventDurationMinutes = 60;

function onOpen() {
  var ui = SpreadsheetApp.getUi(); 
  ui.createMenu('Calendar')
    .addItem('Synchronize calendar changes', 'syncCalendar')
    .addToUi();
}

function hasNonBold(range) {
  for (const row of range.getFontWeights()) {
    if (row.indexOf('bold') < 0) {
      return true;
    }
  }
  return false;
}

function onEdit(e) {
  const range = e && e.range || SpreadsheetApp.getActiveSheet().getActiveCell();

  if (!/[^@]+@.+?\.google.com/.test(range.getSheet().getName())) {
    return;
  }
  
  if (hasNonBold(range)) {
    range.setFontWeight('bold');
  }
}

function composeDateTime(date, time) {
  return new Date(date.getFullYear(), date.getMonth(), date.getDate(), time.getHours(), time.getMinutes(), time.getSeconds());
}

function extractDate(dateTime) {
  return new Date(dateTime.getFullYear(), dateTime.getMonth(), dateTime.getDate());
}

function addDays(date, days=1) {
  return new Date(date.getTime() + days * 24 * 3600 * 1000);
}

function syncCalendar() {
  var expectedColumns = ["id", "title", "location", "startDate", "startTime", "endDate", "endTime", "description", "color"];
  var sheet = SpreadsheetApp.getActiveSheet();
  var calendarId = sheet.getName();

  // https://developers.google.com/apps-script/reference/calendar/calendar-app
  var calendar = CalendarApp.getCalendarById(calendarId);
  
  var range = sheet.getDataRange();
  var isFillMode = range.isBlank();
  if (isFillMode) {
    range = sheet.getRange(1, 1, 1, expectedColumns.length);
    range.setValues([expectedColumns]);
    
    sheet.getRange(1, expectedColumns.indexOf('color') + 1).setNote('https://developers.google.com/apps-script/reference/calendar/event-color');
  }
  
  var values = range.getValues();
  var fontWeights = range.getFontWeights();
  
  var headers = values[0];
  var indices = {};
  headers.forEach(function(name, i) {
    indices[name] = i;
  });
  expectedColumns.forEach(function(name) {
    if (!(name in indices)) {
      throw "Could not find column " + name + " in " + JSON.stringify(headers) + " (expected case-sensitive column names: " + JSON.stringify(expectedColumns) + ")";
    }
  });
  
  if (isFillMode) {
    sheet.setFrozenRows(1);

    function getColumn(index) {
      return sheet.getRange(1, index + 1, sheet.getMaxRows());
    }
    for (const index of [indices.id]) {
      getColumn(index).setWrapStrategy(SpreadsheetApp.WrapStrategy.CLIP);
    }
    for (const index of [indices.startDate, indices.endDate]) {
      getColumn(index).setNumberFormat('dd/mm/yyyy');
    }
    for (const index of [indices.startTime, indices.endTime]) {
      getColumn(index).setNumberFormat('hh:mm');
    }
    for (const index of [indices.title, indices.description, indices.location]) {
      getColumn(index).setWrapStrategy(SpreadsheetApp.WrapStrategy.WRAP);
    }

    var oneYear = 365 * 24 * 60 * 60 * 1000;
    var now = new Date();
    var events = calendar.getEvents(
      // now, // Or any other past date of your choice?
      new Date(now.getTime() - 10 * oneYear),
      new Date(now.getTime() + 20 * oneYear),
      {
        max: 1000
      });
    Logger.log('Number of events: ' + events.length);

    const idsSeen = new Set();
    for (var i = 0; i < events.length; i++) {
      if (i && ((i % 20) == 0)) {
        SpreadsheetApp.flush();
      }
      function setColumn(index, value) {
        sheet.getRange(i + 2, index + 1).setValue(value);
      }
      const event = events[i];
      const id = event.getId();
      // if (event.getEventSeries().getId() != event.getId()) {
      if (idsSeen.has(id)) {
        continue;
      }
      idsSeen.add(id);

      setColumn(indices.id, event.getId());
      if (event.isAllDayEvent()) {
        const startDate = extractDate(event.getAllDayStartDate());
        const endDate = extractDate(new Date(event.getAllDayEndDate().getTime() - 1));
        const isSameDate = startDate.getTime() == endDate.getTime();

        setColumn(indices.startDate, event.getAllDayStartDate());
        const lastDateTime = new Date(event.getAllDayEndDate().getTime() - 1);
        setColumn(indices.endDate, isSameDate ? '' : lastDateTime);
      } else {
        const startDate = extractDate(event.getStartTime());
        const endDate = extractDate(event.getEndTime());
        const isSameDate = startDate.getTime() == endDate.getTime();

        setColumn(indices.startDate, startDate);
        setColumn(indices.startTime, event.getStartTime());
        setColumn(indices.endDate, isSameDate ? '' : endDate);
        setColumn(indices.endTime, event.getEndTime());
      }
      setColumn(indices.title, event.getTitle());
      setColumn(indices.description, event.getDescription());
      setColumn(indices.location, event.getLocation());
      setColumn(indices.color, event.getColor());
    }
    sheet.getDataRange().setFontWeight("normal");
    return;
  }
  for (var i = 1; i < values.length; i++) {    
    if (fontWeights[i].indexOf("bold") < 0) {
      continue;
    }
    var row = {};
    var bolds = {};
    for (var j = 0; j < headers.length; j++) {
      row[headers[j]] = values[i][j];
      bolds[headers[j]] = fontWeights[i][j] == "bold";
    }
    
    function setColumn(index, value) {
      range.getCell(i + 1, index + 1).setValue(value);
    }
    function resetColumnWeight(index) {
      range.getCell(i + 1, index + 1).setFontWeight("normal");
    }
    
    if (row.id.trim() == '') {
      if (!row.startDate) {
        Logger.log("Error: no startDate on row " + (i + 1));
        continue;
      }
      // Logger.log(row);
      let event;
      const options = {
        location: row.location,
        description: row.description,
      };
      if (row.startTime) {
        event = calendar.createEvent(
          row.title,
          composeDateTime(row.startDate, row.startTime),
          composeDateTime(row.endDate || row.startDate, row.endTime || new Date(0, 0, 0, 0, defaultEventDurationMinutes)),
          options);
      } else {
        event = calendar.createAllDayEvent(
          row.title,
          row.startDate,
          row.endDate || addOneDay(row.startDate),
          options);
      }
        
      setColumn(indices.id, event.getId());
      
      resetColumnWeight(indices.title);
      resetColumnWeight(indices.startDate);
      resetColumnWeight(indices.startTime);
      resetColumnWeight(indices.endDate);
      resetColumnWeight(indices.endTime);
      resetColumnWeight(indices.location);
      resetColumnWeight(indices.description);
    } else {
      var event = calendar.getEventById(row.id);
      
      if (bolds.startDate || bolds.endDate || bolds.startTime || bolds.endTime) {
        if (!row.startDate) {
          Logger.log("Error: no startDate on row " + (i + 1));
          continue;
        }

        if (row.startTime) {
          const startTime = composeDateTime(row.startDate, row.startTime);
          const endTime = composeDateTime(row.endDate || row.startDate, row.endTime || new Date(0, 0, 0, 0, defaultEventDurationMinutes));
          event.setTime(startTime, endTime);
        } else {
          event.setAllDayDates(row.startDate, row.endDate || addOneDay(row.startDate));
        }
        resetColumnWeight(indices.startDate);
        resetColumnWeight(indices.startTime);
        resetColumnWeight(indices.endDate);
        resetColumnWeight(indices.endTime);
      }
      
      if (bolds.title) {
        event.setTitle(row.title);
        resetColumnWeight(indices.title);
      } else {
        setColumn(indices.title, event.getTitle());
      }
      if (bolds.description) {
        event.setDescription(row.description);
        resetColumnWeight(indices.description);
      } else {
        setColumn(indices.description, event.getDescription());
      }
      if (bolds.location) {
        event.setLocation(row.location);
        resetColumnWeight(indices.location);
      } else {
        setColumn(indices.location, event.getLocation());
      }
      if ('delete' in indices) {
        function isReallyTrue(value) {
          return value === true || (value instanceof String && value.toLowerCase() === 'true');
        }
        if (isReallyTrue(row['delete'])) {
          event.deleteEvent();

        }
      }
      if ('color' in indices) {
        if (bolds.color && row.color != '') {
          event.setColor(row.color);
          resetColumnWeight(indices.color);
        } else {
          setColumn(indices.color, event.getColor());
        }
      }
    }
    resetColumnWeight(indices.id);
  }
}