rootux · September 18, 2017 23:59 · Sep 18, 2017
diff --git a/code.gs b/code.gs
@@ -0,0 +1,71 @@
+/**
+ * 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;
+}