Google Calendar event will not delete with Apps Script deleteEvent() method

Question

I've created a script which loops through each google sheet row and creates a calendar event if the background on a cell in column 5 is = dark yellow 1.

function CheckDocument() {
 var ss = SpreadsheetApp.getActiveSpreadsheet()
 var sheet1=ss.getSheetByName('Sheet1');
 var tasksApproaching = ""; // list of items of approaching overdue tasks
 var tasksOverdue = ""; // list of items that are overdue
 var tasksApproachingArr = []; // array of approaching overdue tasks
 var tasksOverdueArr = []; // array of overdue tasks
 var timeRemaining = []; // array of time values (today - refreshdate) example (3/5/21 - 3/2/21 = 3 days)
 var calendarID = sheet2.getRange(6,2).getValue(); //obtain calendarID
 var eventCal = CalendarApp.getCalendarById(calendarID); //set object of calendarID

for (var i=3;i<n+1;i++){ //loop through sheet, add tasks to arrays if it meets conditional formatting color criteria
        var taskRefreshDate = sheet1.getRange(i,5).getValue();
        var background = sheet1.getRange(i,5).getBackground();
        if (background == "#f1c232"){ //dark yellow 1 background color
            calendarTask = sheet1.getRange(i,1).getValue(); //set title for calendar event creation
            tasksApproachingArr.push(sheet1.getRange(i,1).getValue()); //send task name to array
            timeRemainingMils = taskRefreshDate.getTime() - today; //Milliseconds of time between refresh date and today
            timeRemaining.push(Math.floor(timeRemainingMils/(86400 * 1000))+1); //Convert milliseconds to # days
            if(sheet1.getRange(i,9).getValue()==""){
               eventCal.createAllDayEvent(calendarTask, taskRefreshDate, {
                 description: calendarTask
               });
               sheet1.getRange(i,9).setValue("Yes");
            }
            } else if(background == "#e06666") { //light red 1 background
              tasksOverdueArr.push(sheet1.getRange(i,1).getValue());
            } else if(sheet1.getRange(i,9).getValue()=="Yes"){
              sheet1.getRange(i,9).setValue("");
            }
        }
}

My objective is to remove those events from my calendar if the background is updated to dark green 1.

var startTime = new Date(2021,1,1,0,0,0);
  var endTime = new Date(2021,12,31,0,0,0);
  var events = eventCal.getEvents(startTime,endTime);
  for (var e=0; e<events.length;e++){
        eventStartTime = events[e].getStartTime();
        eventTitle = events[e].getTitle();
        for (var i=3;i<n+1;i++){
          if((eventStartTime == sheet1.getRange(i,5).getValue()) && (eventTitle == sheet1.getRange(i,1).getValue()) && (sheet1.getRange(i,5).getBackground()=="#6aa84f")){
              events[e].deleteEvent();
          }   
        }
    }

I can successfully create the calendar events, however, when I update the spreadsheet by changing one of the cells in column 5 from dark yellow to dark green, the event still remains on my calendar. Any idea as to what I'm missing? Thanks.

You can debug this by putting console.log statements after getting the events in the calendar, displaying the start time of the events, titles, and even the background color of the cell, so you can check whether there is an incorrect value passed into the if statement. Or if the events are being fetched at all. — CMB, Apr 06 '21 at 16:26
I've edited my submission to include the function trigger. If you're asking about the specific calendar trigger, that's where I'm a bit unclear. I changed the last line of code in the second cluster of code from (events[e].deleteEvent();) to eventCal.deleteEvent(events[e]), however, that change did not result in the removal of the calendar event on my calendar. — pghInitechBranch, Apr 06 '21 at 16:28
Hi Carlos, that's exactly what I did and though they yielded the exact same values, the delete method did not execute. For example, they logged: "abc", " Wed Apr 07 2021 00:00:00 GMT-0400 (Eastern Daylight Time)", "#6aa84f" and the expected values were "abc", " Wed Apr 07 2021 00:00:00 GMT-0400 (Eastern Daylight Time)", "#6aa84f". — pghInitechBranch, Apr 06 '21 at 16:29

score 1 · Accepted Answer · answered Apr 06 '21 at 16:47

1

Solution:

Some good ways of comparing equality of Date objects are either of these methods:

var d1 = new Date(2013, 0, 1);
var d2 = new Date(2013, 0, 1);
/*
 * note: d1 == d2 returns false as described above
 */
d1.getTime() == d2.getTime(); // true
d1.valueOf() == d2.valueOf(); // true
Number(d1)   == Number(d2);   // true
+d1          == +d2;          // true

So in your code the comparison could be:

eventStartTime.getTime() == sheet1.getRange(i,5).getValue().getTime()

or any other of the methods above.

Reference:

Compare Dates in JavaScript

answered Apr 06 '21 at 16:47

CMB

4,950
1
4
16

Hi Carlos, I added the .getTime() method to both values and even though they align, the deleteEvent() method is still not executing. – pghInitechBranch Apr 06 '21 at 17:22
Have you tried logging the result of the comparisons like ````eventStartTime.getTime() == sheet1.getRange(i,5).getValue().getTime()````? Is anyone of the conditions returning ````false````? How about the other methods of comparing dates? Do they also return ````false````? – CMB Apr 06 '21 at 17:27
After logging each comparison, the results fluctuated between TRUE and FALSE. My assumption is that because those results contained both TRUE and FALSE outcomes, the IF statement is executing correctly. If every result was FALSE then one of the comparisons is causing it to fail. Does that sound right to you? – pghInitechBranch Apr 06 '21 at 17:54
Yes, and which of the conditions is returning FALSE? Is it the date comparison, or the other two strings? What values caused it to be evaluated as false? Please edit your question with the results from your logs. – CMB Apr 06 '21 at 17:57
I condensed the testing variables and determined there is a misalignment in the time values. console.log(events[0].getStartTime().getTime()); returns 1617681600000. console.log(sheet1.getRange(5,5).getValue().getTime()); returns 1620273600000. – pghInitechBranch Apr 06 '21 at 18:15
I'm an idiot! I figured out why it wasn't executing. When I created the events, the startTime of the event was a date, for example Jan 1, 2021 with a yellow background. In order for the background to change to green, the date would need to change to meet the conditional formatting custom formula. For example, the new date would be Jan 10. 2021 with a green background. That's why the delete method failed to fire because the original date never matched the new date. Thanks for all of you help. – pghInitechBranch Apr 06 '21 at 18:31

Google Calendar event will not delete with Apps Script deleteEvent() method

1 Answers1

Solution:

Reference: