I am attempting to write events from Google Sheets to Google Calendar. However, I want to modify my code to write the Event ID back to the Google Sheet. This code successfully writes the events. However, I am not sure how to modify it to write the event ID for the created event back to the spreadsheet. Any help is appreciated!
function createCalendarEvent() {
/** Create Marketing Events from Google Spreadsheet **/
var spreadsheet = SpreadsheetApp.getActiveSheet();
var calendarId = spreadsheet.getRange("F1").getValue();
var eventCal = CalendarApp.getCalendarById(calendarId);
/** I have grabbed the active sheet, specified the cell in which to find the calendar Id, and lastly, told the CalendarApp which calendar to write events to. **/
var marketingEntries = spreadsheet.getRange("A4:F100").getValues();
/** Now we need to pull in the fields of data that we want to turn into events. **/
for (x=0; x<marketingEntries.length; x++) {
var markEntry = marketingEntries[x];
var title = markEntry[0]
var date = markEntry[1]
var descrip = markEntry[2]
var gues = markEntry[3]
/** var sendInvites = markEntry[] **/
eventCal.createAllDayEvent(title , new Date(date) , {description: descrip, guests: gues});
}
}
I have also included a screenshot of the sheet I working to build this out of. enter image description here
I have been looking through other stack overflow posts (like this one: How to save event id from google calendar to google sheets without overwriting formulas) and thorugh the Google Developer pages but I am not sure how to write the ID of the event as it is being created or right after it is created, to the spreadsheet in the EventId column.
I found this code works to clear my sheet from A5:J and then gets the calendar events and writes them back to my sheet starting in row 9.
//Pull Calendar Events by Calendar ID and Sets the Sheet Name where you want the events to populate.
function getCalendarEvents() {
var calendarId = 'CALENDAR ID'; // Replace with the ID of the Google Calendar you want to pull events from
var sheetName = 'SHEET NAME'; // Replace with the name of the sheet where you want to output the events
var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(sheetName);
var rangeStart = sheet.getRange('C3').getValues();
var rangeEnd = sheet.getRange('F3').getValues();
var start = new Date(rangeStart);
var end = new Date(rangeEnd);
var events = Calendar.Events.list(calendarId, {timeMin: start.toISOString(), timeMax: end.toISOString(), singleEvents: true, orderBy: 'startTime'}).items;
sheet.getRange('A5:J').clearContent(); // Clear existing data in the sheet from row 5 downward, columns A through J
var data = [];
for (var i = 0; i < events.length; i++) {
var event = events[i];
var title = event.summary || '';
var start = event.start.dateTime || event.start.date || '';
if (start) {
start = new Date(start).toISOString().slice(0,10);
}
var end = event.end.dateTime || event.end.date || '';
if (end) {
end = new Date(end).toISOString().slice(0,10);
}
var description = event.description || '';
var guests = event.attendees ? event.attendees.map(function(attendee) { return attendee.email; }).join(', ') : '';
var color = event.colorId || '';
var id = event.id || '';
data.push([id, title, start, end, description, guests, color ]);
}
if (data.length > 0) {
sheet.getRange(9, 1, data.length, data[0].length).setValues(data); // Pushes the calendar data to the sheet starting in row 9
}
}Then, I created some rows where I could specify whether or not I wanted to create a new calendar event, update an event, or delete an event by checking to see if the word or value of "TRUE" was in the right field. This is what I am calling Calendar Sync:
function syncWithCalendar() {
var calendarId = 'CALENDAR ID';
var sheetName = 'SHEET NAME'; // Replace with the name of the sheet where you want to output the events
var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(sheetName);
var data = sheet.getRange('A5:J').getValues(); // retrieve data starting from row 5
var calendar = CalendarApp.getCalendarById(calendarId);
for (var i = 0; i < data.length; i++) {
var event = {
'id': data[i][0],
'title': data[i][1],
'start': new Date(data[i][2]),
'starttwo': data[i][2],
'end': new Date(data[i][3]),
'endtwo': data[i][3],
'description': data[i][4],
'guests': data[i][5].split(",").map(function(email) { return email.trim(); }), // split the string into an array of email addresses
'color': data[i][6],
};
var desc = data[i][4];
var gues = data[i][5];
var col = data[i][6];
if (data[i][7]) { // if update event is true
var calendarEvent = calendar.getEventById(event.id);
calendarEvent.setTitle(event.title);
calendarEvent.setTime(event.start, event.end);
calendarEvent.setDescription(event.description);
calendarEvent.setColor(event.color);
// Remove guests not present in the sheet. Not sure this is working???
var existingGuests = calendarEvent.getGuestList();
for (var j = 0; j < existingGuests.length; j++) {
var email = existingGuests[j].getEmail();
if (event.guests.indexOf(email) === -1) {
calendarEvent.removeGuest(email);
}
}
// Add guests from the sheet not already present. This definitely works.
for (var k = 0; k < event.guests.length; k++) {
if (existingGuests.map(function(guest) { return guest.getEmail(); }).indexOf(event.guests[k]) === -1) {
calendarEvent.addGuest(event.guests[k]);
}
}
}
if (data[i][8]) { // if new event is true
console.log('Creating new event:');
console.log('Title: ' + event.title);
console.log('Start: ' + event.start);
console.log('End: ' + event.end);
console.log('Description: ' + event.description);
console.log('Guests: ' + event.guests);
console.log('Color: ' + event.color);
var newEvent = calendar.createEvent(event.title, new Date(event.starttwo), new Date(event.endtwo),{
description: desc,
guests: gues,
color: col,
});
sheet.getRange(i+5, 1).setValue(newEvent.getId()); // write new event ID back to column A
// Now update the new event
var updatedEvent = {
'id': newEvent.getId(),
'title': event.title,
'start': new Date(event.starttwo),
'starttwo': event.starttwo,
'end': new Date(event.endtwo),
'endtwo': event.endtwo,
'description': event.description,
'guests': event.guests,
'color': event.color,
};
var desc = event.description;
var gues = event.guests;
var col = event.color;
var calendarEvent = calendar.getEventById(updatedEvent.id);
calendarEvent.setTitle(updatedEvent.title);
calendarEvent.setTime(updatedEvent.start, updatedEvent.end);
calendarEvent.setDescription(updatedEvent.description);
calendarEvent.setColor(updatedEvent.color);
}
if (data[i][9]) { // if delete event is true
var calendarEvent = calendar.getEventById(event.id);
calendarEvent.deleteEvent();
sheet.getRange(i+5, 1).setValue(''); // clear the event ID in column A
}
}
sheet.getRange('H5:J').clearContent(); // Clear boolean value change operaters
}The worksheet that goes with this code is here: https://docs.google.com/spreadsheets/d/1Z9mwwNtmpDcyWmxgkakB1YTiaplbBxYeLQjYC7ds0Fo/edit?usp=sharing