Last active
August 9, 2018 07:44
-
-
Save 8bu/cf0fa85bfd0299c849b5777df3250131 to your computer and use it in GitHub Desktop.
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
function onEdit() { | |
// Re: https://productforums.google.com/d/topic/docs/gnrD6_XtZT0/discussion | |
// | |
// This script prevents cells from being updated. When a user edits a cell on any sheet, | |
// it is checked against the same cell on a helper sheet, and: | |
// | |
// - 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 back to the cell on | |
// the source sheet, undoing the change | |
// | |
// This in effect protects sheets in a "write once" manner, albeit with lots of limitations. | |
// The script does *not* provide protection against edits by a determined user. | |
// For example, selecting all cells and pressing Delete lets any user erase many cells at once. | |
// The values that were deleted from the cells will however be preserved on the helper sheet | |
// and will reappear when an individual cell is edited again. | |
// | |
// Helper sheets are created automatically when an edit is first made, one helper sheet | |
// per source sheet. For a source sheet named "Sheet1", the helper sheet is "Sheet1_helper". | |
// Helper sheets are automatically hidden when created to not clutter the display, but | |
// they can be uhidden by any user with "can edit" rights to the spreadsheet. | |
// Users with edit rights can also disable this script at will. | |
// | |
// To change a value that was entered previously, empty the corresponding cell on the helper sheet, | |
// then edit the cell on the source sheet. | |
// When you rename a source sheet, remember to rename the helper sheet as well. | |
// Choose "View > Hidden sheets" to show the helper sheet, then rename it using the pop-up | |
// menu at the sheet's tab at the tab bar at bottom of the browser window. | |
// | |
// To take this script into use: | |
// | |
// - take a backup of your spreadsheet through File > Make a copy | |
// - select all the text in this script, starting at the "function onEdit()" line and ending at the last "}" | |
// - copy the script to the clipboard with Control+C | |
// - open the spreadsheet where you want to use the function | |
// - choose Tools > Script editor > Blank (this opens a new tab in the browser) | |
// - press Control+A followed by Control+V to paste the script in | |
// - press Control+S to save the script | |
// - close the script editor tab and go back to the spreadsheet tab | |
// | |
// The script will from then on watch updates on all the sheets and only allow edits | |
// when the cell is empty to start with. | |
// | |
// Note that the script only protects _values_ rather than _formulas_. | |
// To protect formulas, use Data > Named and protected ranges. | |
// | |
// If your sheets that you would like to protect already have data on them, create helper | |
// sheets manually by choosing the Duplicate command from the sheet's tab menu at the tab bar | |
// at the bottom of the browser window. Rename the new sheet so that "Copy of Sheet1" becomes | |
// "Sheet1_helper". | |
// | |
// The range where edits are of this "write once" type can be limited by changing the values | |
// assigned to the firstDataRow, lastDataRow, firstDataColumn and lastDataColumn variables below. | |
// The range defined by these values is global and will apply to all the sheets the same. | |
// | |
// You can exclude some sheets from being watched by putting them on the freeToEditSheetNames | |
// list. See below for more info. | |
// modify these variables per your requirements | |
// define the range where edits are "write once" | |
// to watch only the range A1:D100, define rows as 1,100 and columns as 1,4 | |
// to watch only the range M20:V30, define rows as 20,30 and columns as 13,22 | |
var firstDataRow = 1; // only take into account edits on or below this row | |
var lastDataRow = 999; // only take into account edits on or above this row | |
var firstDataColumn = 1; // only take into account edits on or to the right of this column | |
var lastDataColumn = 999; // only take into account edits on or to the left of this column | |
// naming pattern for sheets where values are copied for later checking | |
var helperSheetNameSuffix = "_helper"; | |
// sheets that are free to edit with no protection | |
var freeToEditSheetNames = ["Free to edit 1", "Free to edit 2", helperSheetNameSuffix + "$"]; | |
// You can use regular expressions in sheet names. The match is not case-sensitive, | |
// so "free.*edit" will match "free to edit", "Free Editing for Everyone", | |
// "Sheet (free to edit)" and "Free edit playground". | |
// Leave the last entry, helperSheetNameSuffix + "$", as it is to ensure that changes to a | |
// helper sheet do not trigger the creation of another _helper_helper sheet. | |
// See these sites for more info: | |
// - http://en.wikipedia.org/wiki/Regular_expression | |
// - https://developer.mozilla.org/en-US/docs/Web/JavaScript/Guide/Regular_Expressions | |
var ss = SpreadsheetApp.getActiveSpreadsheet(); | |
var masterSheet = ss.getActiveSheet(); | |
var masterSheetName = masterSheet.getName(); | |
var masterCell = masterSheet.getActiveCell(); | |
var sheetNameRegExp; | |
// do not record edits on free to edit sheets | |
for (var sheet in freeToEditSheetNames) { | |
sheetNameRegExp = new RegExp(freeToEditSheetNames[sheet], "i"); | |
if (sheetNameRegExp.test(masterSheetName)) return; | |
} | |
// find helper sheet | |
var helperSheetName = masterSheetName + helperSheetNameSuffix; | |
var helperSheet = ss.getSheetByName(helperSheetName); | |
if (helperSheet == null) { // helper sheet does not exist yet, create it as the last sheet in the spreadsheet | |
helperSheet = ss.insertSheet(helperSheetName, ss.getNumSheets()); | |
Utilities.sleep(2000); // give time for the new sheet to render before going back | |
ss.setActiveSheet(masterSheet); | |
helperSheet.hideSheet(); | |
ss.setActiveRange(masterCell); | |
} | |
if (masterCell.getRow() < firstDataRow || masterCell.getColumn() < firstDataColumn || | |
masterCell.getRow() > lastDataRow || masterCell.getColumn() > lastDataColumn) return; | |
var helperCell = helperSheet.getRange(masterCell.getA1Notation()); | |
var newValue = masterCell.getValue(); | |
var oldValue = helperCell.getValue(); | |
if (oldValue == "") { | |
helperCell.setValue(newValue); | |
} else { | |
masterCell.setValue(oldValue); | |
} | |
} |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment