Protect row according to the data in cell (Yes/No)

Sanli

I have a sheet with a table. When me or one of my editors changes the data of the cell in column F (so that it is not blank), I need the row to become protected for everybody except me (owner).

enter image description here

I have seen several similar questions here, but no one gives a working script... I will appreciate any help.

Tanaike

I believe your current situation and your goal as follows.

  • Your Spreadsheet is shared with some users.
  • When the user is edited the dropdown list of the column "F", when the value is not empty, you want to protect the row of columns "A" to "F".
  • After the row was protected, you want to edit by only the owner.
  • You want to achieve this using Google Apps Script.

In this case, I would like to propose to run the script with the installable OnEdit trigger. The sample script is as follows.

Sample script:

Please copy and paste the following script to the script editor of Spreadsheet and please install the OnEdit trigger to the function of myFunction.

function myFunction(e) {
  const sheetName = "Sheet1"; // Please set your sheet name.

  const range = e.range;
  const sheet = range.getSheet();
  const value = range.getValue();
  const row = range.getRow();
  if (sheet.getSheetName() != sheetName || range.getColumn() != 6 || row == 1 || value == "") return;
  const p = sheet.getRange(`A${row}:F${row}`).protect();
  const owner = Session.getActiveUser().getEmail();
  p.getEditors().forEach(f => {
    const email = f.getEmail();
    if (email != owner) p.removeEditor(email);
  });
}
  • In this script, it supposes that the dropdown list is put to the cells "F2:F" from your sample image. If you want to change the range, please modify above script.
  • When you use this script, by an user who is not owner, please edit the dropdown list of the column "F" of "Sheet1". By this, when the value of dropdown list is not empty, the script works. And, the row is protected. The editor is only the owner.

References:

Added:

About your following 2nd question in comment,

thank you very much!! It works, and I know now something about triggers - a new thing for me. Is it possible to extend this function for 3 sheets with the same structure (within one spreadsheet)? Sheet1, Sheet2,Sheet 3...

When you want to use above script for the specific sheets like "Sheet1", "Sheet2", "Sheet3", how about the following sample script?

Sample script:

function myFunction(e) {
  const sheetNames = ["Sheet1", "Sheet2", "Sheet3"]; // Please set the sheet names you want to run the script.

  const range = e.range;
  const sheet = range.getSheet();
  const value = range.getValue();
  const row = range.getRow();
  if (!sheetNames.includes(sheet.getSheetName()) || range.getColumn() != 6 || row == 1 || value == "") return;
  const p = sheet.getRange(`A${row}:F${row}`).protect();
  const owner = Session.getActiveUser().getEmail();
  p.getEditors().forEach(f => {
    const email = f.getEmail();
    if (email != owner) p.removeEditor(email);
  });
}

Collected from the Internet

Please contact [email protected] to delete if infringement.

edited at
0

Comments

0 comments
Login to comment

Related

From Dev

Protect row according to the data in cell (Yes/No)

From Dev

Set row height of a UITableView according to the cell in that row

From Dev

Repeat a row according to the specific cell value in SQL

From Dev

Create a custom cell according to the type of data retrieved

From Dev

Exception for the "instance (Num a) => YesNo a where" code row

From Dev

Increase the main tableview row height according to the custom cell

From Dev

Skip excel macro in specific row according to one cell

From Dev

Merging two data frames according to row values

From Dev

Resetting Row number according to record data change

From Dev

R subsetting data according to non zeros in row

From Dev

UIPICKERVIEW with different data to be displayed according to the row selected

From Dev

Get row by row data according to some condition in oracle

From Dev

Verify data from a HTML cell and display a message according to an interval

From Dev

Validate entered cell value according to a given data type

From Dev

How to Auto-size cell in datagridview according to data size

From Dev

Fetch a cell data by clicking another cell data from same row

From Dev

get single row data into multiple row by calculating cell data

From Dev

Sending row/cell data on click to the function - ReactJS

From Dev

inserting text in last cell after row of data

From Dev

Split cell into rows, but with respect to data in original row

From Dev

If cell in column has data then copy and paste to a different cell in same row

From Dev

Get Cell according to its number in Board data structure Cell[][] without passing 2 FOR loops

From Dev

Remove rows from a data frame according to a range of row names

From Dev

R removing data frame row according to a vector value

From Dev

Knockout js data grid formatting according to row values

From Dev

How to add the data in the same row but in diffrent columns according to the hotel names

From Dev

How to place values inside table cell according to combination of table header and first row value?

From Dev

Grid view Row Data Bound putting data in a particular cell

From Dev

How to have excel paste a value in a specific cell according to another data table

Related Related

  1. 1

    Protect row according to the data in cell (Yes/No)

  2. 2

    Set row height of a UITableView according to the cell in that row

  3. 3

    Repeat a row according to the specific cell value in SQL

  4. 4

    Create a custom cell according to the type of data retrieved

  5. 5

    Exception for the "instance (Num a) => YesNo a where" code row

  6. 6

    Increase the main tableview row height according to the custom cell

  7. 7

    Skip excel macro in specific row according to one cell

  8. 8

    Merging two data frames according to row values

  9. 9

    Resetting Row number according to record data change

  10. 10

    R subsetting data according to non zeros in row

  11. 11

    UIPICKERVIEW with different data to be displayed according to the row selected

  12. 12

    Get row by row data according to some condition in oracle

  13. 13

    Verify data from a HTML cell and display a message according to an interval

  14. 14

    Validate entered cell value according to a given data type

  15. 15

    How to Auto-size cell in datagridview according to data size

  16. 16

    Fetch a cell data by clicking another cell data from same row

  17. 17

    get single row data into multiple row by calculating cell data

  18. 18

    Sending row/cell data on click to the function - ReactJS

  19. 19

    inserting text in last cell after row of data

  20. 20

    Split cell into rows, but with respect to data in original row

  21. 21

    If cell in column has data then copy and paste to a different cell in same row

  22. 22

    Get Cell according to its number in Board data structure Cell[][] without passing 2 FOR loops

  23. 23

    Remove rows from a data frame according to a range of row names

  24. 24

    R removing data frame row according to a vector value

  25. 25

    Knockout js data grid formatting according to row values

  26. 26

    How to add the data in the same row but in diffrent columns according to the hotel names

  27. 27

    How to place values inside table cell according to combination of table header and first row value?

  28. 28

    Grid view Row Data Bound putting data in a particular cell

  29. 29

    How to have excel paste a value in a specific cell according to another data table

HotTag

Archive