GAS - Checking edited cell

0
votes

Here's my spreadsheet

enter image description here

Here's what I'm trying to do.

When a user enters any data from row 4 and below, onEdit() function checks the top row to see if Session.getActiveUser(); matches "[email protected]".

*Col 1,2 and 3 at cell range A:C can only be edited by email of A1.

*Col 1,2, and 3 at cell range D:F can only be edited by email of D1.

This is only what I've done so far to check user's edited cell..

function onEdit(e) {
  var editedSheet = e.source.getActiveSheet();
  var editedRange = e.source.getActiveRange();

  // clear edited value if email does not match
  if (Session.getActiveUser() != range.getCell(1,1).getValue()) {
     editedRange.setNote(""); // clear value
  }
}

The problem is that it will be very tedious to check each cell in A1, D1 and so on, since I have several sheets in the spreadsheet.

The other sheets might not necessarily start on column 'A' either. The cell structure might start on column 'B'.

1
google-apps-scriptgoogle-sheets
Just to correct current code, setNote("") won't clear the cell's value. You will have to range clear() function to clear range values. You can refer this spreadsheet documentation. – rpm
Does the user edit just one cell? Or will the user paste in multiple cells? – Alan Wells
The user should be able to edit cells from col 1 to 3, if its email matches the top row.. – chopz
You can do one thing, put onOpen() trigger along with onEdit() trigger and check if activeUser is same as in A1 or D1 (as per your condition). If it is not same, don't allow that user to edit cells' value. – rpm

1 Answers

0
votes

OnEdit isn't very reliable for this type of action, consider instead, an alternative method to restrict user input, you can use a sideBar, a GoogleForm, a published HTML (doGet), another sheet, etc.

With onEdit you will end up with various bugs if the user copy and paste, edits lots os cells very rapidly, goes off-line then on-line, fi there's too much calls, etc.