Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- function onEdit() {
- // Re: /docs/forum/AAAABuH1jm0gnrD6_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);
- }
- }
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement