Pages

USANA CellSentials

USANA CellSentials
Support Healthy Cellular Function

Saturday, July 25, 2020

Trouble with oldValue in Edit Event Objects

Google Sheets

Simple and Installable triggers in Google Apps Script on sheets is a very useful feature to certain event occurs in a sheet. Events such onChange, onSelectionChange and onEdit instance. On your script if manage properly, it could be a great extension to your user interface. Let's say a user selects a certain cell in sheets, there's a corresponding script that would prompt a user dialog box, where further operations are presented to the user. Or, if a user edit's a value that would trigger another script do some customized function.

oldValue

These triggers are really useful. But, there's one annoying feature which that unexpectedly not available in sheets. Let's say, Cell A1 has a numerical value of 4. Then you edited Cell A1 to change value to 5. In the onEdit(e), if you call the function e.oldValue, you get 4. This is totally fine just how'd you expect it to be. What if from value of 4 in Cell A1, you deleted it. So basically, it is now blank. If you call the range("A1").oldValue. What would you think is the return value?...(blank) or null?...Actually it's undefined.

Does it even makes sense?

It doesn't and that's a problem. We are supposed to capture every value, if they have that oldValue method in the first place. So, is there a solution? Actually there is. But, to implement this, we need to add a mirror tab or sheet of the target sheet being edited. Let's examine the implementation flow. 

It's a very simple solution, by the way. Every Edit, we need to write the same value, to a same range in the mirror tab. But, retrieving oldValue if we are deleting a cell value, we need to retrieve firs the mirror value before writing the new value which is now blank.
Custom fetch oldValue in onEdit(e)
Demo - Let's do above scenario
  1. On "Main" tab and Range("A1"), write the number 4. In doing so, the script write's the same number in "Main_Mirror" tab.
  2. Now, let's change the value in Range("A1") to 5. We have below in the Log. 
    Stackdriver logs New value non-blank
  3. So now, let us delete 5 from Range("A1") to 5. We should expect NEWVALUE as empty or blank(""). While, OLDVALUE is now 5.
Stackdriver logs New value blank
There you have it. It's a simple and quick solution. Please see the actual script below. Comment below and subscribe to my for more Google Apps Script tricks.

The script: