Created
September 18, 2017 23:59
-
-
Save rootux/fc5349134bb43a835606a5842d374b79 to your computer and use it in GitHub Desktop.
Google Apps Script Undo function
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
/** | |
* 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; | |
} |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment
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