google script detect empty cell after edit -- Take 2

Question

This is related to onEdit functionality and a question with the same title found here .

I am not getting the responses they are indicating I might or should when cells go from having values to empty, and those work around solutions did not work as a result. It's as if something has changed since those answers were given or there is different underlying behavior.

Here is a link to spreadsheet with a simple example.

In this example, I have a cell with data validation on it. In context, this cell allows me to pick sales ticket IDs from a list to recall data. After I have recalled the data, if I remove the value from the selection cell with backspace or delete, the onEdit function should be called, a falsy value should exist for "e.value" and I should be able to clear the data it retrieved. Pretty simple.

== Full Testing Protocal ==

1) When the cell is blank and I pick a value from the list, I test the e.value property for truthy, i get truthy response, and it completes a function to get data.

2) When the cell is NOT blank and I pick a value from the list, I test the e.value property for truthy, i get truthy response, and it completes a function to get data.

3) When the cell is NOT blank, and I click into the cell and select backspace or delete to empty the cell. I test the e.value property for truthy and instead of getting a falsy response, I get an object back?! {old_value=xxxx}.

So, not only do I not get a falsy value, I get an object returned instead that is missing the value item in it??

From looking at the other post, it seems when there is no "value", google has decided to leave the "value" item out of the e object instead of leaving "value" item in the e object and setting it to null or blank.

I don't know why that was chosen, but that's the way I interpret that. That said, it seems to me that decision should not have mattered.

If I'm testing for an item in an object and the item doesn't exist, I should get a falsy of some sort, but instead, I'm getting an object returned to me?!

The only way that I know of that might to work around this is to check for variable type to see if an object was returned which is really terribly confusing for those new to programming and shouldn't be required. I will try that shortly, but wanted to get this out first.

I do recall running into this several years ago and not taking the time to try to get a sound answer.

function onEdit(e){
  Logger.log("onEdit running")
  var ss =        SpreadsheetApp.getActiveSpreadsheet();  

  if (ss.getRangeByName("sTkt_uniqueID_recall").getA1Notation() == e.range.getA1Notation() ){
    Logger.log("onEdit - sTkt_uniqueID_recall")

    if(e.value){
      Logger.log("recall ID cell has value - get Data")
      Logger.log(e.value)
      //sTkt_getRecallData() 

    }else{  
      //oE.value should be blank, null or something falsy.. 
      //instead an object is returned?! {old_value: xxxxxx} 
      // and thus is not null and thus it never gets here..
      Logger.log("recall ID cell now empty - clear Data")      
      Logger.log(e.value)
      //sTkt_clearForm()
    }
    Logger.log("onEdit fxnComplete")   
  }
}

Thanks for the help with this.

EDIT: Added logs.

Logs when item is in cell...

[19-10-20 15:17:18:071 PDT] onEdit running
[19-10-20 15:17:18:159 PDT] onEdit - sTkt_uniqueID_recall
[19-10-20 15:17:18:160 PDT] recall ID cell has value - get Data
[19-10-20 15:17:18:160 PDT] 1002
[19-10-20 15:17:18:161 PDT] onEdit fxnComplete 

Logs when item has been deleted -- it takes wrong path b/c of the object with oldValue in it...

[19-10-20 15:15:25:411 PDT] onEdit running
[19-10-20 15:15:25:509 PDT] onEdit - sTkt_uniqueID_recall
[19-10-20 15:15:25:510 PDT] recall ID cell has value - get Data
[19-10-20 15:15:25:511 PDT] {oldValue=1001.0}
[19-10-20 15:15:25:511 PDT] onEdit fxnComplete

EDIT: Providing the full event object shows this clearly. Should have just done that earlier. As you can see old value is in there, but there is an object being returned for "value" instead of a falsy (blank or something else)...

[19-10-20 15:54:19:750 PDT] {authMode=LIMITED, range=Range, source=Spreadsheet, oldValue=10/3/2019--6939, user=xxx@xxxxacupuncture.com, value={oldValue=10/3/2019--6939}}

EDIT: I've tested a work around and this is the one I will likely use as it remind me of exactly what is going on AND Im thinking if the issue gets cleaned up or fixed, this work around should still work and I wouldn't need to make any changes in places it may go in.

if(e.value && typeof e.value !== 'object')

Answer 1

• In your situation, using your shared Spreadsheet, when you delete the value from the cell "C4" of the data validation with the delete button, the event object of e of onEdit(e) has "value":{"oldValue":"deleted value"}.
  • You want to know about this situation.

If my understanding is correct, how about this answer?

When I had tested this, in your situation, I noticed that the border of cell under the simple trigger is related to this situation.

Preparation 1:

For the explanation, it supposes as follows.

1. Create new Spreadsheet.
2. Put a text of sample to the cell "A1".
3. Set a simple trigger of the OnEdit event trigger as the script of function onEdit(e) {Logger.log(JSON.stringify(e))}.
   • At the explanation, e of the event object is used, when the OnEdit event trigger was fired.

Sample situations 1:

Situation 1A:

When the value of sample of the cell "A1" is deleted by the delete button, e of the event object returns the following value.

{"authMode":{},"range":{"columnStart":1,"rowStart":1,"rowEnd":1,"columnEnd":1},"source":{},"user":{"nickname":"","email":""}}

Situation 1B:

When the text of sample in the cell "A1" is deleted by deleting each character using the backspace key, e of the event object returns the following value.

