I have created simple formula in google sheets which is going to be shared with many people. Sheet will be secured except few columns. I want to let those people edit cell's color in those columns. However, I don't want another person to change cell's color if it was already changed. Is there a way to block cell so it won't be allowing color changing? I found such a script:

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 = "Helper" // sheet where the values are copied for later checking

  var firstDataRow = 1; // only take into account edits on or below this row

  var firstDataColumn = 1; // only take into account edits on or to the right of this column

  var ss = SpreadsheetApp.getActiveSpreadsheet();

  var masterSheet = ss.getActiveSheet();

  if (masterSheet.getName() != masterSheetName) return;

  var masterCell = masterSheet.getActiveCell();

  if (masterCell.getRow() < firstDataRow || masterCell.getColumn() < firstDataColumn) return;

  var helperSheet = ss.getSheetByName(helperSheetName);

  var helperCell = helperSheet.getRange(masterCell.getBackgroundColor());

  var newValue = masterCell.getBackgroundColor();

  var oldValue = helperCell.getBackgroundColor();

  var green = "#00FF00"

  var red = "#FF0000"

  if (oldValue == green) {
    masterCell.setBackgroundColor(green);

  } else {
    helperCell.setBackgroundColor(red);
  }
}

But it doesn't work. Any idea?

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Browse other questions tagged or ask your own question.