You have been creating or deleting too many calendars or calendar events in a short time. Please try again later.

[casadeferro]

Dear Dave, many thanks for this excellent script that I've been using to schedule the cleaning of 3 apartments I run on Airbnb. Unfortunately the last few days I've been getting the same error every time I run the script.

"You have been creating or deleting too many calendars or calendar events in a short time. Please try again later."

I've seen there's a similar closed issue from May 2017, but I believe I'm using the updated version of your script and can't get it to work as it was before.

I've also noted that the script quintupled a few events on my calendar.

Please let me know if you glimpse a workaround while it isn't fixed. many thanks in advance.

// Script to synchronize a calendar to a spreadsheet and vice versa.
//
// See https://github.com/Davepar/gcalendarsync for instructions on setting this up.
//

// Set this value to match your calendar!!!
// Calendar ID can be found in the "Calendar Address" section of the Calendar Settings.
var calendarId = '<deleted>@group.calendar.google.com';

// Set the beginning and end dates that should be synced. beginDate can be set to Date() to use
// today. The numbers are year, month, date, where month is 0 for Jan through 11 for Dec.
var beginDate = new Date(1970, 0, 1);  // Default to Jan 1, 1970
var endDate = new Date(2500, 0, 1);  // Default to Jan 1, 2500

// Date format to use in the spreadsheet. EDIT
var dateFormat = 'dd/mm/yyyy hh:mm:ss';

var titleRowMap = {
  'title': 'Title',
  'description': 'Description',
  'location': 'Location',
  'starttime': 'Start Time',
  'endtime': 'End Time',
  'guests': 'Guests',
  'color': 'Color',
  'id': 'Id'
};
var titleRowKeys = ['title', 'description', 'location', 'starttime', 'endtime', 'guests', 'color', 'id'];
var requiredFields = ['id', 'title', 'starttime', 'endtime'];

// This controls whether email invites are sent to guests when the event is created in the
// calendar. Note that any changes to the event will cause email invites to be resent.
var SEND_EMAIL_INVITES = true;

// Setting this to true will silently skip rows that have a blank start and end time
// instead of popping up an error dialog.
var SKIP_BLANK_ROWS = true;

// Updating too many events in a short time period triggers an error. These values
// were tested for updating 40 events. Modify these values if you're still seeing errors.
var THROTTLE_THRESHOLD = 10;
var THROTTLE_SLEEP_TIME = 75;

// Adds the custom menu to the active spreadsheet.
function onOpen() {
  var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  var menuEntries = [
//    {
//      name: "Update from Calendar",
//      functionName: "syncFromCalendar"
//    }, 
    {
      name: "Update to Limpezas",
      functionName: "syncToCalendar"
    }
  ];
  spreadsheet.addMenu('Calendar Sync', menuEntries);
}

// Creates a mapping array between spreadsheet column and event field name
function createIdxMap(row) {
  var idxMap = [];
  for (var idx = 0; idx < row.length; idx++) {
    var fieldFromHdr = row[idx];
    for (var titleKey in titleRowMap) {
      if (titleRowMap[titleKey] == fieldFromHdr) {
        idxMap.push(titleKey);
        break;
      }
    }
    if (idxMap.length <= idx) {
      // Header field not in map, so add null
      idxMap.push(null);
    }
  }
  return idxMap;
}

// Converts a spreadsheet row into an object containing event-related fields
function reformatEvent(row, idxMap, keysToAdd) {
  var reformatted = row.reduce(function(event, value, idx) {
    if (idxMap[idx] != null) {
      event[idxMap[idx]] = value;
    }
    return event;
  }, {});
  for (var k in keysToAdd) {
    reformatted[keysToAdd[k]] = '';
  }
  return reformatted;
}

// Converts a calendar event to a psuedo-sheet event.
function convertCalEvent(calEvent) {
  convertedEvent = {
    'id': calEvent.getId(),
    'title': calEvent.getTitle(),
    'description': calEvent.getDescription(),
    'location': calEvent.getLocation(),
    'guests': calEvent.getGuestList().map(function(x) {return x.getEmail();}).join(','),
    'color': calEvent.getColor()
  };
  if (calEvent.isAllDayEvent()) {
    convertedEvent.starttime = calEvent.getAllDayStartDate();
    var endtime = calEvent.getAllDayEndDate();
    if (endtime - convertedEvent.starttime === 24 * 3600 * 1000) {
      convertedEvent.endtime = '';
    } else {
      convertedEvent.endtime = endtime;
      if (endtime.getHours() === 0 && endtime.getMinutes() == 0) {
        convertedEvent.endtime.setSeconds(endtime.getSeconds() - 1);
      }
    }
  } else {
    convertedEvent.starttime = calEvent.getStartTime();
    convertedEvent.endtime = calEvent.getEndTime();
  }
  return convertedEvent;
}

// Converts calendar event into spreadsheet data row
function calEventToSheet(calEvent, idxMap, dataRow) {
  convertedEvent = convertCalEvent(calEvent);

  for (var idx = 0; idx < idxMap.length; idx++) {
    if (idxMap[idx] !== null) {
      dataRow[idx] = convertedEvent[idxMap[idx]];
    }
  }
}

// Returns empty string or time in milliseconds for Date object
function getEndTime(ev) {
  return ev.endtime === '' ? '' : ev.endtime.getTime();
}

// Tests whether calendar event matches spreadsheet event
function eventMatches(cev, sev) {
  var convertedCalEvent = convertCalEvent(cev);
  return convertedCalEvent.title == sev.title &&
    convertedCalEvent.description == sev.description &&
    convertedCalEvent.location == sev.location &&
    convertedCalEvent.starttime.toString() == sev.starttime.toString() &&
    getEndTime(convertedCalEvent) === getEndTime(sev) &&
    convertedCalEvent.guests == sev.guests &&
    convertedCalEvent.color == ('' + sev.color);
}

// Determine whether required fields are missing
function areRequiredFieldsMissing(idxMap) {
  return requiredFields.some(function(val) {
    return idxMap.indexOf(val) < 0;
  });
}

// Returns list of fields that aren't in spreadsheet
function missingFields(idxMap) {
  return titleRowKeys.filter(function(val) {
    return idxMap.indexOf(val) < 0;
  });
}

// Set up formats and hide ID column for empty spreadsheet
function setUpSheet(sheet, fieldKeys) {
  sheet.getRange(1, fieldKeys.indexOf('starttime') + 1, 999).setNumberFormat(dateFormat);
  sheet.getRange(1, fieldKeys.indexOf('endtime') + 1, 999).setNumberFormat(dateFormat);
  sheet.hideColumns(fieldKeys.indexOf('id') + 1);
}

// Display error alert
function errorAlert(msg, evt, ridx) {
  var ui = SpreadsheetApp.getUi();
  if (evt) {
    ui.alert('Skipping row: ' + msg + ' in event "' + evt.title + '", row ' + (ridx + 1));
  } else {
    ui.alert(msg);
  }
}

