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.

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

Current results(2020) with new V8 engine suggests that e.oldValue is always included, when the cell is deleted or backspaced or each of the character is deleted one by one.

Actions:

The following actions are taken one by one and the event object is recorded on each action with

const onEdit = e => console.log(JSON.stringify(e));

|   | B(Value)      | Action taken               |
|---+---------------+----------------------------|
| 3 | deletedValue1 | deleteFromPC/insertValue   |
| 4 | deletedValue2 | backspaceFromPC            |
| 5 | deletedValue3 | deleteCharacter1by1PC      |
| 6 | deletedValue4 | clearFromMOBILE            |
| 7 | deletedValue5 | deleteCharacter1By1MOBILE  |
| 8 | deletedValue6 | overwriteValue6To6.1MOBILE |
| 9 | deletedValue7 | overwriteValue7To7.1PC     |

• insertValue: The text "deletedValue1" is inserted into B3.

• autofillInsertedValue: The text in B3 is autofilled to B9. B3:B9 contains "deletedValue1" to "deletedValue7"

• deleteFromPC: B3 is deleted using Delete button.

• backspaceFromPC: B4 is selected and Backspace button is pressed. This fully clears B4.

• deleteCharacter1by1PC: B5 is selected> Enter is pressed to enter edit mode> Each character is cleared out one by one> Enter is pressed again to exit edit mode.

• clearFromMOBILE: In mobile app, Options is entered by long pressing and clear is pressed.(on B6)

• deleteCharacter1By1MOBILE: Same as above, but from Mobile.(on B7)

• overwriteValue6To6.1MOBILE: B8 value is overwritten from "deletedValue6" to "deletedValue6.1"

• overwriteValue7To7.1PC: Same as above , but from PC on B9.

Results:

The following json provides the event object logged by each of the action mentioned above. The action is used as key of this result object, while the value is the actual event object logged.

{
  "insertValue": {
    "authMode": "LIMITED",
    "range": { "columnEnd": 2, "columnStart": 2, "rowEnd": 3, "rowStart": 3 },
    "source": {},
    "user": { "email": "", "nickname": "" },
    "value": "deletedValue1"
  },
  "autofillInsertedValue": {
    "authMode": "LIMITED",
    "range": { "columnEnd": 2, "columnStart": 2, "rowEnd": 9, "rowStart": 4 },
    "source": {},
    "user": { "email": "", "nickname": "" }
  },
  "deleteFromPC": {
    "authMode": "LIMITED",
    "oldValue": "deletedValue1",
    "range": { "columnEnd": 2, "columnStart": 2, "rowEnd": 3, "rowStart": 3 },
    "source": {},
    "user": { "email": "", "nickname": "" }
  },
  "backspaceFromPC": {
    "authMode": "LIMITED",
    "oldValue": "deletedValue2",
    "range": { "columnEnd": 2, "columnStart": 2, "rowEnd": 4, "rowStart": 4 },
    "source": {},
    "user": { "email": "", "nickname": "" }
  },
  "deleteCharacter1by1PC": {
    "authMode": "LIMITED",
    "oldValue": "deletedValue3",
    "range": { "columnEnd": 2, "columnStart": 2, "rowEnd": 5, "rowStart": 5 },
    "source": {},
    "user": { "email": "", "nickname": "" }
  },
  "clearFromMOBILE": {
    "authMode": "LIMITED",
    "oldValue": "deletedValue4",
    "range": { "columnEnd": 2, "columnStart": 2, "rowEnd": 6, "rowStart": 6 },
    "source": {},
    "user": { "email": "", "nickname": "" }
  },
  "deleteCharacter1By1MOBILE": {
    "authMode": "LIMITED",
    "oldValue": "deletedValue5",
    "range": { "columnEnd": 2, "columnStart": 2, "rowEnd": 7, "rowStart": 7 },
    "source": {},
    "user": { "email": "", "nickname": "" }
  },
  "overwriteValue6To6.1MOBILE": {
    "authMode": "LIMITED",
    "oldValue": "deletedValue6",
    "range": { "columnEnd": 2, "columnStart": 2, "rowEnd": 8, "rowStart": 8 },
    "source": {},
    "user": { "email": "", "nickname": "" },
    "value": "deletedValue6.1"
  },
  "overwriteValue7To7.1PC": {
    "authMode": "LIMITED",
    "oldValue": "deletedValue7",
    "range": { "columnEnd": 2, "columnStart": 2, "rowEnd": 9, "rowStart": 9 },
    "source": {},
    "user": { "email": "", "nickname": "" },
    "value": "deletedValue7.1"
  }
}

Inferences:

• e.oldValue key:

  • is present on all events, when cell is deleted(in any way) or overwritten
  • not present, when a empty cell is edited with new value or autofilled.

• e.value key:

  • is present, when a new value is inserted or old value is overwritten
  • not present, when value is deleted or cell is autofilled.

• Both value and oldValue are not present, if cell is autofilled.

Answer 4

For anyone not able to get e.oldValue to return the old value when deleting a cell using the delete or backspace key on a Google Sheet as mentioned by @Tanaike:

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

I could not get e.oldValue to return anything other than 'undefined' and I could not find the answer here, but @Tanaike explains the solution in more detail here:

---

The Short Answer:

The results of e.oldValue are determined by whether or not you have formatting applied (cell borders, background shading, etc.) to the edited cell.

---

Situation 1

If you have the default formatting applied to cell A1 with the text "Sample" in the cell, and you use the delete or backspace key (Mac or PC) to remove the text from the cell, you will get the following result:

e.oldValue === undefined.

*Note: if you do not get this result, you may have formatting applied that is not obvious, so use the Format > Clear formatting option and try again.

Situation 2

If you have formatting applied to cell A1, such as borders, background color, or font (I have tested those 3, but not all other possible options) with the text "Sample" in the cell, and you use the delete or backspace key (Mac or PC) to remove the text from the cell, you will get the following results:

e.oldValue === Sample

So, by formatting cells, columns or entire sheets in some way, you can then return and test by the value of an edited cell before deletion.

This behavior can be tested on a blank Google Sheet with the following code:

function onEdit(e) {
  console.log("This is the current value in e: " + e.value);
  console.log("This is the Old Value in e: " + e.oldValue);
  if (e.value) {
    console.log("There is a value in e");
  }
  if (e.oldValue) {
    console.log("There is an old value in e");
  }
  if (!e.value) {
    console.log("There is NOT a value in e");
  }
  if (!e.oldValue) {
    console.log("There is NOT an old value in e");
  }
}

Answer 5

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.