I have a sheet with over 400 calendar events and I am trying to write a script that will take these events from my spreadsheet and create events for them in my Google Calendar. To prevent the script from duplicating events every time it runs, I am setting the eventID as a check in the last column of my spreadsheet. The logic is that if the startTime column and endTime columns are not blank and the EventID column IS blank, then the script should run and create a new event, then log the ID into the last column.
I am finding that the code logs an eventID in the last column all the time, which means it creates an event every time, even when the conditions are not met.
function createCalendarEvent() {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getSheetByName('MasterList');
var data = sheet.getSheetValues(3, 1, sheet.getLastRow(), 12);
var calendar = CalendarApp.getCalendarById('*CalendarID*');
for (var i = 0; i <= sheet.getLastRow(); i++) {
var title = data[i][0]; //String
var date = new Date(data[i][1]); //Date
var startTime = new Date(data[i][9]); //DateTime
var endTime = new Date(data[i][10]); //DateTime
var location = data[i][5]; //String
var status = data[i][11]; //String
var allDay = data[i][3]; //String
if (allDay === 'ALL DAY' && status === ''){
var allDayEvent = calendar.createAllDayEvent(title, date, {location: location});
sheet.getRange(i + 3, 12).setValue(allDayEvent.getId());
}
else if (startTime !== '' && endTime !== '' && status === '' ) {
var event = calendar.createEvent(title, startTime, endTime, {location: location});
sheet.getRange(i + 3, 12).setValue(event.getId());
}
else {
Logger.log('\nRow %s is missing start and/or end time or the event has already been created.\n', i + 3);
}
}
}
Based on suggestions in the comments, I determined that the "empty" cells were being treated at the "zero" date of Google Sheets, which is 12/30/1899 0:00:00. So while I could not see a value in the cell, the code was still grabbing that value. Once I set the condition that the endTime had to be greater than the startTime, it worked out (and flushed out a few errors in the spreadsheet where one of the users indeed entered end times that were before the start time).
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments