Writing an 'Undo' Function for Google Spreadsheets Using GAS

Question

Currently there is no undo() function for Google Apps Script in the Spreadsheet/Sheet/Range classes. There were a few issues opened on the Issue Tracker, I can only find one now (I don't know what Triaged means): here.

There have been suggested workarounds using the DriveApp and revision history but I took a look around and didn't find anything (maybe it's buried?). In any case, an undo() function is incredibly necessary for so many different operations. I could only think of one kind of workaround, but I haven't been able to get it to work (the way the data is stored, I don't know if it's even possible). Here is some pseudo -

function onOpen () {
  // Get all values in the sheet(s)
  // Stringify this/each (matrix) using JSON.stringify
  // Store this/each stringified value as a Script or User property (character limits, ignore for now)
}

function onEdit () {
  // Get value of edited cell
  // Compare to some value (restriction, desired value, etc.)
  // If value is not what you want/expected, then:
  // -----> get the stringified value and parse it back into an object (matrix)
  // -----> get the old data of the current cell location (column, row)
  // -----> replace current cell value with the old data
  // -----> notifications, coloring cell, etc, whatever else you want
  // If the value IS what you expected, then:
  // -----> update the 'undoData' by getting all values and re-stringifying them
  //        and storing them as a new Script/User property
}

Basically, when the Spreadsheet is opened store all values as a Script/User property, and only reference them when certain cell criteria(on) are met. When you want to undo, get the old data that was stored at the current cell location, and replace the current cell's value with the old data. If the value doesn't need to be undone, then update the stored data to reflect changes made to the Spreadsheet.

So far my code has been a bust, and I think it's because the nested array structure is lost when the object is stringified and stored (e.g., it doesn't parse correctly). If anyone has written this kind of function, please share. Otherwise, suggestions for how to write this will be helpful.

Edit: These documents are incredibly static. The number of rows/columns will not change, nor will the location of the data. Implementing a get-all-data/store-all-data-type function for temporary revision history will actually suit my needs, if it is possible.

Answer 1

I had a similar problem when I needed to protect the sheet yet allow edits via a sidebar. My solution was to have two sheets (one hidden). If you edit the first sheet, this triggers the onEdit procedure and reloads the values from the second sheet. If you unhide and edit the second sheet, it reloads from the first. Works perfectly, and quite entertaining to delete data on mass and watch it self repair!

Answer 2

As long as you will not add or remove rows and columns, you can rely on the row and column numbers as indices for historic values that you store in ScriptDb.

function onEdit(e) {
  // Exit if outside validation range
  // Column 3 (C) for this example
  var row = e.range.getRow();
  var col = e.range.getColumn();
  if (col !== 3) return;
  if (row <= 1) return; // skip headers

  var db = ScriptDb.getMyDb();

  // Query database for history on this cell
  var dbResult = db.query({type:"undoHistory",
                       row:row,
                       col:col});
  if (dbResult.getSize() > 0) {
    // Found historic value
    var historicObject = dbResult.next();
  }
  else {
    // First change for this cell; seed historic value
    historicObject = db.save({type:"undoHistory",
                              row:row,
                              col:col,
                              value:''});
  }

  // Validate the change.
  if (valueValid(e.value,row,col)) {
    // update script db with this value
    historicObject.value = e.value;
    db.save(historicObject);
  }
  else {
    // undo the change.
    e.range.getSheet()
           .getRange(row,col)
           .setValue(historicObject.value);
  }
}

You need to provide a function that validates your data values. Again, in this example we only care about data in one column, so the validation is very simple. If you needed to perform different types of validation different columns, for instance, then you could switch on the col parameter.

/**
 * Test validity of edited value. Return true if it
 * checks out, false if it doesn't.
 */
function valueValid( value, row, col ) {
  var valid = false;

  // Simple validation rule: must be a number between 1 and 5.
  if (value >= 1 && value <= 5)
    valid = true;

  return valid;
}

Collaboration

This undo function will work for spreadsheets that are edited collaboratively, although there is a race condition around storing of historic values in the script database. If multiple users made a first edit to a cell at the same time, the database could end up with multiple objects representing that cell. On subsequent changes, the use of query() and the choice to pick only the first result ensures that only one of those multiples would be selected.

If this became a problem, it could be resolved by enclosing the function within a Lock.

Answer 3

Revised the answer from the group to allow for range when user selects multiple cells:

I have used what I would call "Dual Sheets".

One sheet acts as a backup / master and the other as the active sheet

/**
 * Test function for onEdit. Passes an event object to simulate an edit to
 * a cell in a spreadsheet.
 * Check for updates: https://stackoverflow.com/a/16089067/1677912
 */
function test_onEdit() {
  onEdit({
    user : Session.getActiveUser().getEmail(),
    source : SpreadsheetApp.getActiveSpreadsheet(),
    range : SpreadsheetApp.getActiveSpreadsheet().getActiveCell(),
    value : SpreadsheetApp.getActiveSpreadsheet().getActiveCell().getValue(),
    authMode : "LIMITED"
  });
}


function onEdit() {
  // This script prevents cells from being updated. When a user edits a cell on the master sheet,
  // it is checked against the same cell on a helper sheet. If the value on the helper sheet is
  // empty, the new value is stored on both sheets.
  // If the value on the helper sheet is not empty, it is copied to the cell on the master sheet,
  // effectively undoing the change.
  // The exception is that the first few rows and the first few columns can be left free to edit by
  // changing the firstDataRow and firstDataColumn variables below to greater than 1.
  // To create the helper sheet, go to the master sheet and click the arrow in the sheet's tab at
  // the tab bar at the bottom of the browser window and choose Duplicate, then rename the new sheet
  // to Helper.
  // To change a value that was entered previously, empty the corresponding cell on the helper sheet,
  // then edit the cell on the master sheet.
  // You can hide the helper sheet by clicking the arrow in the sheet's tab at the tab bar at the
  // bottom of the browser window and choosing Hide Sheet from the pop-up menu, and when necessary,
  // unhide it by choosing View > Hidden sheets > Helper.
  // See https://productforums.google.com/d/topic/docs/gnrD6_XtZT0/discussion
  
  // modify these variables per your requirements
  var masterSheetName = "Master" // sheet where the cells are protected from updates
  var helperSheetName = "Helper" // sheet where the values are copied for later checking
  
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var masterSheet = ss.getActiveSheet();
  if (masterSheet.getName() != masterSheetName) return;
  
  var masterRange = masterSheet.getActiveRange();
  
  var helperSheet = ss.getSheetByName(helperSheetName);
  var helperRange = helperSheet.getRange(masterRange.getA1Notation());
  var newValue = masterRange.getValues();
  var oldValue = helperRange.getValues();
  Logger.log("newValue " + newValue);
  Logger.log("oldValue " + oldValue);
      Logger.log(typeof(oldValue));
  if (oldValue == "" || isEmptyArrays(oldValue)) {
    helperRange.setValues(newValue);
  } else {
    Logger.log(oldValue);
    masterRange.setValues(oldValue);
    
  }
}

// In case the user pasted multiple cells this will be checked
function isEmptyArrays(oldValues) {
  if(oldValues.constructor === Array && oldValues.length > 0) {
    for(var i=0;i<oldValues.length;i++) {
      if(oldValues[i].length > 0 && (oldValues[i][0] != "")) {
          return false; 
      }
    }
  }
  return true;
}