Based off of Fernando Lara's answer here Google App Script - Add or Update Calendar Event I am trying to create new events on Google Calendar. Here is my script:
function updateCal(){
var cal = CalendarApp.getCalendarById("CalendarID");
var data = SpreadsheetApp.getActive().getSheetByName("Sheet1");
const rows = data.getDataRange().getValues();
rows.forEach(function(row, index){
if (index === 0) return;
var eventdate = data.getRange(index, 4, 1, 1).getValue();
var eventtitle = data.getRange(index,1,1,1).getValue();
var eventdescription = data.getRange(index,2,1,1).getValue();
console.log(eventdate + eventtitle + eventdescription)
const eventdate1 = new Date(eventdate)
const events = cal.getEventsForDay(eventdate1);
if (events.length == 0){ //The error was in this line
cal.createAllDayEvent(eventtitle,eventdate1,{description: eventdescription});
} else {
ev = events[0];
ev.setDescription(eventdescription)
}
})
}
The above script is successful at adding a new event or modifying an existing event if the description changes. There is one major flaw, however. The script does not allow for the creation of another all-day event on a day that already has an existing event. I tried deleting the original event from the spreadsheet manually and adding a new event for the same day with a different title and different description. The title remained the same on Google Calendar, but the description changed.
I believe this line below is incorrect, but I am not sure how to fix it:
if (events.length == 0)
Can anyone point me in the right direction?
Order of Process:
getValue()
is used in a loop. In this case, the process cost will become high.When these points are reflected in your script, how about the following modification?
function updateCal() {
var cal = CalendarApp.getCalendarById("CalendarID");
var data = SpreadsheetApp.getActive().getSheetByName("Sheet1");
const [, ...rows] = data.getDataRange().getValues();
rows.forEach(([eventtitle, eventdescription, , eventdate]) => {
console.log(eventdate + eventtitle + eventdescription)
const eventdate1 = new Date(eventdate)
const events = cal.getEventsForDay(eventdate1);
if (events.length == 0 || (events.length > 0 && !events.some(e => e.getTitle() == eventtitle))) {
cal.createAllDayEvent(eventtitle, eventdate1, { description: eventdescription });
} else {
ev = events[0];
ev.setDescription(eventdescription)
}
});
}
By this modification, cal.createAllDayEvent
is run by checking both the event date and the event title.
In your script, I guessed that you might have wanted to skip the header row. But, if you don't want to skip it, please modify const [, ...rows] = data.getDataRange().getValues();
to const rows = data.getDataRange().getValues();
.
If you want to check the description, please modify if (events.length == 0 || (events.length > 0 && !events.some(e => e.getTitle() == eventtitle))) {
to if (events.length == 0 || (events.length > 0 && !events.some(e => e.getTitle() == eventtitle) && !events.some(e => e.getDescription() == eventdescription))) {
.
From your following reply,
One big problem, however. When I change the description of an event in the spreadsheet, the description does not change in the calendar as it did before.
How about the following modified script?
function updateCal() {
var cal = CalendarApp.getCalendarById("CalendarID");
var data = SpreadsheetApp.getActive().getSheetByName("Sheet1");
const [, ...rows] = data.getDataRange().getValues();
rows.forEach(([eventtitle, eventdescription, , eventdate]) => { // Now [eventtitle, eventdescription, eventdate] ?
console.log(eventdate + eventtitle + eventdescription)
const eventdate1 = new Date(eventdate)
const events = cal.getEventsForDay(eventdate1);
if (events.length == 0 || (events.length > 0 && !events.some(e => e.getTitle() == eventtitle) && !events.some(e => e.getDescription() == eventdescription))) {
cal.createAllDayEvent(eventtitle, eventdate1, { description: eventdescription });
} else {
events.forEach(e => {
if (e.getTitle() == eventtitle && e.getDescription() != eventdescription) {
e.setDescription(eventdescription);
}
});
}
});
}
rows.forEach(([eventtitle, eventdescription, eventdate]) => {
, please modify rows.forEach(([eventtitle, eventdescription, , eventdate]) => {
to it.