Modify google calendar based on google spreadsheet data (prevent duplication)

Question

I have the following spreadsheet and I want to send a reminder to the people and also add the events in the calendar. I have written the following code which works but has issues.I have activated trigger that with each edit it runs the code. So, to prevent duplicate emails or duplicate "adding to the calendar", I added two columns eventid and calendarid which changes the cell to remindersent if reminder is sent or calendarupdated if the event is added to the calendar. Although it resolves the duplicate problem but causes other problems:

1. if I edit any information in spreadsheet, it will not send a reminder or update calendar since it already did. even if I delete the calendarsent text from calendarid, it adds a new event instead of modifying the existing event.

2. in the email column, I need to fill all the cells to get reminders. for example if I fill the first cell, and leave the second cell blank, and fill the third cell, the email that I entered in the third cell will not get the reminder.

I will appreciate if anyone can help me to improve my code. It will mean a lot to me. Thanks

 
function createCalendarEvent() {
var sheet  = SpreadsheetApp.getActiveSheet();
  
   // figure out what the last row is
  var lastRow1 = sheet.getLastRow();
 
  // the rows are indexed starting at 1, and the first row
  // is the headers, so start with row 5
  var startRow1 = 5;
  
 
  // grab column 6 (the 'days left' column) 
  var range = sheet.getRange(5,6,lastRow1-startRow1+1,1 );
  var numRows = range.getNumRows();
  var days_left_values = range.getValues();
   
  
    range = sheet.getRange(5, 1, lastRow1-startRow1+1, 1);
  var start_info_values = range.getValues();
  
     range = sheet.getRange(5, 2, lastRow1-startRow1+1, 1);
  var end_info_values = range.getValues();
  
  range = sheet.getRange(5, 3, lastRow1-startRow1+1, 1);
  var reminder_info_values = range.getValues();
  
     range = sheet.getRange(5, 4, lastRow1-startRow1+1, 1);
  var names_info_values = range.getValues();
  
  range = sheet.getRange(5, 5, lastRow1-startRow1+1, 1);
    var emails_info_values = range.getValues();
  
   range = sheet.getRange(5, 7, lastRow1-startRow1+1, 1);
    var eventID_info_values = range.getValues();
  
    range = sheet.getRange(5, 8, lastRow1-startRow1+1, 1);
    var CalID_info_values = range.getValues();

  
  var warning_count = 0;
  var msg = "";
  var complete="ReminderSent";
  var CalendarUpdated="CalendarUpdated";
  
   
      var calendarId = sheet.getRange("H2").getValue();
     var eventCal = CalendarApp.getCalendarById(calendarId);
   range = sheet.getRange(5, 1, lastRow1-startRow1+1, 8);
    var data = range.getValues();
  
    for (var i = 0; i < data.length; ++i) {
      
       var start = start_info_values [i][0];
       var end = end_info_values [i][0];
       var reminder_name = reminder_info_values[i][0];
       var names = names_info_values [i][0];

    var CalID = CalID_info_values [i][0]; //event marked CalendarUpdated
   
    if (CalID != CalendarUpdated) {
      var currentCell = sheet .getRange(startRow1 + i, 8);
      eventCal.createEvent(eminder_name, start, end, {description: names} );
    
      currentCell.setValue(CalendarUpdated);
    
  }
  }
   
  // Loop over the days left values
  for (var i = 0; i <= numRows - 1; i++) {
    var days_left = days_left_values[i][0];
    if(days_left <=2) {
     
      //var reminder_name = reminder_info_values[i][0];
       
      msg = msg + "Reminder: "+reminder_name+" is due in "+days_left+" days.\n";
      warning_count++;
    }
    var emails= emails_info_values [i][0];
      if(warning_count) {
        var eventID= eventID_info_values[i][0];
        if(eventID!=complete){
           var currentCell = sheet .getRange(startRow1 + i, 7);
    MailApp.sendEmail(emails,"Reminder", msg);
          currentCell.setValue(complete);
        }
  }
    
  }
}
  

  

Answer 1

if I edit any information in spreadsheet, it will not send a reminder or update calendar since it already did. even if I delete the calendarsent text from calendarid, it adds a new event instead of modifying the existing event.

There are two things you can do, either

• delete onEdit the ReminderSent and CalendarUpdated values for the edited row, so that the code runs again
• or (much better) get rid of the eventid and calendarid columns in general and simply run your code for the edited row only

In both cases, you need to work with event objects. This would allow you to run your code only on the edited row and thus avoid duplicates and looping through all rows.

Sample to verify if a new event was created or an already existing one updated:

function createCalendarEvent(event) {
 var calendarId = sheet.getRange("H2").getValue();
 var eventCal = CalendarApp.getCalendarById(calendarId);
 var values = event.values;
 var start = values[0][0];
 var end = values[0][1];
 var title = values[0][2];
 var exists = eventCal.getEvents(start, end, {search: title});
 if (exists.length == 0){
   eventCal.createEvent(title, start, end);
  } else {
   var event = eventCal.getEventById(exists[0].getId());
   var range = e.range;
   var column = range.getColumn();
   switch(column){
     case 1:
     case 2:
       event.setTime(start, end);
      break;
     case 3:
       event.setTitle(title);
      break;
     //...
    }
  //...
  }
/ ...  
}

if I fill the first cell, and leave the second cell blank, and fill the third cell, the email that I entered in the third cell will not get the reminder.

If you try to send an email to an empty string, it is likely to make your code error and stop. To avoid this, verify either the email value is not empty before sending the email:

if(emails != "" && emails != " "){
  MailApp.sendEmail(emails,"Reminder", msg);
}