Skip to content

Instantly share code, notes, and snippets.

@rootux
Created September 18, 2017 23:59
Show Gist options
  • Save rootux/fc5349134bb43a835606a5842d374b79 to your computer and use it in GitHub Desktop.
Save rootux/fc5349134bb43a835606a5842d374b79 to your computer and use it in GitHub Desktop.
Google Apps Script Undo function
/**
* 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 = "Backup" // 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;
}
@delibelly
Copy link

Hi,
Just can across your script and i am wondering how to get this to work with formulas? I have a Google spreadsheet which is formula heavy and is shared with staff. I don't want them to change cells that have formula in. Thank You

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment