Can't delete an event in my google calendar using scripts

Question

I made a spreadsheet where I can easily manage my events in my google Calendar. In the spreadsheet Row 1 has the dates, Row 2 has the specifics of the appointment and Row 3 saves the eventId of the event created by the script. Every week starts on a new row.

The script works like it should and looks like this:

function CalenderUpdate() {

  var calId = "xxxxxxxxxxxxxxxx@group.calendar.google.com";
  var descr = "";
  var date
  var titel
  var sheet = SpreadsheetApp.getActiveSheet();
  var Afspraak = sheet.getRange(2,1,3,5).getValues();
  var cal = CalendarApp.getCalendarById(calId);

  for (j=2;j<9;j=j+3){
    Afspraak = sheet.getRange(j,1,3,5).getValues();

    for (i=0; i<5; i++){
      descr ="";
      date = Afspraak[0][i];
      titel = Afspraak[1][i];

      if (titel == "A"){ descr = "Late 14:00 - 21:00";}
      if (titel == "M"){ descr = "Early 6:00 - 14:00";}
      if (Afspraak[1][i] != ""){
        var event = cal.createAllDayEvent(titel,date,{description:descr});
        sheet.getRange(j+2,i+1).setValue(event.getId());
      }
    }
  }
}

Now I want a new function. If I edit something in the schedule, I want it to update in my calendar. So I added a function onEdit that would delete the event if one created on that day, and if necessary create a new. I started with this code to delete the event if something is edited. But it doesn't delete the appointment and I can't figure out why.

function onEdit(e){
  var ss = SpreadsheetApp.getActiveSheet();
  var range = e.range;
  var rij = range.getRow();
  var col = range.getColumn();
  var afspraakId = ss.getRange(rij+1,col).getValue();

  if (rij % 3 == 0) { ss.getRange(1,1).setValue(afspraakId); }

  var calId = "xxxxxxxxxxxxxxx@group.calendar.google.com";
  var cal = CalendarApp.getCalendarById(calId);
  var event = cal.getEventSeriesById(id);
  event.deleteEventSeries();

}

Hope someone can help me?

EDIT:

I changed the onEdit(e) to onEditInstallable(e) but the script never get triggered.
Even if I add a trigger by the menu

What do I do wrong?

EDIT2:

I've done some editing on my script. This is the end result:

function test_onEdit() {
  onEditInstallable({
    user : Session.getActiveUser().getEmail(),
    source : SpreadsheetApp.getActiveSpreadsheet(),
    range : SpreadsheetApp.getActiveSpreadsheet().getActiveCell(),
    value : SpreadsheetApp.getActiveSpreadsheet().getActiveCell().getValue(),
    authMode : "LIMITED"
  });
}

function onEditInstallable(e){ 
  var ss = SpreadsheetApp.getActiveSheet();
  var range = e.range;
  var rij = range.getRow();
  var col = range.getColumn();

  if (rij % 3 == 0 && col < 8) {

    var cal = CalendarApp.getCalendarById("xxxxxxxxxxxxxxxxxx@group.calendar.google.com");

    var event
    var geg = ["","",""];
    var descr ="";


    for (i = 0 ; i < 3 ; i++){ geg[i] = ss.getRange(rij-1+i,col).getValue();}

    if (geg[2] != ""){
      event = cal.getEventSeriesById(geg[2]);
      event.deleteEventSeries();
      ss.getRange(rij+1,col).setValue("");
    }

    if (geg[1] != ""){
      event = cal.createAllDayEvent(geg[1],geg[0],{description:descr});
      ss.getRange(rij+1,col).setValue(event.getId());
    }
  } 
}

The script works if I run the test_OnEdit. But the onEditInstallable doesn't trigger automatic? I'm an amateur in programming and I don't understand much of this page. Hope someone can help me figure this out.