// Updates a calendar event from a sheet event.
function updateEvent(calEvent, sheetEvent){
  sheetEvent.sendInvites = SEND_EMAIL_INVITES;
  if (sheetEvent.endtime === '') {
    calEvent.setAllDayDate(sheetEvent.starttime);
  } else {
    calEvent.setTime(sheetEvent.starttime, sheetEvent.endtime);
  }
  calEvent.setTitle(sheetEvent.title);
  calEvent.setDescription(sheetEvent.description);
  calEvent.setLocation(sheetEvent.location);
  // Set event color
  if (sheetEvent.color > 0 && sheetEvent.color < 12) {
    calEvent.setColor('' + sheetEvent.color);
  }
  var guestCal = calEvent.getGuestList().map(function (x) {
    return {
      email: x.getEmail(),
      added: false
    };
  });
  var sheetGuests = sheetEvent.guests || '';
  var guests = sheetGuests.split(',').map(function (x) {
    return x ? x.trim() : '';
  });
  // Check guests that are already invited.
  for (var gIx = 0; gIx < guestCal.length; gIx++) {
    var index = guests.indexOf(guestCal[gIx].email);
    if (index >= 0) {
      guestCal[gIx].added = true;
      guests.splice(index, 1);
    }
  }
  guests.forEach(function (x) {
    if (x) calEvent.addGuest(x);
  });
  guestCal.forEach(function (x) {
    if (!x.added) {
      calEvent.removeGuest(x.email);
    }
  });
}

// Synchronize from calendar to spreadsheet.
function syncFromCalendar() {
  // Get calendar and events
  var calendar = CalendarApp.getCalendarById(calendarId);
  var calEvents = calendar.getEvents(beginDate, endDate);

  // Get spreadsheet and data
  var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = spreadsheet.getActiveSheet();
  var range = sheet.getDataRange();
  var data = range.getValues();
  var eventFound = new Array(data.length);

  // Check if spreadsheet is empty and add a title row
  var titleRow = [];
  for (var idx = 0; idx < titleRowKeys.length; idx++) {
    titleRow.push(titleRowMap[titleRowKeys[idx]]);
  }
  if (data.length < 1) {
    data.push(titleRow);
    range = sheet.getRange(1, 1, data.length, data[0].length);
    range.setValues(data);
    setUpSheet(sheet, titleRowKeys);
  }

  if (data.length == 1 && data[0].length == 1 && data[0][0] === '') {
    data[0] = titleRow;
    range = sheet.getRange(1, 1, data.length, data[0].length);
    range.setValues(data);
    setUpSheet(sheet, titleRowKeys);
  }

  // Map spreadsheet headers to indices
  var idxMap = createIdxMap(data[0]);
  var idIdx = idxMap.indexOf('id');

  // Verify header has all required fields
  if (areRequiredFieldsMissing(idxMap)) {
    var reqFieldNames = requiredFields.map(function(x) {return titleRowMap[x];}).join(', ');
    errorAlert('Spreadsheet must have ' + reqFieldNames + ' columns');
    return;
  }

  // Array of IDs in the spreadsheet
  var sheetEventIds = data.slice(1).map(function(row) {return row[idIdx];});

  // Loop through calendar events
  for (var cidx = 0; cidx < calEvents.length; cidx++) {
    var calEvent = calEvents[cidx];
    var calEventId = calEvent.getId();

    var ridx = sheetEventIds.indexOf(calEventId) + 1;
    if (ridx < 1) {
      // Event not found, create it
      ridx = data.length;
      var newRow = [];
      var rowSize = idxMap.length;
      while (rowSize--) newRow.push('');
      data.push(newRow);
    } else {
      eventFound[ridx] = true;
    }
    // Update event in spreadsheet data
    calEventToSheet(calEvent, idxMap, data[ridx]);
  }

  // Remove any data rows not found in the calendar
  var rowsDeleted = 0;
  for (var idx = eventFound.length - 1; idx > 0; idx--) {
    //event doesn't exists and has an event id
    if (!eventFound[idx] && sheetEventIds[idx - 1]) {
      data.splice(idx, 1);
      rowsDeleted++;
    }
  }

  // Save spreadsheet changes
  range = sheet.getRange(1, 1, data.length, data[0].length);
  range.setValues(data);
  if (rowsDeleted > 0) {
    sheet.deleteRows(data.length + 1, rowsDeleted);
  }
}

// Synchronize from spreadsheet to calendar.
function syncToCalendar() {
  // Get calendar and events
  var calendar = CalendarApp.getCalendarById(calendarId);
  if (!calendar) {
    errorAlert('Cannot find calendar. Check instructions for set up.');
  }
  var calEvents = calendar.getEvents(beginDate, endDate);
  var calEventIds = calEvents.map(function(val) {return val.getId();});

  // Get spreadsheet and data
  var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = spreadsheet.getActiveSheet();
  var range = sheet.getDataRange();
  var data = range.getValues();
  if (data.length < 2) {
    errorAlert('Spreadsheet must have a title row and at least one data row');
    return;
  }

  // Map headers to indices
  var idxMap = createIdxMap(data[0]);
  var idIdx = idxMap.indexOf('id');
  var idRange = range.offset(0, idIdx, data.length, 1);
  var idData = idRange.getValues()

  // Verify header has all required fields
  if (areRequiredFieldsMissing(idxMap)) {
    var reqFieldNames = requiredFields.map(function(x) {return titleRowMap[x];}).join(', ');
    errorAlert('Spreadsheet must have ' + reqFieldNames + ' columns');
    return;
  }

  var keysToAdd = missingFields(idxMap);

  // Loop through spreadsheet rows
  var numChanges = 0;
  var numUpdated = 0;
  var changesMade = false;
  for (var ridx = 1; ridx < data.length; ridx++) {
    var sheetEvent = reformatEvent(data[ridx], idxMap, keysToAdd);

    // If enabled, skip rows with blank/invalid start and end times
    if (SKIP_BLANK_ROWS && !(sheetEvent.starttime instanceof Date) &&
        !(sheetEvent.endtime instanceof Date)) {
      continue;
    }

    // Do some error checking first
    if (!sheetEvent.title) {
      errorAlert('must have title', sheetEvent, ridx);
      continue;
    }
    if (!(sheetEvent.starttime instanceof Date)) {
      errorAlert('start time must be a date/time', sheetEvent, ridx);
      continue;
    }
    if (sheetEvent.endtime !== '') {
      if (!(sheetEvent.endtime instanceof Date)) {
        errorAlert('end time must be empty or a date/time', sheetEvent, ridx);
        continue;
      }
      if (sheetEvent.endtime < sheetEvent.starttime) {
        errorAlert('end time must be after start time for event', sheetEvent, ridx);
        continue;
      }
    }

    // Ignore events outside of the begin/end range desired.
    if (sheetEvent.starttime > endDate) {
      continue;
    }
    if (sheetEvent.endtime === '') {
      if (sheetEvent.starttime < beginDate) {
        continue;
      }
    } else {
      if (sheetEvent.endtime < beginDate) {
        continue;
      }
    }

    // Determine if spreadsheet event is already in calendar and matches
    var addEvent = true;
    if (sheetEvent.id) {
      var eventIdx = calEventIds.indexOf(sheetEvent.id);
      if (eventIdx >= 0) {
        calEventIds[eventIdx] = null;  // Prevents removing event below
        addEvent = false;
        var calEvent = calEvents[eventIdx];
        if (!eventMatches(calEvent, sheetEvent)) {
          // Update the event
          updateEvent(calEvent, sheetEvent);

          // Maybe throttle updates.
          numChanges++;
          if (numChanges > THROTTLE_THRESHOLD) {
            Utilities.sleep(THROTTLE_SLEEP_TIME);
          }
        }
      }
    }
    if (addEvent) {
      var newEvent;
      sheetEvent.sendInvites = SEND_EMAIL_INVITES;
      if (sheetEvent.endtime === '') {
        newEvent = calendar.createAllDayEvent(sheetEvent.title, sheetEvent.starttime, sheetEvent);
      } else {
        newEvent = calendar.createEvent(sheetEvent.title, sheetEvent.starttime, sheetEvent.endtime, sheetEvent);
      }
      // Put event ID back into spreadsheet
      idData[ridx][0] = newEvent.getId();
      changesMade = true;

      // Set event color
      if (sheetEvent.color > 0 && sheetEvent.color < 12) {
        newEvent.setColor('' + sheetEvent.color);
      }

      // Maybe throttle updates.
      numChanges++;
      if (numChanges > THROTTLE_THRESHOLD) {
        Utilities.sleep(THROTTLE_SLEEP_TIME);
      }
    }
  }

  // Save spreadsheet changes
  if (changesMade) {
    idRange.setValues(idData);
  }

  // Remove any calendar events not found in the spreadsheet
  var numToRemove = calEventIds.reduce(function(prevVal, curVal) {
    if (curVal !== null) {
      prevVal++;
    }
    return prevVal;
  }, 0);
  if (numToRemove > 0) {
    var ui = SpreadsheetApp.getUi();
    var response = ui.Button.YES;
    if (numToRemove > numUpdated) {
      response = ui.alert('Delete ' + numToRemove + ' calendar event(s) not found in spreadsheet?',
          ui.ButtonSet.YES_NO);
    }
    if (response == ui.Button.YES) {
      calEventIds.forEach(function(id, idx) {
        if (id != null) {
          calEvents[idx].deleteEvent();
          Utilities.sleep(20);
        }
      });
    }
  }
  Logger.log('Updated %s calendar events', numChanges);
}

