Sheets script to first delete events, then add events to calendar

[Cory Davenport]

I am very new to coding, and have been utilizing this community from previous posts and trying to use that in my script. However, I rapidly get in over my head.  I am trying to utilize google sheets for entering in scheduled employees and then taking that data and automatically enter into google calendar. The snag I am in now is my loop does not delete the last cell, but will create an event in the last cell. I disabled the add events with // while I work on the delete events.  Also, if possible, would love if the script would detect when new cells are entered so I wouldn't have to manually change the script to include new dates.

function delete_events()

{

// Find Spreadsheet

  var spreadsheet = SpreadsheetApp.getActiveSheet();

 // Find Calendar ID

 var calendarID = spreadsheet.getRange("D1").getValue();

 // Find Calendar from ID

 var calendarName = CalendarApp.getCalendarById(calendarID);

 // Get active dates

 var getDateStart = spreadsheet.getRange('A2').getValue();

 var getDateEnd = spreadsheet.getRange('A32').getValue();

 var from = new Date(getDateStart);

 var todate = new Date(getDateEnd);

 // Get Calendar dates from spreadsheet

  var events = calendarName.getEvents(from, todate);

 // Delete from dates listed above

   for (x=0; x<events.length;x++){

       var removeEvent = events[x];

//             if(removeEvent.getDescription().startwith("PA"){

                 removeEvent.deleteEvent();

                 Logger.log(removeEvent);

//             }

        }

       }

// //Add Events to Calendar

// //Get active sheet

//    var spreadsheet = SpreadsheetApp.getActiveSheet();

// //Get Calendar ID

//    var calendarID = spreadsheet.getRange("D1").getValue();

//    var eventCal = CalendarApp.getCalendarById(calendarID);

// //Start loop for data

//    var signups = spreadsheet.getRange("A2:B31").getValues();

//           for (x=0; x<signups.length;x++)

//           {

//             var shift = signups[x];

//             var startTime = shift[0];

//             var volunteer = shift[1];

// //Get the id save it to Calendar

//             var calendarEvent  = eventCal.createAllDayEvent(volunteer, startTime);

//             shift[4] = calendarEvent.getId();

//             //load the event

//            

//        

//           }

//}

[Alan Wells]

I don't know what you mean by deleting the last cell.  The code deletes calendar events.  I think of a cell as being in a spreadsheet.

You can use:

/*
Multiple
Lines
commented out
*/

To comment out multiple lines of code.

You are using "x" as the counter in the "for" loop.  Either use the "var" key word in the parenthesis:

for (var x=0; x<signups.length;x++)

Or define "x" somewhere higher up.

var x;//defined by not assigned a value - initial value will be undefined

for (x=0; x<signups.length;x++)

If you don't use the "var" keyword then the variable is defined in the "global" scope, which could cause a problem at some point.

A manual edit can be detected, and code run (triggered) from the "On Edit" event.  The On Edit triggers don't detect a change made from code.

[Cory Davenport]

Thank you for the response. The "cell" is from google sheets which is where I am attempting to delete events. Currently the script runs through the the dates entered in A2-A32 (4/1 to 5/1) of the spreadsheet and then deletes any events in my google calendar correlating to those dates. However, when I run the script it won't delete the events from 5/1 (cell A32) of spreadsheet. This is true even if I change the range of the spreadsheet cells, it doesn't delete whatever I have as the "getDateEnd".

       
function delete_events()
{
// Find Spreadsheet
  var spreadsheet = SpreadsheetApp.getActiveSheet();
  // Find Calendar ID
  var calendarID = spreadsheet.getRange("D1").getValue();
  // Find Calendar from ID
  var calendarName = CalendarApp.getCalendarById(calendarID);
  // Get active dates
  var getDateStart = spreadsheet.getRange('A2').getValue();
  var getDateEnd = spreadsheet.getRange('A32').getValue();
  var from = new Date(getDateStart);
  var todate = new Date(getDateEnd);

//  var from = new Date();
//  var todate = new Date();

  // Get Calendar dates from spreadsheet
  var events = calendarName.getEvents(from, todate);
  // Delete from dates listed above

    for (var x=0; x<events.length;x++){

        var removeEvent = events[x];
//             if(removeEvent.getDescription().startwith("PA"){
                  removeEvent.deleteEvent();
                  Logger.log(removeEvent);
//             }

        }
        }

        /*
 //Add Events to Calendar
 //Get active sheet
    var spreadsheet = SpreadsheetApp.getActiveSheet();
 //Get Calendar ID

    var calendarID = spreadsheet.getRange("D1").getValue();

    var eventCal = CalendarApp.getCalendarById(calendarID);
 //Start loop for data

    var signups = spreadsheet.getRange("A2:B31").getValues();

           for (x=0; x<signups.length;x++)

           {

             var shift = signups[x];

             var startTime = shift[0];

             var volunteer = shift[1];

 //Get the id save it to Calendar

             var calendarEvent  = eventCal.createAllDayEvent(volunteer, startTime);

             shift[4] = calendarEvent.getId();
             //load the event
           
       
           }
}
*/

   

