Writing a Undo function for Google spreadsheets using GAS

Question

The undo() function for Google Apps Script is currently not available in the Table / Sheet / Range classes. There were several problems in "Tracking Problems", I can only find them now (I don’t know what Triaged is): here .

Workarounds were suggested using DriveApp and change history, but I looked around and found nothing (maybe he buried?). In any case, the undo() function is incredibly necessary for many different operations. I could only think of one workaround, but I was not able to get it to work (way to store data, I don't know if this is possible). Here are a few 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 table opens, save all the values ​​as the Script / User property, and refer only to them when certain cell criteria (on) are met. When you want to cancel, get the old data that was saved in the current cell and replace the current cell value with the old data. If the value does not need to be undone, update the saved data to reflect the changes made to the spreadsheet.

Until now, my code has been a bust, and I think because the structure of nested arrays is lost when the object is contracted and saved (for example, it is not parsed correctly). If someone wrote this feature, share it. Otherwise, suggestions for writing this will be helpful.

Change These documents are incredibly static. The number of rows / columns will not change, as well as the location of the data. Implementing the get-all-data / store-all-data-type function for a temporary change history will really satisfy my needs, if possible.

Answer 1

Until you add or remove rows and columns, you can rely on row and column numbers as indexes for the historical 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 checks your data values. Again, in this example, we only need data in one column, so the check is very simple. If you needed to perform different types of checks on different columns, for example, you could switch by 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; } 

Cooperation

This undo function will work for tables that are edited together, although there is a race condition regarding the storage of historical values ​​in the script database. If several users made the first edit in a cell at the same time, the database could consist of several objects representing this cell. With subsequent changes, using query() and choosing to select only the first result ensures that only one of these multiples will be selected.

If this became a problem, it could be solved by including the function in the lock.

Answer 2

I had a similar problem when I needed to protect a sheet, but allow editing through the sidebar. My solution was to have two sheets (one is hidden). If you edit the first sheet, this calls the onEdit procedure and reloads the values ​​from the second sheet. If you open and edit the second sheet, it reloads from the first. It works fine and it’s quite interesting to delete data by mass and monitor the self-repair!

Answer 3

Revised response from a group to allow a range when a user selects multiple cells:

I used what I would call Double Sheets.

 /** * 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 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 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; }