joelando
commented
4 years ago Entire code:
// Synchronize from spreadsheet to calendar. function syncToCalendar() { console.info('Starting sync to calendar'); var scriptStart = Date.now(); // 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 numAdded = 0; var numUpdates = 0; var eventsAdded = 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];
var convertedCalEvent = convertCalEvent(calEvent);
var eventDiffs = eventDifferences(convertedCalEvent, sheetEvent);
if (eventDiffs > 0) {
// When there are only 1 or 2 event differences, it's quicker to
// update the event. For more event diffs, delete and re-add the event. The one
// exception is if the event has guests (eventDiffs=99). We don't
// want to force guests to re-confirm, so go through the slow update
// process instead.
if (eventDiffs < 3 && eventDiffs !== EVENT_DIFFS_WITH_GUESTS) {
numUpdates += updateEvent(calEvent, convertedCalEvent, sheetEvent);
} else {
addEvent = true;
calEventIds[eventIdx] = sheetEvent.id;
}
}
}
}
console.info('%d updates, time: %d msecs', numUpdates, Date.now() - scriptStart);
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();
eventsAdded = true;
// Set event color
if (sheetEvent.color > 0 && sheetEvent.color < 12) {
newEvent.setColor('' + sheetEvent.color);
}
// Throttle updates.
numAdded++;
Utilities.sleep(THROTTLE_SLEEP_TIME);
if (numAdded % 10 === 0) {
console.info('%d events added, time: %d msecs', numAdded, Date.now() - scriptStart);
}
}
// If the script is getting close to timing out, save the event IDs added so far to avoid lots
// of duplicate events.
if ((Date.now() - scriptStart) > MAX_RUN_TIME) {
idRange.setValues(idData);
}}
// Save spreadsheet changes if (eventsAdded) { 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.alert('Delete ' + numToRemove + ' calendar event(s) not found in spreadsheet?', ui.ButtonSet.YES_NO); if (response == ui.Button.YES) { var numRemoved = 0; calEventIds.forEach(function(id, idx) { if (id != null) { calEvents[idx].deleteEvent(); Utilities.sleep(THROTTLE_SLEEP_TIME); numRemoved++; if (numRemoved % 10 === 0) { console.info('%d events removed, time: %d msecs', numRemoved, Date.now() - scriptStart); } } }); } } }
davepar
Hi there,
First of all thanks for the code - it works great when running with the Google Sheet open.
I tried to setup a trigger for this and one issue I'm running into is that Extra events identified on the calendar require confirmation for delete through a button which makes my Triggered run of the script fail.
I'm completely new to coding, but I've read the whole script and it's something to do with this part. Can it be removed to 'ask' and simply do it?
// 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.alert('Delete ' + numToRemove + ' calendar event(s) not found in spreadsheet?', ui.ButtonSet.YES_NO); if (response == ui.Button.YES) { var numRemoved = 0; calEventIds.forEach(function(id, idx) { if (id != null) { calEvents[idx].deleteEvent(); Utilities.sleep(THROTTLE_SLEEP_TIME); numRemoved++; if (numRemoved % 10 === 0) { console.info('%d events removed, time: %d msecs', numRemoved, Date.now() - scriptStart); } } }); } } }