Lock a row when cell is checked in Google Sheets

Question

I have a Google Sheets spreadsheet with a group of friends where each person enters a bet on their own designated tab. After they finish editing columns A-E, they have to check the box in column F to "submit" their bet. Once the box is checked, I want to lock that row to prevent someone from altering a bet. I would also like to change the background color in columns A-F from orange to gray to signify a submission.

https://docs.google.com/spreadsheets/d/12M1F69a_DgEW3yU154Bm6kVpi5dZ1tdJksvKzNBgJek/edit?usp=sharing

Answer 1

I haven't checked if it's possible to lock rows this way. But, just in case, as another options or a possible workaround I'd propose to copy the 'locked' values on a locked or/and hidden sheet and compare the values every time when spreadsheet is modified. The script could compare the sheets and copy the 'locked' (submitted) values from the locked sheet back on user's sheet if there are differences.

Probably, as an extremely vandal proof solution you can store the bets as script properties:

https://developers.google.com/apps-script/guides/properties

Update

It's turned out that you can't restrict current user from editing any cell on the sheet: How to lock a sheet for current user with Google script

Neither the owner of the spreadsheet nor the current user can be removed.

There is the workaround with additional 'helper' sheets: https://support.google.com/docs/forum/AAAABuH1jm0gnrD6_XtZT0/?hl=en&gpf=%23!topic%2Fdocs%2FgnrD6_XtZT0

Just for educational purposes, here is my try. It changes backgrounds by click on the checkboxes (to gray and back to orange) but it doesn't protect the cells:

function onEdit(e) {
  const col = e.range.columnStart;
  // e.source.toast(col);  // <-- to debug
  if (col != 6) return;

  const row = e.range.rowStart;
  // e.source.toast(row);

  const sheet = e.source.getActiveSheet();
  const range = sheet.getRange(row, 1, 1, 6);
  // e.source.toast(range.getValues());

  const value = e.range.getValue();
  // e.source.toast(value);

  if (!value) {
    // set orange backgrounds    <-- it works fine
    range.setBackgrounds([range.getBackgrounds()[0].map(x => x = '#fce5cd')]);
    return;
  }

  // set gray backgrounds    <-- it works fine
  range.setBackgrounds([range.getBackgrounds()[0].map(x => x = '#cccccc')]);

  const protection = range.protect();
  protection.removeEditors(protection.getEditors()); // <-- it doesn't work
  
  const user = Session.getEffectiveUser();
  // e.source.toast(user);
  protection.removeEditors(user); // <-- it doesn't work as well
 
}

Answer 2

There is a workaround - you need to register the script as installable trigger: https://developers.google.com/apps-script/guides/triggers/installable

It helps when users (not the owner) need to be restricted.

One hint: if you register the function as installable triger and the function is e.g. onEdit, and the installable trigger will be set as onEdit, then the function will fire twice. In this case name your funtion e.g. "myfunction" and it will be fired once.