[Alan Wells]

Rows can be deleted. Content can be cleared.  If rows, columns or ranges are deleted then it shifts cells.  If you don't want to shift rows, then you can clear content.

https://developers.google.com/apps-script/reference/spreadsheet/range?hl=en#clearoptions

[Cory Davenport]

I don't want to delete the rows or columns in the spreadsheet.  The spreadsheet has the data that I am using to delete calendar events. The script is tied to the spreadsheet and calendar.

[Alan Wells]

What about clearing the content of the cells?  I don't see any thing in your code that is removing anything from the spreadsheet.

[Cory Davenport]

That is because I don't want to remove anything from the spreadsheet. I am using the spreadsheet Column A for calendar events dates (April 1, 2020 to May 1, 2020). Column B will be used for the actual calendar events.  D1 contains the calendar ID. The code is supposed to delete all current events based on the dates in Column A...FROM THE CALENDAR. Then the second part of the script adds the data from Column B to the Calendar.  It is working other than the loop seems to be missing the last Date in the range (in this instance May 1). It is deleting all the other events from my calendar except that.  I would like to add it so that no matter how many dates I put into Column A that the script will continue to work without me having to change the getDateEnd

 var getDateStart = spreadsheet.getRange('A2').getValue();

  var getDateEnd = spreadsheet.getRange('A32').getValue();Enter code here...

[Alan Wells]

I misunderstood.

You can check how many events that the calendar search is getting.

var events = calendarName.getEvents(from, todate);

Logger.log('events.length: ' + events.length);//View the Logs to see the print out


If the number of events found aren't the same as expected, then the search isn't working as intended.  And if it's not getting all the events, then that would be why it's not deleting all the events that you expected it to delete.





The endTime parameter is "non-inclusive"

Quote from documentation:
the end of the time range, non-inclusive

You would need to add some time to the endTime (date) to include the last day in the dates.

[Cory Davenport]

No worries. The more I learn, the more precise I can be about what I am doing. Yes, it is definitely missing the last event. So there is no way to keep the loop going until no dates are detected in Column A and I will have to adjust range manually?

[Alan Wells]

It looks like your dates are in column A from row 2 to row 32, and you are using the value in cell A32 as the end date.  That assumes that your dates will always be nicely arranged in perfect order with the start (oldest) in row 2 and the newest date in row 32.  If that's the case, then "no problem" but you still need to deal with the "non inclusive" endTime.  Right now you are getting multiple events in one search.  You could get just the events for one day, one at a time.  That's less efficient.  Or you could get all the events from beginning to end, and then do one separate look-up, for the last end date, because the endTime is non-inclusive.  It's excluding the events from the endTime.

You could use:

getEventsForDay(date);//Has no end parameter so doesn't exclude any events - but just good for one day

and delete those separately, after the loop is done to delete all the other events.

I was thinking that you could generate an endTime date that is one day newer.  As long as it will take a date into the future, then it would work all the time.  If the endTime was today, then you'd need to create a new endTime that was tomorrow in order for it to include today.

If the events are All-Day events, then I'd probably add a day to the endTime and then use that.  If they are hourly events, I'd probably add a few minutes to the endTime.

What do you think you're preference would be?

[Cory Davenport]

My plan is to always have the dates in Column A. The purpose of this is for ease of data entry of a schedule.  Events are all day events.

So if I use

getEventsForDay(date);

instead of

var getDateStart = spreadsheet.getRange('A2').getValue();

  var getDateEnd = spreadsheet.getRange('A33').getValue();

  var from = new Date(getDateStart);
  var todate = new Date(getDateEnd);

How would I get the full range dates?

Would it be

getEventsForDay(4/1/2020 , 5/1/2020);

I was hoping that there was a way to detect the last date in the spreadsheet as my plan is to just enter the info into the spreadsheet and run the script without having to change any of the script. Eventually this spreadsheet will have all the dates of the year in it.  The purpose of delete_events is to just make sure the events don't get posted 2 times when there is a change to the schedule. 

