How to get old Formula (not oldValue) from onEdit() in Google Sheet Script

Question

Does anyone know if there's a way to access the old (ie, pre-edit) formula, as opposed to oldValue, of the edit event object? I thought e.oldFormula would work, but it's undefined and not even in the documentation.

I'm working on a script to require a password when editing certain ranges but, in order to undo the edit if the user fails to provide the correct password, I need to know what was in the cell before. For cells that contain numbers or strings, e.oldValue works great, but most of the cells I want to protect contain formulas (that's why I want to protect them) so e.oldValue stops the data from updating like it should.

Here's the code I've got that only works if the cell contains no formulas (other than that, it works great:

// Prompt for password before allowing edit of formula cells
function onEdit(e) {
   var ss = SpreadsheetApp.getActiveSpreadsheet();
   var sheet = ss.getActiveSheet();
   var cell = sheet.getActiveCell();
   var editRange = e.range;
   var editCol = editRange.getColumn();


  if (editCol != 4 ) {
    var password = "321"; // not actual password
    var passwordAttempt = Browser.inputBox('Enter Password to edit protected range, or hit cancel to cancel edit:', Browser.Buttons.OK_CANCEL);

    if(passwordAttempt == password) {
      Browser.msgBox('Edit Accepted');
    } else {
      if (passwordAttempt != 'cancel'){
        Browser.msgBox('Incorrect password. Contact James Atkins for password to edit protected ranges.');
      }
      if(e.oldValue){
         Browser.msgBox('old value is defined as ' + e.oldFormula);
         e.range.setValue(e.oldValue);
      } else {
         e.range.clear();
     }
  }

FYI, I'm doing this because the built-in protected ranges in Google Sheets don't distinguish between a user changing the contents of a cell by manually editing it and by activating a script that changes the contents. I want to allow all users to activate scripts that sort and clear certain ranges, but not let them mess with the contents manually.

If it's not there in the official documentation, then it's not there. Make a suggestion to the Google team — Sujay Phadke, Mar 25 '17 at 06:45
If users can activate scripts and edit cells, they can see the script and the password too. Isn't it? — Sangbok Lee, Mar 25 '17 at 07:17
I'm not sure if this is helpful but the Advanced Drive Service gives you access to revisions. Perhaps you can the the old values that way. [Advanced Drive Service](https://developers.google.com/apps-script/advanced/drive) — Cooper, Mar 25 '17 at 15:52
There seems to be a good answer to this question that's four months old, but you do not seem to have responded to it, Hannah. Would you consider replying, accepting, or upvoting it, depending on how it helped you? — halfer, Jul 25 '17 at 08:33

score 0 · Answer 1 · edited May 23 '17 at 12:32

You can solve this problem by creating a clone of your sheet to keep track of your formula. Make the clone sheet off limits to end users. So they cannot edit it.

function onEdit(e) {
   var ss = SpreadsheetApp.getActiveSpreadsheet();
   var sheet = ss.getActiveSheet();  
   var cloneSheet = ss.getSheetByName("cloneSheet")
   var cell = sheet.getActiveCell();
   var editRange = e.range;
   var cloneRange = cloneSheet.getRange(editRange.getA1Notation())
   var editCol = editRange.getColumn();


  if (editCol != 4 && sheet.getName() != "cloneSheet") { 
    var password = "321"; // not actual password
    var passwordAttempt = Browser.inputBox('Enter Password to edit protected range, or hit cancel to cancel edit:', Browser.Buttons.OK_CANCEL);

    if(passwordAttempt == password) {
      Browser.msgBox('Edit Accepted');
      cloneRange.setValue(editRange.getFormula()) //modify the clone to match the new formula. 
    } else {
      if (passwordAttempt != 'cancel'){
        Browser.msgBox('Incorrect password. Contact James Atkins for password to edit protected ranges.');
      }
      if(e.oldValue){
         Browser.msgBox('old value is defined as ' + cloneRange.getFormula());
         e.range.setValue(cloneRange.getFormula());
      } else {
         e.range.clear();
     }
  }
  }
}

However, end user can still see the password. If you really what end user to make only limited changes the below solution is better.

Alternate Solution

When an end user executes a script, even though you have written the script, it runs with scope/identity of the end user. So if they don't have edit permission then the script that runs under their alias also does not have it either. This is in response to your comment:

google sheets don't distinguish between a user changing the contents of a cell by manually editing it and by activating a script that changes the contents.

So how to get around this problem, you can use a google web app to run sort and clear scripts as you. The end user uses a script that makes a call to this script using UrlFetch, this gets around the problem of scopes and prevents them from having a look at your code/password etc.

Here is SO question that addressing a similar problem: Google Apps Script: Temporarily run script as another user

How to get old Formula (not oldValue) from onEdit() in Google Sheet Script

1 Answers1