You need an installable trigger, see [this answer](http://stackoverflow.com/a/26318730) — , Apr 15 '16 at 20:24
following your edit : is your script working when you run the function manually ? — Serge insas, Apr 16 '16 at 09:02
if i change: var rij = range.getRow();var col = range.getColumn(); too rij = 3 & col = 1 the script works if I run it manually — Zorrotwee, Apr 16 '16 at 10:13
to help debugging this you can either set the email notification to "immediately" or, even better, simulate an event by script to reproduce the real condition of use. See this post (from Mogsdad) for a brilliant example : http://stackoverflow.com/questions/16089041/how-can-i-test-a-trigger-function-in-gas/16089067#16089067 — Serge insas, Apr 16 '16 at 22:01

score 0 · Answer 1 · answered Apr 17 '16 at 09:47

Your second edit is a good approach, I'd suggest to add a couple of logs in the script to check the condition values.

I'd rather use the comments for this but it would definitely be too long and hard to read so I use the 'answer' format.

function onEditInstallable(e){ 
  var ss = SpreadsheetApp.getActiveSheet();
  var range = e.range;
  var rij = range.getRow();
  var col = range.getColumn();
  Logger.log('rij = '+rij+'  col = '+col);
  if (rij % 3 == 0 && col < 8) {

    var cal = CalendarApp.getCalendarById("xxxxxxxxxxxxxxxxxx@group.calendar.google.com");

    var event
    var geg = ["","",""];
    var descr ="";


    for (i = 0 ; i < 3 ; i++){ geg[i] = ss.getRange(rij-1+i,col).getValue();}
    Logger.log('geg[2] = '+geg[2]);
    if (geg[2] != ""){
      event = cal.getEventSeriesById(geg[2]);
      event.deleteEventSeries();
      ss.getRange(rij+1,col).setValue("");
    }

    if (geg[1] != ""){
      event = cal.createAllDayEvent(geg[1],geg[0],{description:descr});
      ss.getRange(rij+1,col).setValue(event.getId());
    }
  } 
}

score 0 · Accepted Answer · edited Apr 17 '16 at 12:34

I used this last piece of code and linked the test_onEdit script to a button in the spreadsheet. On the first time testing it from there Google gave a pop-up asking for extra authorizations. From then on everything works like a charm.

Now my spreadsheet automatically edits my agenda if I change something in the spreadsheet.

Thanks for the help everyone.

function test_onEdit() {
      onEditInstallable({
        user : Session.getActiveUser().getEmail(),
        source : SpreadsheetApp.getActiveSpreadsheet(),
        range : SpreadsheetApp.getActiveSpreadsheet().getActiveCell(),
        value : SpreadsheetApp.getActiveSpreadsheet().getActiveCell().getValue(),
        authMode : "LIMITED"
      });
    }
    function onEditInstallable(e){ 
      var ss = SpreadsheetApp.getActiveSheet();
      var range = e.range;
      var rij = range.getRow();
      var col = range.getColumn();
      Logger.log('rij = '+rij+'  col = '+col);
      if (rij % 3 == 0 && col < 8) {

        var cal = CalendarApp.getCalendarById("xxxxxxxxxxxxxxxxxx@group.calendar.google.com");

        var event
        var geg = ["","",""];
        var descr ="";


        for (i = 0 ; i < 3 ; i++){ geg[i] = ss.getRange(rij-1+i,col).getValue();}
        Logger.log('geg[2] = '+geg[2]);
        if (geg[2] != ""){
          event = cal.getEventSeriesById(geg[2]);
          event.deleteEventSeries();
          ss.getRange(rij+1,col).setValue("");
        }

        if (geg[1] != ""){
          event = cal.createAllDayEvent(geg[1],geg[0],{description:descr});
          ss.getRange(rij+1,col).setValue(event.getId());
        }
      } 
    }

score 0 · Answer 3 · answered Oct 18 '19 at 13:52

Try this:

function deleteEvent(event) {
  if (typeof event != 'undefined') {
    Logger.log("Deleting event %s", event.getTitle())
     event.deleteEvent()
  }
}

function deleteEvents(eventCal,startTime,endTime,title){
    //  var oldEvents = eventCal.getEvents(startTime, endTime, {search: title});  
      var oldEvents = eventCal.getEvents(startTime, endTime);
      Logger.log("oldEvents %s", oldEvents);
      for (var j = 0; j < oldEvents.length; j++){  
         Logger.log("oldEvents[j] %s", oldEvents[j]);
         deleteEvent(oldEvents[j]);
      }
  }

Can't delete an event in my google calendar using scripts

EDIT:

EDIT2:

3 Answers3