// Set up a trigger to automatically update the calendar when the spreadsheet is
// modified. See the instructions for how to use this.
function createSpreadsheetEditTrigger() {
  var ss = SpreadsheetApp.getActive();
  ScriptApp.newTrigger('syncToCalendar')
      .forSpreadsheet(ss)
      .onEdit()
      .create();
}

// Delete the trigger. Use this to stop automatically updating the calendar.
function deleteTrigger() {
  // Loop over all triggers.
  var allTriggers = ScriptApp.getProjectTriggers();
  for (var idx = 0; idx < allTriggers.length; idx++) {
    if (allTriggers[idx].getHandlerFunction() === 'syncToCalendar') {
      ScriptApp.deleteTrigger(allTriggers[idx]);
    }
  }
}

[brianfigg]

I also get this alert, on ocassion (not everytime, but nearly every other time). Why does this happen? How is it fixed? Thank you.

On Thu, Nov 29, 2018 at 6:20 AM casadeferro notifications@github.com wrote:

Dear Dave, many thanks for this excellent script that I've been using to schedule the cleaning of 3 apartments I run on Airbnb. Unfortunately the last few days I've been getting the same error every time I run the script.

"You have been creating or deleting too many calendars or calendar events in a short time. Please try again later."

I've seen there's a similar closed issue from May 2017, but I believe I'm using the updated version of your script and can't get it to work as it was before.

I've also noted that the script quintupled a few events on my calendar.

Please let me know if you glimpse a workaround while it isn't fixed. many thanks in advance.

