I have reviewed other answers to seemingly similar questions and haven't been able to find a solution yet. I am currently using the below script (thanks to a lot of help!) to auto-populate events from multiple sheets in a workbook; however, what I am also hoping to achieve:
- the events auto update in the calendar if pre-existing data in C4:F24 is changed (without a duplicate event being created)
- the event deletes if data in cells E4:F24 is deleted.
I already have an "onEdit" trigger enabled; however, it does not allow for any of the above. Any help is incredibly appreciated! I have updated the script to reflect what I am currently testing. This script returns the following error when the installed "OnEdit" trigger runs: ReferenceError: e is not defined at simpleSheetsToCalendar(Code:4:17)
function simpleSheetsToCalendar() {
var sheetNames = ["Oct-08-2021 | Plan", "Oct-15-2021 | Plan"]; // Please set the sheet names you want to use.
var calendarId = "myCalendar"; // Please set your calendar ID.
var {range} = e;
var sheet = range.getSheet();
if (!(sheetNames.includes(sheet.getSheetName()) && range.rowStart >= 4 && range.rowEnd <= 24 && range.columnStart >= 3 && range.columnStart <= 6)) return;
// 1. Retrieve all events from the Calendar and create an object for checking the duplicated titles.
var events = [];
var pageToken = "";
do {
var res = Calendar.Events.list(calendarId, {maxResults: 2500, fields: "nextPageToken,items(id,summary,start(dateTime),end(dateTime))", pageToken});
events = events.concat(res.items);
pageToken = res.nextPageToken;
} while(pageToken);
var obj = events.reduce((o, e) => Object.assign(o, {[e.summary]: e}), {});
// 2. Retrieve sheets from a Google Spreadsheet and retrieve the values from "C4:F24", and create an object for creating new events, deleting events and updating events.
var values = sheet.getRange("C4:F24").getValues().reduce((o, [title,,startTime,endTime]) => {
if (!obj[title] && title && startTime && endTime && !o[title]) {
o.add[title] = {startTime, endTime};
} else if (obj[title] && title && startTime && endTime) {
if (new Date(obj[title].start.dateTime).getTime() != startTime.getTime() || new Date(obj[title].end.dateTime).getTime() != endTime.getTime()) {
obj[title].start.dateTime = startTime;
obj[title].end.dateTime = endTime;
o.modify.push(obj[title]);
}
} else if (obj[title] && title && !startTime && !endTime) {
o.remove.push(obj[title].id);
}
return o;
}, {add: {}, remove: [], modify: []});
// 3. Create new events.
var eventCal = CalendarApp.getCalendarById(calendarId);
var ar = Object.entries(values.add);
if (ar.length > 0) {
ar.forEach(([title, {startTime, endTime}]) => eventCal.createEvent(title, startTime, endTime));
}
// 4. Modify events.
if (values.modify.length > 0) {
values.modify.forEach(({id, start, end}) => eventCal.getEventById(id).setTime(start.dateTime, end.dateTime));
}
// 5. Delete events.
if (values.remove.length > 0) {
values.remove.forEach(id => eventCal.getEventById(id).deleteEvent());
}
}CodePudding user response：
From your following question and your replying,
- the events auto update in the calendar if pre-existing data in C4:F24 is changed (without a duplicate event being created)
- the event deletes if data in cells E4:F24 is deleted.
In your situation, I understood as follows.
- You want to automatically execute the script when the cells "C4:C24" of the specification sheets are edited.
- When the columns "E" and "F" are changed while the column "C" has the value, you want to change the start and endtime of the event.
- When the cells "E4:F24" are deleted while the cells "C4:C24" are not deleted, you want to delete the events from the calendar.
In this case, how about the following flow?
- Retrieve all events from the Calendar and create an object for checking the duplicated titles.
- Retrieve sheets from a Google Spreadsheet and retrieve the values from "C4:F24", and create an object for creating new events, deleting events and updating events.
- When new title is found, create new events.
- When the title is existing and the start and end times are changed, modify events.
- When the title is existing and the start and end times are deleted, Delete events.
And, in order to execute the script, in this case, the installable OnEdit trigger is used. Because Calendar API is used. When this flow is reflected in your script, it becomes as follows.
Modified script:
In this sample script, in order to retrieve all titles of the calendar event, Calendar API is used. So before you use this script, please enable Calendar API at Advanced Google services. And, please set sheetNames and calendarId.
And also, please install the installable OnEdit trigger to the function simpleSheetsToCalendar.
function simpleSheetsToCalendar(e) {
var sheetNames = ["Sheet1", "Sheet2",,,]; // Please set the sheet names you want to use.
var calendarId = "calendarId"; // Please set your calendar ID.
var {range} = e;
var sheet = range.getSheet();
if (!(sheetNames.includes(sheet.getSheetName()) && range.rowStart >= 4 && range.rowEnd <= 24 && range.columnStart >= 3 && range.columnStart <= 6)) return;
// 1. Retrieve all events from the Calendar and create an object for checking the duplicated titles.
var events = [];
var pageToken = "";
do {
var res = Calendar.Events.list(calendarId, {maxResults: 2500, fields: "nextPageToken,items(id,summary,start(dateTime),end(dateTime))", pageToken});
events = events.concat(res.items);
pageToken = res.nextPageToken;
} while(pageToken);
var obj = events.reduce((o, e) => Object.assign(o, {[e.summary]: e}), {});
// 2. Retrieve sheets from a Google Spreadsheet and retrieve the values from "C4:F24", and create an object for creating new events, deleting events and updating events.
var values = sheet.getRange("C4:F24").getValues().reduce((o, [title,,startTime,endTime]) => {
if (!obj[title] && title && startTime && endTime && !o[title]) {
o.add[title] = {startTime, endTime};
} else if (obj[title] && title && startTime && endTime) {
if (new Date(obj[title].start.dateTime).getTime() != startTime.getTime() || new Date(obj[title].end.dateTime).getTime() != endTime.getTime()) {
obj[title].start.dateTime = startTime;
obj[title].end.dateTime = endTime;
o.modify.push(obj[title]);
}
} else if (obj[title] && title && !startTime && !endTime) {
o.remove.push(obj[title].id);
}
return o;
}, {add: {}, remove: [], modify: []});
// 3. Create new events.
var eventCal = CalendarApp.getCalendarById(calendarId);
var ar = Object.entries(values.add);
if (ar.length > 0) {
ar.forEach(([title, {startTime, endTime}]) => eventCal.createEvent(title, startTime, endTime));
}
// 4. Modify events.
if (values.modify.length > 0) {
values.modify.forEach(({id, start, end}) => eventCal.getEventById(id).setTime(start.dateTime, end.dateTime));
}
// 5. Delete events.
if (values.remove.length > 0) {
values.remove.forEach(id => eventCal.getEventById(id).deleteEvent());
}
}
- When this script is run, the events for updating and deleting are retrieved from all event lists from Calendar and the values from Spreadsheet. And, when the values of columns "E" and "F" are changed, the event with the title of column "C" is updated. When the values of columns "E" and "F" are empty, the event with the title of column "C" is deleted.
Note:
In this answer, from your question, it supposes that when the values of columns "E" and "F" are removed, the values of the title of column "C" are existing. Please be careful about this.
In this modified script, the script is automatically executed when the cells "C4:F24" of the specification sheets. So when you directly run the function, an error like
Cannot destructure property 'range' of 'e' as it is undefined.occurs. When you run the script, please edit the cells "C4:F24" of the specific sheets ofsheetNames. Please be careful this.Please confirm your sheet names. Please be careful this.