{"authMode":{},"range":{"columnStart":1,"rowStart":1,"rowEnd":1,"columnEnd":1},"source":{},"oldValue":"sample","user":{"nickname":"","email":""},"value":{"oldValue":"sample"}}

Sample situations 2:

Here, in order to replicate your situation, please set the border to the cell "A1".

Situation 2A:

When the value of sample of the cell "A1", which was surrounded by the border, is deleted by the delete button, e of the event object returns the following value.

{"authMode":{},"range":{"columnStart":1,"rowStart":1,"rowEnd":1,"columnEnd":1},"source":{},"oldValue":"sample","user":{"nickname":"","email":""},"value":{"oldValue":"sample"}}

Situation 2B:

When the text of sample in the cell "A1", which was surrounded by the border, is deleted by deleting each character using the backspace key, e of the event object returns the following value.

{"authMode":{},"range":{"columnStart":1,"rowStart":1,"rowEnd":1,"columnEnd":1},"source":{},"oldValue":"sample","user":{"nickname":"","email":""},"value":{"oldValue":"sample"}}

Preparation 2:

For the explanation, it supposes as follows.

1. Create new Spreadsheet.
2. Put a text of sample to the cell "A1".
3. Copy and paste the script of function InstallOnEdit(e) {Logger.log(JSON.stringify(e))}.
   • At the explanation, e of the event object is used, when the OnEdit event trigger was fired.
4. Set the installable OnEdit event trigger to the function of InstallOnEdit.

Sample situations 1:

Situation 1A:

When the value of sample of the cell "A1" is deleted by the delete button, e of the event object returns the following value.

{"authMode":{},"range":{"columnStart":1,"rowStart":1,"rowEnd":1,"columnEnd":1},"source":{},"triggerUid":"###","user":{"nickname":"###","email":"###@gmail.com"}}

Situation 1B:

When the text of sample in the cell "A1" is deleted by deleting each character using the backspace key, e of the event object returns the following value.

{"authMode":{},"range":{"columnStart":1,"rowStart":1,"rowEnd":1,"columnEnd":1},"source":{},"oldValue":"sample","triggerUid":"###","user":{"nickname":"###","email":"###@gmail.com"}}

Sample situations 2:

Here, in order to replicate your situation, please set the border to the cell "A1".

Situation 2A:

When the value of sample of the cell "A1", which was surrounded by the border, is deleted by the delete button, e of the event object returns the following value.

{"authMode":{},"range":{"columnStart":1,"rowStart":1,"rowEnd":1,"columnEnd":1},"source":{},"oldValue":"sample","triggerUid":"###","user":{"nickname":"###","email":"###@gmail.com"}}

Situation 2B:

When the text of sample in the cell "A1", which was surrounded by the border, is deleted by deleting each character using the backspace key, e of the event object returns the following value.

{"authMode":{},"range":{"columnStart":1,"rowStart":1,"rowEnd":1,"columnEnd":1},"source":{},"oldValue":"sample","triggerUid":"###","user":{"nickname":"###","email":"###@gmail.com"}}

Results and discussions:

From above experiment, the following results could be obtained.

1. Values of the event object depend on the situation with and without the border of cell.
   • Also above situation can be seen at not only the border, but also the cases that the background color of the cell, the format (font color, size, bold and so on) except for the default font format.
   • It seems that when the cell and font are changed from the default settings, the event object returns the values of "Sample situations 2".

2. Values of the event object also depend on with and without using the installable event trigger.

3. In the case of the cell with the default cell and font under the simple trigger,

   • When the value of sample of the cell "A1" is deleted by the delete button, e of the event object has no both oldValue and value.
   • When the text of sample in the cell "A1" is deleted by deleting each character using the backspace key, e of the event object has both oldValue and value. And value is {"oldValue":"deleted value"}.

4. In the case of the cell with the border under the simple trigger,

   • When the value of sample of the cell "A1" is deleted by the delete button and also the text of sample in the cell "A1" is deleted by deleting each character using the backspace key, e of the event object has both oldValue and value. And value is {"oldValue":"deleted value"}.

5. In the case of the cell with the default cell and font under the installable trigger,

   • When the value of sample of the cell "A1" is deleted by the delete button, e of the event object has no both oldValue and value.
   • When the text of sample in the cell "A1" is deleted by deleting each character using the backspace key, e of the event object has oldValue and no value. And oldValue is the deleted value which is not the object.
6. In the case of the cell with the border under the installable trigger,
   • When the value of sample of the cell "A1" is deleted by the delete button and also the text of sample in the cell "A1" is deleted by deleting each character using the backspace key, e of the event object has oldValue and no value. And oldValue is the deleted value which is not the object.

From above results, I thought that the values (no both oldValue and value) of event object from the default condition of the cell and font might be a bug or the specification. But I had looked for the official document about this. Unfortunately, I couldn't still find it.

About your situation:

Using above results, when your shared Spreadsheet was tested, the cell "C4" is surrounded by the border. And the simple trigger is used. So the situation is the same with above "Sample situations 2" of "Preparation 1". By this, when the value of cell "C4" is deleted by the delete button, "value":{"oldValue":"deleted value"} is returned.

In this case, how about the following method?

1. When you want to use the simple trigger, I think that the script of the bottom of your question can be used.
2. When you can use the installable OnEdit event trigger, you can know whether the value was deleted by checking with and without value in the event object e.

References:

• Event Objects
• Simple Triggers
• Installable Triggers