`// Script to synchronize a calendar to a spreadsheet and vice versa. // // See https://github.com/Davepar/gcalendarsync for instructions on setting this up. //

// Set this value to match your calendar!!! // Calendar ID can be found in the "Calendar Address" section of the Calendar Settings. var calendarId = '@group.calendar.google.com';

// Set the beginning and end dates that should be synced. beginDate can be set to Date() to use // today. The numbers are year, month, date, where month is 0 for Jan through 11 for Dec. var beginDate = new Date(1970, 0, 1); // Default to Jan 1, 1970 var endDate = new Date(2500, 0, 1); // Default to Jan 1, 2500

// Date format to use in the spreadsheet. EDIT var dateFormat = 'dd/mm/yyyy hh:mm:ss';

var titleRowMap = { 'title': 'Title', 'description': 'Description', 'location': 'Location', 'starttime': 'Start Time', 'endtime': 'End Time', 'guests': 'Guests', 'color': 'Color', 'id': 'Id' }; var titleRowKeys = ['title', 'description', 'location', 'starttime', 'endtime', 'guests', 'color', 'id']; var requiredFields = ['id', 'title', 'starttime', 'endtime'];

// This controls whether email invites are sent to guests when the event is created in the // calendar. Note that any changes to the event will cause email invites to be resent. var SEND_EMAIL_INVITES = true;

// Setting this to true will silently skip rows that have a blank start and end time // instead of popping up an error dialog. var SKIP_BLANK_ROWS = true;

// Updating too many events in a short time period triggers an error. These values // were tested for updating 40 events. Modify these values if you're still seeing errors. var THROTTLE_THRESHOLD = 10; var THROTTLE_SLEEP_TIME = 75;

// Adds the custom menu to the active spreadsheet. function onOpen() { var spreadsheet = SpreadsheetApp.getActiveSpreadsheet(); var menuEntries = [ // { // name: "Update from Calendar", // functionName: "syncFromCalendar" // }, { name: "Update to Limpezas", functionName: "syncToCalendar" } ]; spreadsheet.addMenu('Calendar Sync', menuEntries); }

// Creates a mapping array between spreadsheet column and event field name function createIdxMap(row) { var idxMap = []; for (var idx = 0; idx < row.length; idx++) { var fieldFromHdr = row[idx]; for (var titleKey in titleRowMap) { if (titleRowMap[titleKey] == fieldFromHdr) { idxMap.push(titleKey); break; } } if (idxMap.length <= idx) { // Header field not in map, so add null idxMap.push(null); } } return idxMap; }

// Converts a spreadsheet row into an object containing event-related fields function reformatEvent(row, idxMap, keysToAdd) { var reformatted = row.reduce(function(event, value, idx) { if (idxMap[idx] != null) { event[idxMap[idx]] = value; } return event; }, {}); for (var k in keysToAdd) { reformatted[keysToAdd[k]] = ''; } return reformatted; }

// Converts a calendar event to a psuedo-sheet event. function convertCalEvent(calEvent) { convertedEvent = { 'id': calEvent.getId(), 'title': calEvent.getTitle(), 'description': calEvent.getDescription(), 'location': calEvent.getLocation(), 'guests': calEvent.getGuestList().map(function(x) {return x.getEmail();}).join(','), 'color': calEvent.getColor() }; if (calEvent.isAllDayEvent()) { convertedEvent.starttime = calEvent.getAllDayStartDate(); var endtime = calEvent.getAllDayEndDate(); if (endtime - convertedEvent.starttime === 24 3600 1000) { convertedEvent.endtime = ''; } else { convertedEvent.endtime = endtime; if (endtime.getHours() === 0 && endtime.getMinutes() == 0) { convertedEvent.endtime.setSeconds(endtime.getSeconds() - 1); } } } else { convertedEvent.starttime = calEvent.getStartTime(); convertedEvent.endtime = calEvent.getEndTime(); } return convertedEvent; }

// Converts calendar event into spreadsheet data row function calEventToSheet(calEvent, idxMap, dataRow) { convertedEvent = convertCalEvent(calEvent);

for (var idx = 0; idx < idxMap.length; idx++) { if (idxMap[idx] !== null) { dataRow[idx] = convertedEvent[idxMap[idx]]; } } }

// Returns empty string or time in milliseconds for Date object function getEndTime(ev) { return ev.endtime === '' ? '' : ev.endtime.getTime(); }

// Tests whether calendar event matches spreadsheet event function eventMatches(cev, sev) { var convertedCalEvent = convertCalEvent(cev); return convertedCalEvent.title == sev.title && convertedCalEvent.description == sev.description && convertedCalEvent.location == sev.location && convertedCalEvent.starttime.toString() == sev.starttime.toString() && getEndTime(convertedCalEvent) === getEndTime(sev) && convertedCalEvent.guests == sev.guests && convertedCalEvent.color == ('' + sev.color); }

// Determine whether required fields are missing function areRequiredFieldsMissing(idxMap) { return requiredFields.some(function(val) { return idxMap.indexOf(val) < 0; }); }

// Returns list of fields that aren't in spreadsheet function missingFields(idxMap) { return titleRowKeys.filter(function(val) { return idxMap.indexOf(val) < 0; }); }

// Set up formats and hide ID column for empty spreadsheet function setUpSheet(sheet, fieldKeys) { sheet.getRange(1, fieldKeys.indexOf('starttime') + 1, 999).setNumberFormat(dateFormat); sheet.getRange(1, fieldKeys.indexOf('endtime') + 1, 999).setNumberFormat(dateFormat); sheet.hideColumns(fieldKeys.indexOf('id') + 1); }

// Display error alert function errorAlert(msg, evt, ridx) { var ui = SpreadsheetApp.getUi(); if (evt) { ui.alert('Skipping row: ' + msg + ' in event "' + evt.title + '", row ' + (ridx + 1)); } else { ui.alert(msg); } }

// Updates a calendar event from a sheet event. function updateEvent(calEvent, sheetEvent){ sheetEvent.sendInvites = SEND_EMAIL_INVITES; if (sheetEvent.endtime === '') { calEvent.setAllDayDate(sheetEvent.starttime); } else { calEvent.setTime(sheetEvent.starttime, sheetEvent.endtime); } calEvent.setTitle(sheetEvent.title); calEvent.setDescription(sheetEvent.description); calEvent.setLocation(sheetEvent.location); // Set event color if (sheetEvent.color > 0 && sheetEvent.color < 12) { calEvent.setColor('' + sheetEvent.color); } var guestCal = calEvent.getGuestList().map(function (x) { return { email: x.getEmail(), added: false }; }); var sheetGuests = sheetEvent.guests || ''; var guests = sheetGuests.split(',').map(function (x) { return x ? x.trim() : ''; }); // Check guests that are already invited. for (var gIx = 0; gIx < guestCal.length; gIx++) { var index = guests.indexOf(guestCal[gIx].email); if (index >= 0) { guestCal[gIx].added = true; guests.splice(index, 1); } } guests.forEach(function (x) { if (x) calEvent.addGuest(x); }); guestCal.forEach(function (x) { if (!x.added) { calEvent.removeGuest(x.email); } }); }

// Synchronize from calendar to spreadsheet. function syncFromCalendar() { // Get calendar and events var calendar = CalendarApp.getCalendarById(calendarId); var calEvents = calendar.getEvents(beginDate, endDate);

// Get spreadsheet and data var spreadsheet = SpreadsheetApp.getActiveSpreadsheet(); var sheet = spreadsheet.getActiveSheet(); var range = sheet.getDataRange(); var data = range.getValues(); var eventFound = new Array(data.length);

// Check if spreadsheet is empty and add a title row var titleRow = []; for (var idx = 0; idx < titleRowKeys.length; idx++) { titleRow.push(titleRowMap[titleRowKeys[idx]]); } if (data.length < 1) { data.push(titleRow); range = sheet.getRange(1, 1, data.length, data[0].length); range.setValues(data); setUpSheet(sheet, titleRowKeys); }

if (data.length == 1 && data[0].length == 1 && data[0][0] === '') { data[0] = titleRow; range = sheet.getRange(1, 1, data.length, data[0].length); range.setValues(data); setUpSheet(sheet, titleRowKeys); }

// Map spreadsheet headers to indices var idxMap = createIdxMap(data[0]); var idIdx = idxMap.indexOf('id');

// Verify header has all required fields if (areRequiredFieldsMissing(idxMap)) { var reqFieldNames = requiredFields.map(function(x) {return titleRowMap[x];}).join(', '); errorAlert('Spreadsheet must have ' + reqFieldNames + ' columns'); return; }

// Array of IDs in the spreadsheet var sheetEventIds = data.slice(1).map(function(row) {return row[idIdx];});

// Loop through calendar events for (var cidx = 0; cidx < calEvents.length; cidx++) { var calEvent = calEvents[cidx]; var calEventId = calEvent.getId();

var ridx = sheetEventIds.indexOf(calEventId) + 1; if (ridx < 1) { // Event not found, create it ridx = data.length; var newRow = []; var rowSize = idxMap.length; while (rowSize--) newRow.push(''); data.push(newRow); } else { eventFound[ridx] = true; } // Update event in spreadsheet data calEventToSheet(calEvent, idxMap, data[ridx]);

}

// Remove any data rows not found in the calendar var rowsDeleted = 0; for (var idx = eventFound.length - 1; idx > 0; idx--) { //event doesn't exists and has an event id if (!eventFound[idx] && sheetEventIds[idx - 1]) { data.splice(idx, 1); rowsDeleted++; } }

// Save spreadsheet changes range = sheet.getRange(1, 1, data.length, data[0].length); range.setValues(data); if (rowsDeleted > 0) { sheet.deleteRows(data.length + 1, rowsDeleted); } }

// Synchronize from spreadsheet to calendar. function syncToCalendar() { // Get calendar and events var calendar = CalendarApp.getCalendarById(calendarId); if (!calendar) { errorAlert('Cannot find calendar. Check instructions for set up.'); } var calEvents = calendar.getEvents(beginDate, endDate); var calEventIds = calEvents.map(function(val) {return val.getId();});

// Get spreadsheet and data var spreadsheet = SpreadsheetApp.getActiveSpreadsheet(); var sheet = spreadsheet.getActiveSheet(); var range = sheet.getDataRange(); var data = range.getValues(); if (data.length < 2) { errorAlert('Spreadsheet must have a title row and at least one data row'); return; }

// Map headers to indices var idxMap = createIdxMap(data[0]); var idIdx = idxMap.indexOf('id'); var idRange = range.offset(0, idIdx, data.length, 1); var idData = idRange.getValues()

// Verify header has all required fields if (areRequiredFieldsMissing(idxMap)) { var reqFieldNames = requiredFields.map(function(x) {return titleRowMap[x];}).join(', '); errorAlert('Spreadsheet must have ' + reqFieldNames + ' columns'); return; }

var keysToAdd = missingFields(idxMap);

// Loop through spreadsheet rows var numChanges = 0; var numUpdated = 0; var changesMade = false; for (var ridx = 1; ridx < data.length; ridx++) { var sheetEvent = reformatEvent(data[ridx], idxMap, keysToAdd);

// If enabled, skip rows with blank/invalid start and end times if (SKIP_BLANK_ROWS && !(sheetEvent.starttime instanceof Date) && !(sheetEvent.endtime instanceof Date)) { continue; }

// Do some error checking first if (!sheetEvent.title) { errorAlert('must have title', sheetEvent, ridx); continue; } if (!(sheetEvent.starttime instanceof Date)) { errorAlert('start time must be a date/time', sheetEvent, ridx); continue; } if (sheetEvent.endtime !== '') { if (!(sheetEvent.endtime instanceof Date)) { errorAlert('end time must be empty or a date/time', sheetEvent, ridx); continue; } if (sheetEvent.endtime < sheetEvent.starttime) { errorAlert('end time must be after start time for event', sheetEvent, ridx); continue; } }

// Ignore events outside of the begin/end range desired. if (sheetEvent.starttime > endDate) { continue; } if (sheetEvent.endtime === '') { if (sheetEvent.starttime < beginDate) { continue; } } else { if (sheetEvent.endtime < beginDate) { continue; } }

// Determine if spreadsheet event is already in calendar and matches var addEvent = true; if (sheetEvent.id) { var eventIdx = calEventIds.indexOf(sheetEvent.id); if (eventIdx >= 0) { calEventIds[eventIdx] = null; // Prevents removing event below addEvent = false; var calEvent = calEvents[eventIdx]; if (!eventMatches(calEvent, sheetEvent)) { // Update the event updateEvent(calEvent, sheetEvent);

  // Maybe throttle updates.
  numChanges++;
  if (numChanges > THROTTLE_THRESHOLD) {
    Utilities.sleep(THROTTLE_SLEEP_TIME);
  }
}

} } if (addEvent) { var newEvent; sheetEvent.sendInvites = SEND_EMAIL_INVITES; if (sheetEvent.endtime === '') { newEvent = calendar.createAllDayEvent(sheetEvent.title, sheetEvent.starttime, sheetEvent); } else { newEvent = calendar.createEvent(sheetEvent.title, sheetEvent.starttime, sheetEvent.endtime, sheetEvent); } // Put event ID back into spreadsheet idData[ridx][0] = newEvent.getId(); changesMade = true;

// Set event color if (sheetEvent.color > 0 && sheetEvent.color < 12) { newEvent.setColor('' + sheetEvent.color); }

// Maybe throttle updates. numChanges++; if (numChanges > THROTTLE_THRESHOLD) { Utilities.sleep(THROTTLE_SLEEP_TIME); } }

}

// Save spreadsheet changes if (changesMade) { idRange.setValues(idData); }

// Remove any calendar events not found in the spreadsheet var numToRemove = calEventIds.reduce(function(prevVal, curVal) { if (curVal !== null) { prevVal++; } return prevVal; }, 0); if (numToRemove > 0) { var ui = SpreadsheetApp.getUi(); var response = ui.Button.YES; if (numToRemove > numUpdated) { response = ui.alert('Delete ' + numToRemove + ' calendar event(s) not found in spreadsheet?', ui.ButtonSet.YES_NO); } if (response == ui.Button.YES) { calEventIds.forEach(function(id, idx) { if (id != null) { calEvents[idx].deleteEvent(); Utilities.sleep(20); } }); } } Logger.log('Updated %s calendar events', numChanges); }

// Set up a trigger to automatically update the calendar when the spreadsheet is // modified. See the instructions for how to use this. function createSpreadsheetEditTrigger() { var ss = SpreadsheetApp.getActive(); ScriptApp.newTrigger('syncToCalendar') .forSpreadsheet(ss) .onEdit() .create(); }

// Delete the trigger. Use this to stop automatically updating the calendar. function deleteTrigger() { // Loop over all triggers. var allTriggers = ScriptApp.getProjectTriggers(); for (var idx = 0; idx < allTriggers.length; idx++) { if (allTriggers[idx].getHandlerFunction() === 'syncToCalendar') { ScriptApp.deleteTrigger(allTriggers[idx]); } } } `