I was looking at this for guidance and trying to figure out if it would somehow work for me.

 

       

function setFormulas(){
   var ss = SpreadsheetApp.getActive()          
   var sheet = SpreadsheetApp.getActiveSheet()
   var cell = ss.getActiveCell()
   var cell1 = ("C2");
   var formulaCell = ("A5");
   var cell2 = ("C3");
   var cell1isblank = SpreadsheetApp.getActiveSheet().getRange(cell1).isBlank()
   var cell2isblank = SpreadsheetApp.getActiveSheet().getRange(cell2).isBlank()

    if (cell1isblank == false && cell2isblank == true) {
  SpreadsheetApp.getActiveSheet().getRange(formulaCell).setFormula("=formula1")
    }
    else if (cell2isblank == false && cell1isblank == true ) {
SpreadsheetApp.getActiveSheet().getRange(formulaCell).setFormula("=formula2")
    }
  //}
   else {
  SpreadsheetApp.getActiveSheet().getRange(formulaCell).setFormula("=Formula3")
  }
}  

   

[Michael Ellis]

Cory, 

I think your problem is with your getEvents() statement.   The parameters it needs are timestamps.   That means the start date and end date parameters are more fully described as start date/start time and end date/end time.   You are providing only the date component of the parameter.   So the events that are retrieved in your search start at (for example) May 2, 2020 at 0:00AM and continue until May 5, 2020 at 0:00AM.   That means you would get all the events placed on the calendar on May 2 and May 4.   Things on May 5 would not be included.   That's what AJ is explaining when he says the end time is not inclusive.

You can fix your problem by adding 1 day to the end date that you get from the last cell in your spreadsheet.   

Mike

[Cory Davenport]

Michael,

Thank you for that explanation. I did add an extra date to the end of my spreadsheet, and it is working as is now. However, this means I need to change the script every time I add more dates into the spreadsheet. Is there a way to run the loop until it detects a blank cell in Column A? I'm sure I will need to somehow change my getRange to include isBlank...

// Find Calendar ID
  var calendarID = spreadsheet.getRange("D1").getValue();
  // Find Calendar from ID
  var calendarName = CalendarApp.getCalendarById(calendarID);
  // Get active dates
  var getDateStart = spreadsheet.getRange('A2').getValue();

  var getDateEnd = spreadsheet.getRange('A33').getValue();

  var from = new Date(getDateStart);
  var todate = new Date(getDateEnd);
  // Get Calendar dates from spreadsheet
  var events = calendarName.getEvents(from, todate);

  //View the Logs to see the print out

  Logger.log('events.length= ' + events.length);

  // Delete from dates listed above

    for (var x=0; x<events.length;x++){

        var removeEvent = events[x];

        removeEvent.deleteEvent();
                 Logger.log(removeEvent);              
                        }
       
        }

[Michael Ellis]

Cory,

Use something like this:  

var lastRow = sheet.getLastRow();

var getDateEnd = spreadsheet.getRange('A'+lastRow).getValue();

I haven't tested this but it should be close to right.

Give that a try.  if you need more help, share your sheet and I'll help you debug it.

Documentation link:  https://developers.google.com/apps-script/reference/spreadsheet/spreadsheet?hl=en#getlastrow

Michael G. Ellis

--
You received this message because you are subscribed to a topic in the Google Groups "Google Apps Script Community" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/google-apps-script-community/SHAYPU8Cjug/unsubscribe.
To unsubscribe from this group and all its topics, send an email to google-apps-script-c...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/7f10d64b-6e49-491f-98de-9d45bfddc9b3%40googlegroups.com.

[Cory Davenport]

So the first part of my script is working perfectly.  Thank you again to everyone's help. 

Now I am trying to get the second part of my script to work with a dynamic range.  I tried basically copying the code for deleting events and changing the end to add events to calendar, but have been messing with it for 2 days and seem to be stuck. 

I am getting a "Range not Found" error on this line

var signups = spreadsheet.getRange('A2:endRange').getValues();


Before I was using a static range

var signups = spreadsheet.getRange("A2:B31").getValues();

