Google Sheet "write once" script issue

[Ashik Alam Leo]

Hello,

I've found this script (below) on the internet. It's for google sheet.

what it does?

when I input a value in a cell, script copy the value to a helper sheet.

so, if I or other user tried to delete or change the value, it returns to previous value, it won't let you change the value.

what is the problem? what I want?

1. The script creates a helper sheet, so, if other user change the value from helper sheet it also changes in main sheet. I don't want that. I tried to lock the helper sheet, then the script won't copy any value from main sheet to helper sheet. I want to lock the helper sheet only editable by me, and also other users value copied to helper sheet.

2. If I or other user copied bunch of cells and paste to main sheet, script copy only the first cell to the helper sheet, so except first cell other cells remain editable.

Hope I've explained properly. Please someone help me on this. Help will be appreciated.

Thanks

Script:

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", "Cash_Note", 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);

  }

}