— You are receiving this because you are subscribed to this thread. Reply to this email directly, view it on GitHub https://github.com/Davepar/gcalendarsync/issues/52, or mute the thread https://github.com/notifications/unsubscribe-auth/AiYM2cdfuZODWZRe4RCI9tV-6Bp0iie7ks5uz-1CgaJpZM4Y5xdV .

[standoes]

Me too.... Is there already a solution for this issue?

[towchristopher]

I'm also receiving this error.

[kevenson-umich]

Also receiving this error, hoping for an update soon!

[brianfigg]

Still getting this error.... how is it fixed?

[casadeferro]

Still getting it.

[davepar]

This problem is caused by the Google Calendar API limiting how many calendar events can be created or deleted within a certain time period. Unfortunately, they don't publish what the exact limit is. The script will start adding a delay when there are more than 10 events that need to be updated. You can try increasing this delay (called THROTTLE_SLEEP_TIME in the script) to a larger number. See if that helps.

It would be helpful if people could report approximately how many events they have in their spreadsheet when they're running into this error.

[casadeferro]

I have around 70 entries

[casadeferro]

var THROTTLE_THRESHOLD = 10; var THROTTLE_SLEEP_TIME = 75;

[brianfigg]

I have over 200 and have been attempting to adjust the two different numbers and still get this error randomly. It's working for now, but not sure when or why it gives me the errors. I find that if I delete and event or two it finds missing events and then eventually runs is correctly. Thanks for you time.

On Wed, Jun 26, 2019 at 2:28 PM Dave Parsons notifications@github.com wrote:

This problem is caused by the Google Calendar API limiting how many calendar events can be created or deleted within a certain time period. Unfortunately, they don't publish what the exact limit is. The script will start adding a delay when there are more than 10 events that need to be updated. You can try increasing this delay (called THROTTLE_SLEEP_TIME in the script) to a larger number. See if that helps.

It would be helpful if people could report approximately how many events they have in their spreadsheet when they're running into this error.

— You are receiving this because you commented. Reply to this email directly, view it on GitHub https://github.com/Davepar/gcalendarsync/issues/52?email_source=notifications&email_token=AITAZWJNMV34YFKIBPPCUBLP4PNRNA5CNFSM4GHHC5K2YY3PNVWWK3TUL52HS4DFVREXG43VMVBW63LNMVXHJKTDN5WW2ZLOORPWSZGODYU34WI#issuecomment-506052185, or mute the thread https://github.com/notifications/unsubscribe-auth/AITAZWNKWXATMYFXGEFLRGDP4PNRNANCNFSM4GHHC5KQ .

[casadeferro]

To work around I configured a trigger to run every hour so it bypasses the random error. Error rate: 28.99% of executions w/ errors Executions: 169 # of times your scripts ran

[davepar]

The other bit of info that would be helpful to know is what is changing on the events. Are you adding or removing a lot of events, or editing a particular field on every event? I found some info on quotas, but the script shouldn't be exceeding any of those for 70 or 200 events.

[brianfigg]

Hey Dave, I'm a school athletic director and use a master sheet to add game dates, with opponents, location and times. I add each game one at a time, and then edit times, and dates as they change occasionally. here's my sheet https://docs.google.com/spreadsheets/d/1OVnL0MuskPKm1if3TM-dxInOFjyTJeHbQqZ-O6_95Mo/edit?usp=sharing

On Wed, Jun 26, 2019 at 3:21 PM Dave Parsons notifications@github.com wrote:

The other bit of info that would be helpful to know is what is changing on the events. Are you adding or removing a lot of events, or editing a particular field on every event? I found some info on quotas https://developers.google.com/apps-script/guides/services/quotas, but the script shouldn't be exceeding any of those for 70 or 200 events.

— You are receiving this because you commented. Reply to this email directly, view it on GitHub https://github.com/Davepar/gcalendarsync/issues/52?email_source=notifications&email_token=AITAZWKB3HFSME2YCKZGA5TP4PTXJA5CNFSM4GHHC5K2YY3PNVWWK3TUL52HS4DFVREXG43VMVBW63LNMVXHJKTDN5WW2ZLOORPWSZGODYU7NFY#issuecomment-506066583, or mute the thread https://github.com/notifications/unsubscribe-auth/AITAZWNDX4HZBKDR6JW2OCDP4PTXJANCNFSM4GHHC5KQ .