This is the full version of my current add events code that isn't working

           
function delete_events()
{
/*

// Find Spreadsheet
  var spreadsheet = SpreadsheetApp.getActiveSheet();

  // Find Calendar ID
  var calendarID = spreadsheet.getRange("A1").getValue();

  // Find Calendar from ID
  var calendarName = CalendarApp.getCalendarById(calendarID);
  // Get active dates
  var getDateStart = spreadsheet.getRange('A2').getValue();

  var lastRow = spreadsheet.getLastRow();

  var getDateEnd = spreadsheet.getRange('A'+lastRow).getValue();

  Logger.log('End date = ' + getDateEnd);

  var from = new Date(getDateStart);
  var todate = new Date(getDateEnd);
  // Get Calendar dates from spreadsheet
  var events = calendarName.getEvents(from, todate);
  //View the Logs to see the print out
  Logger.log('events.length= ' + events.length);
  // Delete from dates listed above
    for (var x=0; x<events.length;x++){
        var removeEvent = events[x];
        removeEvent.deleteEvent();
                 Logger.log(removeEvent);              
                        }

 {
 */

 
 //Add Events to Calendar
 //Get active sheet
    var spreadsheet = SpreadsheetApp.getActiveSheet();

 //Get Calendar ID
    var calendarID = spreadsheet.getRange("A1").getValue();
    var eventCal = CalendarApp.getCalendarById(calendarID);
 //Determine Range

    var getDateStart = spreadsheet.getRange('A2').getValue();

    var lastRow = spreadsheet.getLastRow();
    var lastColumn = spreadsheet.getLastColumn();
    var endRange = spreadsheet.getRange(lastRow,lastColumn).getA1Notation();
    Logger.log('lasRow = ' + lastRow);
    Logger.log('lastColumn =' + lastColumn);
    Logger.log('endRange =' + endRange); //currently returning 'B35'
   
//Begin Loop for adding events
    var signups = spreadsheet.getRange('A2:endRange').getValues();
    Logger.log('signups ='+ signups);

     for (x=0; x<signups.length;x++)

             var shift = signups[x];

             var volunteer = shift[0];
             Logger.log('volunteer =' + volunteer);

 //Get the id save it to Calendar

             var calendarEvent  = eventCal.createAllDayEvent(volunteer, startTime);

             shift[4] = calendarEvent.getId();}

This is how my code used to read:

function delete_events()
{


//Add Events to Calendar
 //Get active sheet
    var spreadsheet = SpreadsheetApp.getActiveSheet();

 //Get Calendar ID
    var calendarID = spreadsheet.getRange("A1").getValue();

    var eventCal = CalendarApp.getCalendarById(calendarID);
 //Start loop for data

    var signups = spreadsheet.getRange("A2:B31").getValues();

       for (x=0; x<signups.length;x++)

             var shift = signups[x];

             var startTime = shift[0];

             var volunteer = shift[1];

 //Get the id save it to Calendar

             var calendarEvent  = eventCal.createAllDayEvent(volunteer, startTime);

             shift[4] = calendarEvent.getId();
             //load the event
      }     

[Alan Wells]

I think that this should work:

var newRange = 'A2:' + endRange;//Concatenate strings

var signups = spreadsheet.getRange(newRange).getValues();

[Cory Davenport]

AJ.Addons:  Thank you, that was what I needed for adding events.  However, it seems that I am back to my original issue with deleting events.  The last event created doesn't get deleted. If I add an extra date to the spreadsheet, it will get added to the calendar.

