javascript - google script detect empty cell after edit or delete

Question

I have a problem with my code. It seems fine but it doesn't work and I really don't know why. I tried everything.

I want sync my google excel with my google calendar. Everything work but now I want make event in calendar when I edit only blank cell:

if (e.oldValue == null) { // also tried if(e.oldValue == undefinded)
var date = new Date(dayRange);
cal.createAllDayEvent(
  e.value,
  date,
)

}

And it works fine. But next I want delete event from calendar when I delete cell (so it is blank again):

else if(e.value==null){
  var events = cal.getEvents(date, {
  search: ss.getRange(row,2)
  });  
  for (i in events){
    events[i].deleteEvent();
}

After i "deleted" cell in calendar I get next event with blink title and I don;t know why this event is creating. It seems like this "else if" doesn't work. I really don't know why. I read all example and code seems ok.

This is how I create my trigger:

function createEditTrigger() {
   var ss = SpreadsheetApp.getActive();
   ScriptApp.newTrigger('ToCalendar')
   .forSpreadsheet(ss)
   .onEdit()
   .create();
 }     

I will be very grateful for all the advice.

EDIT

Full code:

function ToCalendar(e) {  
    var ss = SpreadsheetApp.getActiveSheet();
    var cal = CalendarApp.getCalendarById("myID");

    var range = e.range;
    var column = range.getColumn();
    var row = range.getRow();

    var day = ss.getRange(1,column);
    var dayRange = day.getValues();

    if ((e.value != null) && (e.oldValue == null)) {
        var date = new Date(dayRange);
         cal.createAllDayEvent(
         ss.getRange(row,2).getValue(),
         date,
         {
             description: e.value,//ss.getRange(row, column).getValue(),
         }
        )
     }

    //If we edit cell:

     else if(e.oldValue!=undefined){
        var events= cal.getEventsForDay(date,{search: e.oldValue});
        var ev= events[0];
        Logger.log(ev);
        ev.deleteEvent();
        cal.createAllDayEvent(ss.getRange(row,2).getValue(),date, 
        {description: ss.getRange(row, column).getValue()})


   // If we delete cell   



    else if((e.value == null) && (e.oldValue != null)){
         var events = cal.getEvents(date, {
         search: ss.getRange(row,2)
       });  
         for (i in events){
            events[i].deleteEvent();
          }

  }

Creat trigger:

function createEditTrigger() {
   var ss = SpreadsheetApp.getActive();
   ScriptApp.newTrigger('ToCalendar')
   .forSpreadsheet(ss)
   .onEdit()
   .create();
  }  

And screen of my test sheet:

Answer 1

You want to run each function for the case for editing an empty cell and the case for deleting a value of cell. If my understanding is correct, how about this workaround? I think that there may be several workarounds. So please think of this as one of them. In this workaround, it supposes that you are using onEdit(e). When the cell is changed, e of onEdit(e) is changed as follows.

1. In the case for editing a value to empty cell

   • e.value is included in e.
   • e.oldValue is NOT included in e.

2. In the case for overwriting a cell with a value by other value

   • Both e.value and e.oldValue are included in e.

3. In the case for deleting a value from a cell with a value

   • Both e.value and e.oldValue are NOT included in e.

Using above results, in order to run each function for the case for editing an empty cell and the case for deleting a value of cell, you can use the following sample script.

Sample script :

function onEdit(e) {
  if (("value" in e) && !("oldValue" in e)) {
    Logger.log("In the case for editing a value to empty cell")
  }
  if (!("value" in e) && !("oldValue" in e)) {
    Logger.log("In the case for deleting a value from a cell with a value")
  }
}

Of course, you can also use the following script.

function onEdit(e) {
  if ((e.value != null) && (e.oldValue == null)) {
    Logger.log("In the case for editing a value to empty cell")
  }
  if ((e.value == null) && (e.oldValue == null)) {
    Logger.log("In the case for deleting a value from a cell with a value")
  }
}

Note :

• If you want to run the methods which require to authorize, please install a trigger to onEdit().

Reference :

• Event Objects

If I misunderstand your question, I'm sorry.

Edit :

The syntax errors are removed and modified your script. In this modified script,

• When the empty cell is edited, the script in if ((e.value != null) && (e.oldValue == null)) { script } is run.
• When the cell with a value is overwritten by a value, the script in else if(e.oldValue!=undefined) { script } is run.
• When the value of cell with a value is removed, the script in else if((e.value == null) && (e.oldValue == null)) { script } is run.

Modified script :

function ToCalendar(e) {  
  var ss = SpreadsheetApp.getActiveSheet();
  var cal = CalendarApp.getCalendarById("myID");
  var range = e.range;
  var column = range.getColumn();
  var row = range.getRow();
  var day = ss.getRange(1,column);
  var dayRange = day.getValues();
  if ((e.value != null) && (e.oldValue == null)) { // When the empty cell is edited, this becomes true.
    var date = new Date(dayRange);
    cal.createAllDayEvent(ss.getRange(row,2).getValue(),date,{
      description: e.value,//ss.getRange(row, column).getValue(),
    })

  // In your situation, this might not be required.
  } else if(e.oldValue!=undefined) { // When the cell with a value is overwritten by a value, this becomes true.
    //If we edit cell:
    var events= cal.getEventsForDay(date,{search: e.oldValue});
    var ev= events[0];
    Logger.log(ev);
    ev.deleteEvent();
    cal.createAllDayEvent(ss.getRange(row,2).getValue(),date,{description: ss.getRange(row, column).getValue()})

  } else if((e.value == null) && (e.oldValue == null)) { // When the value of cell with a value is removed, this becomes true.
    // If we delete cell
    var events = cal.getEvents(date, {
      search: ss.getRange(row,2)
    });  
    for (i in events){
      events[i].deleteEvent();
    }
  }
}

Added:

It was confirmed that the specification of the event object is changed. The following modification was confirmed. This was mentioned by @I'-'I.

From:

• In the case for deleting a value from a cell with a value
  • Both e.value and e.oldValue are NOT included in e.

To:

• In the case for deleting a value from a cell with a value
  • e.value and e.oldValue are included in e.
    • e.value is an object like {"oldValue":"deleted value"}.
    • e.oldValue is a string like "deleted value".

By this modification, when the value was removed from a cell with the value, this can be checked by the following script.

if (e.value.oldValue) {
    // value was removed from cell.
} else {
    // value is NOT removed from cell.
}

Note :

• In the case for editing a value to empty cell and in the case for overwriting a cell with a value by other value, each result is not changed.