[davepar]

Thanks! This is helpful for testing. I'll make a copy and try doing some updates to see if I can tell where the problem is. Just adding and updating a few events should not be causing this error.

[brianfigg]

Thank you very much

Sent from my iPhone

On Jun 28, 2019, at 11:46 AM, Dave Parsons notifications@github.com wrote:

Thanks! This is helpful for testing. I'll make a copy and try doing some updates to see if I can tell where the problem is. Just adding and updating a few events should not be causing this error.

— You are receiving this because you commented. Reply to this email directly, view it on GitHub, or mute the thread.

[davepar]

I made quite a few changes to this script to better handle lots of event updates. The error about creating or deleting too many events in a short time should be fixed now. It basically waits 200 milliseconds after making any changes to the calendar. This will slow things down. For example, updating 2 fields on 500 events will now take 2 500 200 = 200 seconds. The total maximum run time for a script is 6 minutes, so it will be forced to stop if there are too many updates. But people should rarely run into that unless they have thousands of events.

Let me know if there are any issues. I may have introduced a bug or two with all of these changes, but hopefully not.

[brianfigg]

Do I need to reload the script or make the changes manually you just mention, how do I do that?

On Sat, Jul 6, 2019 at 5:22 PM Dave Parsons notifications@github.com wrote:

I made quite a few changes to this script to better handle lots of event updates. The error about creating or deleting too many events in a short time should be fixed now. It basically waits 200 milliseconds after making any changes to the calendar. This will slow things down. For example, updating 2 fields on 500 events will now take 2 500 200 = 200 seconds. The total maximum run time for a script is 6 minutes, so it will be forced to stop if there are too many updates. But people should rarely run into that unless they have thousands of events.

Let me know if there are any issues. I may have introduced a bug or two with all of these changes, but hopefully not.

— You are receiving this because you commented. Reply to this email directly, view it on GitHub https://github.com/Davepar/gcalendarsync/issues/52?email_source=notifications&email_token=AITAZWLYMUTONHD6KCJVJCTP6EZK7A5CNFSM4GHHC5K2YY3PNVWWK3TUL52HS4DFVREXG43VMVBW63LNMVXHJKTDN5WW2ZLOORPWSZGODZLCK7A#issuecomment-508962172, or mute the thread https://github.com/notifications/unsubscribe-auth/AITAZWOG2THKIRW6DTV5QGDP6EZK7ANCNFSM4GHHC5KQ .

[davepar]

Copying the entire script again would be the easiest. I made a lot of changes in several places. So basically these steps:

• In the Tools menu, select Script Editor.
• Paste in the code from gcalendarsync.js.
• Paste in your "calendarId" value in the script.
• Save the script.

[brianfigg]

TY

On Sun, Jul 7, 2019 at 9:37 AM Dave Parsons notifications@github.com wrote:

Copying the entire script again would be the easiest. I made a lot of changes in several places. So basically these steps:

• In the Tools menu, select Script Editor.
• Paste in the code from gcalendarsync.js https://raw.githubusercontent.com/Davepar/gcalendarsync/master/gcalendarsync.js .
• Paste in your "calendarId" value in the script.
• Save the script.

— You are receiving this because you commented. Reply to this email directly, view it on GitHub https://github.com/Davepar/gcalendarsync/issues/52?email_source=notifications&email_token=AITAZWMKUGFAVMW2VPEYE33P6ILUDA5CNFSM4GHHC5K2YY3PNVWWK3TUL52HS4DFVREXG43VMVBW63LNMVXHJKTDN5WW2ZLOORPWSZGODZLO53Y#issuecomment-509013743, or mute the thread https://github.com/notifications/unsubscribe-auth/AITAZWIJGNPPD2VCECYQMXLP6ILUDANCNFSM4GHHC5KQ .

[brianfigg]

I just did the update you advised, and now I'm getting an error repeating that says Skipping row: must have title in event "", row 51 Skipping row: must have title in event "", row 52 ..... repeating for all cells that are blank. How do I stop this?

On Sun, Jul 7, 2019 at 9:38 AM Brian Figg brianfigg707@gmail.com wrote:

TY

On Sun, Jul 7, 2019 at 9:37 AM Dave Parsons notifications@github.com wrote:

Copying the entire script again would be the easiest. I made a lot of changes in several places. So basically these steps:

• In the Tools menu, select Script Editor.
• Paste in the code from gcalendarsync.js https://raw.githubusercontent.com/Davepar/gcalendarsync/master/gcalendarsync.js .
• Paste in your "calendarId" value in the script.
• Save the script.

— You are receiving this because you commented. Reply to this email directly, view it on GitHub https://github.com/Davepar/gcalendarsync/issues/52?email_source=notifications&email_token=AITAZWMKUGFAVMW2VPEYE33P6ILUDA5CNFSM4GHHC5K2YY3PNVWWK3TUL52HS4DFVREXG43VMVBW63LNMVXHJKTDN5WW2ZLOORPWSZGODZLO53Y#issuecomment-509013743, or mute the thread https://github.com/notifications/unsubscribe-auth/AITAZWIJGNPPD2VCECYQMXLP6ILUDANCNFSM4GHHC5KQ .

[davepar]

Every event must have a title. That's a requirement from GCalendar. Or there is a title, and you're still getting that error?

[brianfigg]

I tried your new script and I was getting an error for all of the empty rows after my 49 events. I went back to my old script and used your number adjustments and it’s work now.
So I wasnt able to use your other edits cuz I’m. It sure what hey were but your script didn’t work as I described. What’s your suggestion?

Sent from my iPhone

On Jul 7, 2019, at 10:05 AM, Dave Parsons notifications@github.com wrote:

Every event must have a title. That's a requirement from GCalendar. Or there is a title, and you're still getting that error?

— You are receiving this because you commented. Reply to this email directly, view it on GitHub, or mute the thread.

[davepar]

I'll take a look at the script tonight. Maybe I introduced a bug.

[brianfigg]

So I've got a new error since you edited it. ReferenceError: "THROTTLE_THRESHOLD" is not defined.DetailsDismiss

Here's my script

// Script to synchronize a calendar to a spreadsheet and vice versa. // // See https://github.com/Davepar/gcalendarsync for instructions on setting this up. //

// Set this value to match your calendar!!! // Calendar ID can be found in the "Calendar Address" section of the Calendar Settings. var calendarId = ' ulusd.org_3n6p3meuop99pnu7q7n270s67k@group.calendar.google.com';

// Set the beginning and end dates that should be synced. beginDate can be set to Date() to use // today. The numbers are year, month, date, where month is 0 for Jan through 11 for Dec. var beginDate = new Date(2018, 0, 1); // Default to Jan 1, 1970 var endDate = new Date(2500, 0, 1); // Default to Jan 1, 2500

// Date format to use in the spreadsheet. var dateFormat = 'M/d/yyyy H:mm';

var titleRowMap = { 'title': 'Title', 'description': 'Description', 'location': 'Location', 'starttime': 'Start Time', 'endtime': 'End Time', 'guests': 'Guests', 'color': 'Color', 'id': 'Id' }; var titleRowKeys = ['title', 'description', 'location', 'starttime', 'endtime', 'guests', 'color', 'id']; var requiredFields = ['id', 'title', 'starttime', 'endtime'];

// This controls whether email invites are sent to guests when the event is created in the // calendar. Note that any changes to the event will cause email invites to be resent. var SEND_EMAIL_INVITES = false;