For example.  Currently my script runs through May 5th. Running it the first time, all the events are loaded correctly.  However, when I run it the second time.  All the events are deleted through May 4th and May 5th now has two calendar events.  Adding dates only changes which date gets extra dates. 

         
function delete_events()
{

// Find Spreadsheet
  var spreadsheet = SpreadsheetApp.getActiveSheet();
  // Find Calendar ID
  var calendarID = spreadsheet.getRange("A1").getValue();
  // Find Calendar from ID
  var calendarName = CalendarApp.getCalendarById(calendarID);
  // Get active dates
  var getDateStart = spreadsheet.getRange('A2').getValue();
  var lastRow = spreadsheet.getLastRow();
  var getDateEnd = spreadsheet.getRange('A'+lastRow).getValue();
  Logger.log('End date = ' + getDateEnd);
  var from = new Date(getDateStart);
  var todate = new Date(getDateEnd);
  // Get Calendar dates from spreadsheet
  var events = calendarName.getEvents(from, todate);
  //View the Logs to see the print out
  Logger.log('events.length= ' + events.length);
  // Delete from dates listed above
    for (var x=0; x<events.length;x++){
        var removeEvent = events[x];
        removeEvent.deleteEvent();

                 Logger.log('removeEvent =' + removeEvent);              
                        }

 
 //Add Events to Calendar
 //Get active sheet
    var spreadsheet = SpreadsheetApp.getActiveSheet();
 //Get Calendar ID
    var calendarID = spreadsheet.getRange("A1").getValue();
    var eventCal = CalendarApp.getCalendarById(calendarID);
 //Start loop for data

    var getDateStart = spreadsheet.getRange('A2').getValue();
    var lastRow = spreadsheet.getLastRow();
    var lastColumn = spreadsheet.getLastColumn();
    var endRange = spreadsheet.getRange(lastRow,lastColumn).getA1Notation();

    Logger.log('endRange =' + endRange);

    Logger.log('lasRow = ' + lastRow);
    Logger.log('lastColumn =' + lastColumn);
    Logger.log('endRange =' + endRange);

    var newRange = 'A2:' + endRange;//Concatenate strings
    var signups = spreadsheet.getRange(newRange).getValues();

    Logger.log('signups ='+ signups);

     for (x=0; x<signups.length;x++){
             var shift = signups[x];

             var startTime = shift[0];
             var volunteer = shift[1];

             Logger.log('volunteer =' + volunteer);
 //Get the id save it to Calendar
             var calendarEvent  = eventCal.createAllDayEvent(volunteer, startTime);

             Logger.log('Calendar Events =' +calendarEvent);
             shift[4] = calendarEvent.getId();
             Logger.log('shift = '+shift);
    }
    }

[Cory Davenport]

Probably not the most elegant way to do it, but I think I figured it out.  I added another loop for the last event date.

var lastEvent = calendarName.getEventsForDay(getDateEnd);
  Logger.log('lastEvent = ' + lastEvent.length);
    for (var y=0; y<lastEvent.length;y++){
      var removeLast = lastEvent[y];
      removeLast.deleteEvent();
      Logger.log('Number of events removed =' + lastEvent);
      }

Here is my final code.  Thank you everyone for your help!!!

function create_schedule()
{
//This first part deletes all Calendar Events from the range of dates of the spreadsheet

// Find Spreadsheet
  var spreadsheet = SpreadsheetApp.getActiveSheet();

  // Find Calendar ID... Put google Calendar ID in A1 on spreadsheet

  var calendarID = spreadsheet.getRange("A1").getValue();
  // Find Calendar from ID
  var calendarName = CalendarApp.getCalendarById(calendarID);

  // Get active dates... Put dates in Column A starting at A2

  var getDateStart = spreadsheet.getRange('A2').getValue();
  var lastRow = spreadsheet.getLastRow();
  var getDateEnd = spreadsheet.getRange('A'+lastRow).getValue();
  Logger.log('End date = ' + getDateEnd);
  var from = new Date(getDateStart);
  var todate = new Date(getDateEnd);
  // Get Calendar dates from spreadsheet
  var events = calendarName.getEvents(from, todate);
  //View the Logs to see the print out
  Logger.log('events.length= ' + events.length);

  // Loop to delete from dates listed above

    for (var x=0; x<events.length;x++){
        var removeEvent = events[x];
        removeEvent.deleteEvent();
                 Logger.log('removeEvent =' + removeEvent);

             
  // Loop to delete the last date in range.                       }
  var lastEvent = calendarName.getEventsForDay(getDateEnd);
  Logger.log('lastEvent = ' + lastEvent.length);
    for (var y=0; y<lastEvent.length;y++){
      var removeLast = lastEvent[y];
      removeLast.deleteEvent();
      Logger.log('Number of events removed =' + lastEvent);
      }}
 
 
 //This part adds events to calendar for the range of dates in the spreadsheet

 //Get active sheet
    var spreadsheet = SpreadsheetApp.getActiveSheet();
 //Get Calendar ID
    var calendarID = spreadsheet.getRange("A1").getValue();
    var eventCal = CalendarApp.getCalendarById(calendarID);
 //Start loop for data
    var getDateStart = spreadsheet.getRange('A2').getValue();
    var lastRow = spreadsheet.getLastRow();
    var lastColumn = spreadsheet.getLastColumn();
    var endRange = spreadsheet.getRange(lastRow,lastColumn).getA1Notation();
    Logger.log('endRange =' + endRange);
    Logger.log('lasRow = ' + lastRow);
    Logger.log('lastColumn =' + lastColumn);
    Logger.log('endRange =' + endRange);
    var newRange = 'A2:' + endRange;//Concatenate strings
    var signups = spreadsheet.getRange(newRange).getValues();