Skip to content

Instantly share code, notes, and snippets.

@8bu
Last active August 9, 2018 07:44
Show Gist options
  • Save 8bu/cf0fa85bfd0299c849b5777df3250131 to your computer and use it in GitHub Desktop.
Save 8bu/cf0fa85bfd0299c849b5777df3250131 to your computer and use it in GitHub Desktop.
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