// Setting this to true will silently skip rows that have a blank start and end time // instead of popping up an error dialog. var SKIP_BLANK_ROWS = true;

// Updating too many events in a short time period triggers an error. These values // were tested for updating 40 events. Modify these values if you're still seeing errors. var THROTTLE_SLEEP_TIME = 200; var MAX_RUN_TIME = 5.75 60 1000;

// Adds the custom menu to the active spreadsheet. function onOpen() { var spreadsheet = SpreadsheetApp.getActiveSpreadsheet(); var menuEntries = [ { name: ".", functionName: "syncFromCalendar" }, { name: "ULHS-Update TO Cal", functionName: "syncToCalendar" } ]; spreadsheet.addMenu('Calendar Sync', menuEntries); }

// Creates a mapping array between spreadsheet column and event field name function createIdxMap(row) { var idxMap = []; for (var idx = 0; idx < row.length; idx++) { var fieldFromHdr = row[idx]; for (var titleKey in titleRowMap) { if (titleRowMap[titleKey] == fieldFromHdr) { idxMap.push(titleKey); break; } } if (idxMap.length <= idx) { // Header field not in map, so add null idxMap.push(null); } } return idxMap; }

// Converts a spreadsheet row into an object containing event-related fields function reformatEvent(row, idxMap, keysToAdd) { var reformatted = row.reduce(function(event, value, idx) { if (idxMap[idx] != null) { event[idxMap[idx]] = value; } return event; }, {}); for (var k in keysToAdd) { reformatted[keysToAdd[k]] = ''; } return reformatted; }

// Converts a calendar event to a psuedo-sheet event. function convertCalEvent(calEvent) { convertedEvent = { 'id': calEvent.getId(), 'title': calEvent.getTitle(), 'description': calEvent.getDescription(), 'location': calEvent.getLocation(), 'guests': calEvent.getGuestList().map(function(x) {return x.getEmail();}).join(','), 'color': calEvent.getColor() }; if (calEvent.isAllDayEvent()) { convertedEvent.starttime = calEvent.getAllDayStartDate(); var endtime = calEvent.getAllDayEndDate(); if (endtime - convertedEvent.starttime === 24 3600 1000) { convertedEvent.endtime = ''; } else { convertedEvent.endtime = endtime; if (endtime.getHours() === 0 && endtime.getMinutes() == 0) { convertedEvent.endtime.setSeconds(endtime.getSeconds() - 1); } } } else { convertedEvent.starttime = calEvent.getStartTime(); convertedEvent.endtime = calEvent.getEndTime(); } return convertedEvent; }

// Converts calendar event into spreadsheet data row function calEventToSheet(calEvent, idxMap, dataRow) { convertedEvent = convertCalEvent(calEvent);

for (var idx = 0; idx < idxMap.length; idx++) { if (idxMap[idx] !== null) { dataRow[idx] = convertedEvent[idxMap[idx]]; } } }

// Returns empty string or time in milliseconds for Date object function getEndTime(ev) { return ev.endtime === '' ? '' : ev.endtime.getTime(); }

// Tests whether calendar event matches spreadsheet event function eventMatches(cev, sev) { var convertedCalEvent = convertCalEvent(cev); return convertedCalEvent.title == sev.title && convertedCalEvent.description == sev.description && convertedCalEvent.location == sev.location && convertedCalEvent.starttime.toString() == sev.starttime.toString() && getEndTime(convertedCalEvent) === getEndTime(sev) && convertedCalEvent.guests == sev.guests && convertedCalEvent.color == ('' + sev.color); }

// Determine whether required fields are missing function areRequiredFieldsMissing(idxMap) { return requiredFields.some(function(val) { return idxMap.indexOf(val) < 0; }); }

// Returns list of fields that aren't in spreadsheet function missingFields(idxMap) { return titleRowKeys.filter(function(val) { return idxMap.indexOf(val) < 0; }); }

// Set up formats and hide ID column for empty spreadsheet function setUpSheet(sheet, fieldKeys) { sheet.getRange(1, fieldKeys.indexOf('starttime') + 1, 999).setNumberFormat(dateFormat); sheet.getRange(1, fieldKeys.indexOf('endtime') + 1, 999).setNumberFormat(dateFormat); sheet.hideColumns(fieldKeys.indexOf('id') + 1); }

// Display error alert function errorAlert(msg, evt, ridx) { var ui = SpreadsheetApp.getUi(); if (evt) { ui.alert('Skipping row: ' + msg + ' in event "' + evt.title + '", row '

• (ridx + 1)); } else { ui.alert(msg); } }

// Updates a calendar event from a sheet event. function updateEvent(calEvent, sheetEvent){ sheetEvent.sendInvites = SEND_EMAIL_INVITES; if (sheetEvent.endtime === '') { calEvent.setAllDayDate(sheetEvent.starttime); } else { calEvent.setTime(sheetEvent.starttime, sheetEvent.endtime); } calEvent.setTitle(sheetEvent.title); calEvent.setDescription(sheetEvent.description); calEvent.setLocation(sheetEvent.location); // Set event color if (sheetEvent.color > 0 && sheetEvent.color < 12) { calEvent.setColor('' + sheetEvent.color); } var guestCal = calEvent.getGuestList().map(function (x) { return { email: x.getEmail(), added: false }; }); var sheetGuests = sheetEvent.guests || ''; var guests = sheetGuests.split(',').map(function (x) { return x ? x.trim() : ''; }); // Check guests that are already invited. for (var gIx = 0; gIx < guestCal.length; gIx++) { var index = guests.indexOf(guestCal[gIx].email); if (index >= 0) { guestCal[gIx].added = true; guests.splice(index, 1); } } guests.forEach(function (x) { if (x) calEvent.addGuest(x); }); guestCal.forEach(function (x) { if (!x.added) { calEvent.removeGuest(x.email); } }); }

// Synchronize from calendar to spreadsheet. function syncFromCalendar() { // Get calendar and events var calendar = CalendarApp.getCalendarById(calendarId); var calEvents = calendar.getEvents(beginDate, endDate);

// Get spreadsheet and data var spreadsheet = SpreadsheetApp.getActiveSpreadsheet(); var sheet = spreadsheet.getActiveSheet(); var range = sheet.getDataRange(); var data = range.getValues(); var eventFound = new Array(data.length);

// Check if spreadsheet is empty and add a title row var titleRow = []; for (var idx = 0; idx < titleRowKeys.length; idx++) { titleRow.push(titleRowMap[titleRowKeys[idx]]); } if (data.length < 1) { data.push(titleRow); range = sheet.getRange(1, 1, data.length, data[0].length); range.setValues(data); setUpSheet(sheet, titleRowKeys); }

if (data.length == 1 && data[0].length == 1 && data[0][0] === '') { data[0] = titleRow; range = sheet.getRange(1, 1, data.length, data[0].length); range.setValues(data); setUpSheet(sheet, titleRowKeys); }

// Map spreadsheet headers to indices var idxMap = createIdxMap(data[0]); var idIdx = idxMap.indexOf('id');

// Verify header has all required fields if (areRequiredFieldsMissing(idxMap)) { var reqFieldNames = requiredFields.map(function(x) {return titleRowMap[x];}).join(', '); errorAlert('Spreadsheet must have ' + reqFieldNames + ' columns'); return; }

// Array of IDs in the spreadsheet var sheetEventIds = data.slice(1).map(function(row) {return row[idIdx];});

// Loop through calendar events for (var cidx = 0; cidx < calEvents.length; cidx++) { var calEvent = calEvents[cidx]; var calEventId = calEvent.getId();

