first time asking a question here.
I've built a Google App Script that takes event info from Google Sheets and creates events on Google Calendar, based on @Mogsdad's answer here. (I couldn't post a question on that thread because of my nonexistent reputation)
The "exportEvents" function worked beautifully the first time. But once the spreadsheet had more rows of information, it didn't finish the script and instead said "You have been creating or deleting too many calendars or calendar events in a short time. Please try again later."
This is not due to the daily quota that Google set because I'm not creating anything close to 10,000 events a day. I searched for a solution, and found this on Google Product Forum by @JEvans-GSA, who is basing his script on @Mogsdad's script like me.
I adapted his solution and separated the "updateEvents" function from "exportEvents" (and also created "deleteEvents" function). Now the "exportEvents" do not run into the "You have been creating or deleting too many calendars or calendar events in a short time. Please try again later" problem. But the "updateEvents" still get the same warning, half way through execution!
- For example, I created 83 events titled "1" to "83" and exported the events to calendar successfully.
- Then I changed the event titles to "100" to "182" and tried to update the events.
- It only updated "1"~"15" to "101"~"114", and everything else remained the same. - I made the function update the "status" column as "Updated (today's date)", which also didn't execute.
So the function started but didn't finish, which makes me think that the error must be somewhere in setting the recurrence. Is there a workaround to make "updateEvents" work, or as @Mogsdad suggested in the original answer, is the only other way to delete and recreate events?
Thank you in advance for any help. Here are links to my spreadsheet (I'm not allowed to use more than 2 links, due to my low rep): - https://docs.google.com/spreadsheets/d/1V6eioCo4QDPO2DdCyW93sLRWT_xtzKM-drDPVI1Gd8s/edit?usp=sharing
And my current script:
//Global settings
var calId = 'n02m72vanqabnt1gcjluk2vosk@group.calendar.google.com';
var moderatorEmail = 'EMAIL_ADDRESS';
var idId = 0;
var startTimeId = 1;
var endTimeId = 2;
var titleId = 3;
var organizerId = 4;
var locId = 5;
var descId = 6;
var urlId = 7;
var topicId = 8;
var typeId = 9;
var contactId = 10;
var contactEmailId = 11;
var actionId = 12;
var statusId = 13;
//Add a custom menu
function onOpen() {
var ss = SpreadsheetApp.getActive();
var items = [
{name: 'Export Events', functionName: 'exportEvents'},
{name: 'Update Events', functionName: 'updateEvents'},
{name: 'Delete Events', functionName: 'deleteEvents'}
];
ss.addMenu('Calendar', items);
}
//Actual functions
function exportEvents() {
var spreadsheet = SpreadsheetApp.getActiveSheet();
var headerRows = 2; //Number of rows of header info to skip
var range = spreadsheet.getDataRange();
var data = range.getValues();
var cal = CalendarApp.getCalendarById(calId);
for (i in data) {
if (i < headerRows) continue; //Skip header rows
var row = data[i];
var title = row[titleId];
var tstart = new Date(row[startTimeId]);
tstart.setDate(tstart.getDate());
tstart.setMonth(tstart.getMonth());
tstart.setYear(tstart.getYear());
tstart.setTime(tstart.getTime());
var tstop = new Date(row[endTimeId]);
tstop.setDate(tstop.getDate());
tstop.setMonth(tstop.getMonth());
tstop.setYear(tstop.getYear());
tstop.setTime(tstart.getTime());
var loc = row[locId];
var contact = row[contactId];
var organizer = row[organizerId];
var topic = row[topicId];
var type = row[typeId];
var contactEmail = row[contactEmailId];
var url = row[urlId];
var status = row[statusId];
var desc = (row[descId]
+"\n\n-Organizer: "+organizer
+"\n-Event URL: "+url
+"\n-Event Type: "+type
+"\n-Event Contact: "+contact+" ("+contactEmail+")")
var id = row[idId];
// Check if the event already exists, update it if it does
try {
var event = cal.getEventSeriesById(id);
}
catch (e) {
// Do nothing - we just want to avoid the exception when event doesn't exist
}
if (!event) {
//cal.createEvent(title, new Date("Month D, YYYY 00:00:00"), new Date("Month D, YYYY 00:00:00), {description:desc,location:loc});
var newEvent = cal.createEvent(title, tstart, tstop, {description:desc,location:loc}).getId();
row[idId] = newEvent; // Update the data array with event ID
var d = new Date();
var time = d.getMonth()+1 + "/" + d.getDate() + "/" + d.getFullYear()
row[statusId] = "Exported " + time
}
debugger;
}
range.setValues(data);
}
function updateEvents() {
var sheet = SpreadsheetApp.getActiveSheet();
var headerRows = 2; //Number of rows to skip
var range = sheet.getDataRange();
var data = range.getValues();
var cal = CalendarApp.getCalendarById(calId);
for (i in data) {
if (i < headerRows) continue;
var row = data[i];
var title = row[titleId]; //Second column
var tstart = new Date(row[startTimeId]);
tstart.setDate(tstart.getDate());
tstart.setMonth(tstart.getMonth());
tstart.setYear(tstart.getYear());
tstart.setTime(tstart.getTime());
var tstop = new Date(row[endTimeId]);
tstop.setDate(tstop.getDate());
tstop.setMonth(tstop.getMonth());
tstop.setYear(tstop.getYear());
tstop.setTime(tstart.getTime());
var loc = row[locId];
var contact = row[contactId];
var organizer = row[organizerId];
var topic = row[topicId];
var type = row[typeId];
var contactEmail = row[contactEmailId];
var url = row[urlId];
var action = row[actionId];
var status = row[statusId];
var desc = (row[descId]
+"\n\n-Organizer: "+organizer
+"\n-Event URL: "+url
+"\n-Type: "+type
+"\n-Event Contact: "+contact+" ("+contactEmail+")")
var id = row[idId];
try {
var event = cal.getEventSeriesById(id);
}
catch (e) {
// do nothing
}
if (event) {
if (action === "Update") {
event.setTitle(title);
event.setDescription(desc);
event.setLocation(loc);
// eventSetTime(tstart, tstop); // cannot setTime on eventSeries.
// ... but we Can set recurrence!
var recurrence = CalendarApp.newRecurrence().addDailyRule().times(1);
event.setRecurrence(recurrence, tstart, tstop);
var d = new Date();
var time = d.getMonth()+1 + "/" + d.getDate() + "/" + d.getFullYear()
row[statusId] = "Updated " + time
}
}
}
debugger;
range.setValues(data);
}
function deleteEvents() {
var sheet = SpreadsheetApp.getActiveSheet();
var headerRows = 2; //Number of rows to skip
var range = sheet.getDataRange();
var data = range.getValues();
var cal = CalendarApp.getCalendarById(calId);
for (i in data) {
if (i < headerRows) continue;
var row = data[i];
var action = row[actionId];
var status = row[statusId];
var id = row[idId];
try {
var event = cal.getEventSeriesById(id);
}
catch (e) {
}
if (event) {
if (action === "Delete") {
event.deleteEventSeries();
var d = new Date();
var time = d.getMonth()+1 + "/" + d.getDate() + "/" + d.getFullYear()
row[idId] = "Deleted";
row[statusId] = "Deleted " + time;
}
}
}
debugger;
range.setValues(data);
}
