google script detect empty cell after edit

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.

Answer 2

The problem in your case is your middle condition, if(e.oldValue!=undefined) This is going to pick up cases where e.value is also undefined, but e.oldValue was not, and create an event. So you are never getting to your final case where you expect a freshly erased cell to delete the event.

I think the logic you want here is if(e.oldValue!=undefined && e.value!=undefined)

I think part of the confusion is stemming from mixing of null and undefined.

When received from the OnEdit trigger, the value of an empty cell is undefined, rather than null.

Because javascript evaluates undefined as false, simply checking if(!e.value) (if not value) should be sufficient, but if you want to be explicit you can check if(e.value === undefined) instead.

Because javascript also evaluates null as false, null == undefined as far as javascript is concerned. For clarity, I suggest changing all your checks to either if(!e.value) or if(!e.oldValue), or removing references to null and using undefined everywhere. This will make the script a lot more readable, since the current code makes it seem like null and undefined are expected to behave differently.

Answer 3

As of 10/21/2019, I no longer find these work arounds to work. When a cell has a value and then that value is deleted with a backspace or delete keystroke, google returns e.oldvalue as expected, but e.value has an odd object in it instead of a falsy response. It's a pretty confusing bug that is really confusing for those trying to automate with gSheets. The new work around now includes checking for data type. Here is a link to the new issue fully documented including the new work around.