var ridx = sheetEventIds.indexOf(calEventId) + 1;
if (ridx < 1) {
  // Event not found, create it
  ridx = data.length;
  var newRow = [];
  var rowSize = idxMap.length;
  while (rowSize--) newRow.push('');
  data.push(newRow);
} else {
  eventFound[ridx] = true;
}
// Update event in spreadsheet data
calEventToSheet(calEvent, idxMap, data[ridx]);

}

// Remove any data rows not found in the calendar var rowsDeleted = 0; for (var idx = eventFound.length - 1; idx > 0; idx--) { //event doesn't exists and has an event id if (!eventFound[idx] && sheetEventIds[idx - 1]) { data.splice(idx, 1); rowsDeleted++; } }

// Save spreadsheet changes range = sheet.getRange(1, 1, data.length, data[0].length); range.setValues(data); if (rowsDeleted > 0) { sheet.deleteRows(data.length + 1, rowsDeleted); } }

// Synchronize from spreadsheet to calendar. function syncToCalendar() { // Get calendar and events var calendar = CalendarApp.getCalendarById(calendarId); if (!calendar) { errorAlert('Cannot find calendar. Check instructions for set up.'); } var calEvents = calendar.getEvents(beginDate, endDate); var calEventIds = calEvents.map(function(val) {return val.getId();});

// Get spreadsheet and data var spreadsheet = SpreadsheetApp.getActiveSpreadsheet(); var sheet = spreadsheet.getActiveSheet(); var range = sheet.getDataRange(); var data = range.getValues(); if (data.length < 2) { errorAlert('Spreadsheet must have a title row and at least one data row'); return; }

// Map headers to indices var idxMap = createIdxMap(data[0]); var idIdx = idxMap.indexOf('id'); var idRange = range.offset(0, idIdx, data.length, 1); var idData = idRange.getValues()

// Verify header has all required fields if (areRequiredFieldsMissing(idxMap)) { var reqFieldNames = requiredFields.map(function(x) {return titleRowMap[x];}).join(', '); errorAlert('Spreadsheet must have ' + reqFieldNames + ' columns'); return; }

var keysToAdd = missingFields(idxMap);

// Loop through spreadsheet rows var numChanges = 0; var numUpdated = 0; var changesMade = false; for (var ridx = 1; ridx < data.length; ridx++) { var sheetEvent = reformatEvent(data[ridx], idxMap, keysToAdd);

// If enabled, skip rows with blank/invalid start and end times
if (SKIP_BLANK_ROWS && !(sheetEvent.starttime instanceof Date) &&
    !(sheetEvent.endtime instanceof Date)) {
  continue;
}

// Do some error checking first
if (!sheetEvent.title) {
  errorAlert('must have title', sheetEvent, ridx);
  continue;
}
if (!(sheetEvent.starttime instanceof Date)) {
  errorAlert('start time must be a date/time', sheetEvent, ridx);
  continue;
}
if (sheetEvent.endtime !== '') {
  if (!(sheetEvent.endtime instanceof Date)) {
    errorAlert('end time must be empty or a date/time', sheetEvent,

ridx); continue; } if (sheetEvent.endtime < sheetEvent.starttime) { errorAlert('end time must be after start time for event', sheetEvent, ridx); continue; } }

// Ignore events outside of the begin/end range desired.
if (sheetEvent.starttime > endDate) {
  continue;
}
if (sheetEvent.endtime === '') {
  if (sheetEvent.starttime < beginDate) {
    continue;
  }
} else {
  if (sheetEvent.endtime < beginDate) {
    continue;
  }
}

// Determine if spreadsheet event is already in calendar and matches
var addEvent = true;
if (sheetEvent.id) {
  var eventIdx = calEventIds.indexOf(sheetEvent.id);
  if (eventIdx >= 0) {
    calEventIds[eventIdx] = null;  // Prevents removing event below
    addEvent = false;
    var calEvent = calEvents[eventIdx];
    if (!eventMatches(calEvent, sheetEvent)) {
      // Update the event
      updateEvent(calEvent, sheetEvent);

      // Maybe throttle updates.
      numChanges++;
      if (numChanges > THROTTLE_THRESHOLD) {
        Utilities.sleep(THROTTLE_SLEEP_TIME);
      }
    }
  }
}
if (addEvent) {
  var newEvent;
  sheetEvent.sendInvites = SEND_EMAIL_INVITES;
  if (sheetEvent.endtime === '') {
    newEvent = calendar.createAllDayEvent(sheetEvent.title,

sheetEvent.starttime, sheetEvent); } else { newEvent = calendar.createEvent(sheetEvent.title, sheetEvent.starttime, sheetEvent.endtime, sheetEvent); } // Put event ID back into spreadsheet idData[ridx][0] = newEvent.getId(); changesMade = true;

  // Set event color
  if (sheetEvent.color > 0 && sheetEvent.color < 12) {
    newEvent.setColor('' + sheetEvent.color);
  }

  // Maybe throttle updates.
  numChanges++;
  if (numChanges > THROTTLE_THRESHOLD) {
    Utilities.sleep(THROTTLE_SLEEP_TIME);
  }
}

}

// Save spreadsheet changes if (changesMade) { idRange.setValues(idData); }

// Remove any calendar events not found in the spreadsheet var numToRemove = calEventIds.reduce(function(prevVal, curVal) { if (curVal !== null) { prevVal++; } return prevVal; }, 0); if (numToRemove > 0) { var ui = SpreadsheetApp.getUi(); var response = ui.Button.YES; if (numToRemove > numUpdated) { response = ui.alert('Delete ' + numToRemove + ' calendar event(s) not found in spreadsheet?', ui.ButtonSet.YES_NO); } if (response == ui.Button.YES) { calEventIds.forEach(function(id, idx) { if (id != null) { calEvents[idx].deleteEvent(); Utilities.sleep(20); } }); } } Logger.log('Updated %s calendar events', numChanges); }

// Set up a trigger to automatically update the calendar when the spreadsheet is // modified. See the instructions for how to use this. function createSpreadsheetEditTrigger() { var ss = SpreadsheetApp.getActive(); ScriptApp.newTrigger('syncToCalendar') .forSpreadsheet(ss) .onEdit() .create(); }

// Delete the trigger. Use this to stop automatically updating the calendar. function deleteTrigger() { // Loop over all triggers. var allTriggers = ScriptApp.getProjectTriggers(); for (var idx = 0; idx < allTriggers.length; idx++) { if (allTriggers[idx].getHandlerFunction() === 'syncToCalendar') { ScriptApp.deleteTrigger(allTriggers[idx]); } } }

On Mon, Jul 8, 2019 at 9:03 AM Dave Parsons notifications@github.com wrote:

I'll take a look at the script tonight. Maybe I introduced a bug.

— You are receiving this because you commented. Reply to this email directly, view it on GitHub https://github.com/Davepar/gcalendarsync/issues/52?email_source=notifications&email_token=AITAZWNKPO7JSXNFNSABG2TP6NQM3A5CNFSM4GHHC5K2YY3PNVWWK3TUL52HS4DFVREXG43VMVBW63LNMVXHJKTDN5WW2ZLOORPWSZGODZNR3XI#issuecomment-509287901, or mute the thread https://github.com/notifications/unsubscribe-auth/AITAZWOH2HNBB7EPOOOXBDDP6NQM3ANCNFSM4GHHC5KQ .

[sarmstead]

Here are the quotas: https://developers.google.com/apps-script/guides/services/quotas