I have a Google Sheet that I gave editor permissions for a few people that can edit the data. inside this sheet, I have a column (column B, WEEKDAY) that I want to display as a read-only value according to the value from another column (column A, holding a date value) of the same row. for example if value of column A is Feb 14th, 2022 then automatically I see value of "Monday" on column B. in order to prevent mistakes by my editors, I set column B as read-only using the protected range feature. but, my problem - once I set this protection, they can't copy the entire row to another row, or delete the entire row, as column B is protected. What is the correct way to solve my need? using Apps script is possible as well for me. meanwhile, I changed the protected range from error to warning as I don't have a better solution for now.

I am using ARRAYFORMULA in cell B1, but users can ignore it and manually override the value of B7 (as an example) instead of the formula value. if they do such manual override they get an error on B1 "Array result was not expanded because it would overwrite data in B7.". this is the reason I was looking to set column B as read-only so users can not manually override the formula value.

CodePudding user response：

Issue:

• You are using an ARRAYFORMULA on B1 which populates several cells in column B.
• You want users to be able to copy and delete rows.
• You want users to be unable to edit values in column B (since that would mess with the ARRAYFORMULA).

Solution:

Use an onEdit trigger which does the following every time a user edit a spreadsheet cell:

• Check whether the user has permission to edit column B, using Session.getActiveUser() (the list of users who have permissions is defined in the script itself, and it may well be empty).
• If the user has permission, check whether the edited cell is in the protected range (B2:B), using the event object.
• If the edited cell is in that range, remove any values in that range, using Range.clearContent().

Regarding the cell B1, which contains the formula, I'd protect it conventionally via Sheets editor (ref).

Code sample:

const EDITORS = ["[email protected]"]; // Users who can edit column B
const SHEET_NAME = "Sheet1"; // Change accordingly
const PROTECTED_COL = 2; // Column B
const FIRST_ROW = 2; // Header row is protected conventionally

function onEdit(e) {
  const userEmail = Session.getActiveUser().getEmail();
  if (!EDITORS.includes(userEmail)) {
    const range = e.range;
    const sheet = range.getSheet();
    if (sheet.getName() === SHEET_NAME && range.getColumn() === PROTECTED_COL && range.getRow() >= FIRST_ROW) {
      sheet.getRange(FIRST_ROW, PROTECTED_COL, sheet.getLastRow()-FIRST_ROW 1).clearContent();
    }
  }
}

Note:

• Make sure you modify EDITORS and SHEET_NAME according to your preferences.