4

We often have the case of two people overwriting each other's changes by writing on the same cell at the same time.

Is there a way to make it so that cells can't be written by more than one person at a time?

For instance, by having them locked for other editors when they are selected by a user.

  • 3
    Different users are normally differently highlighted by a color. Isn't that enough for a user to notice somebody else is active on the same cell? – Jacob Jan Tuinstra May 6 '14 at 11:15
  • 1
    Apparently not, as the problem keep occurring :) – Timst May 6 '14 at 15:32
  • I mean two people selecting the same cell, one writing SUM, and the other writing COUNT almost simultaneously, the result being "COUNT". What you describe can't happen on Spreadsheet (it can happen on Documents though). It would be nice if once a cell is selected, it was locked for the user that selected it. – Timst May 6 '14 at 23:02
  • (Not really a workaround, but...) Maybe the users can be advised to "copy" the cells contents before submitting, so their hard work is not lost if they lose the concurrent editing battle? – MrWhite May 7 '14 at 1:23
  • 1
    Erf. I'd rather we focus on the core issue than on a imaginary use case, but if you really insist, the real situation is that we (a non-profit) are using the spreadsheet as a sign up list. Several volunteers sell tickets and input the ID number of the guests in a column, and sometimes, considering that up to 4 persons write at the same time on a rapid succession, two volunteers will select the same cell and overwrite each other without realizing it, resulting in a guest not being registered. Hence the question: is there a way to lock a specific cell when a user is already editing it? – Timst May 7 '14 at 8:02
3

It is not possible to programmatically alter the protection state of a range/cell in a Google Spreadsheet. I can imagine, to protect the cell after edit (via onEdit) for lets say 1 second and it being made accessible right after.

Star this issue on the Google Apps Script issue tracker: 1721 (I just did...).
The more people staring it, the more likely it will be implemented !!

  • Thanks, that could work. I starred it too, let's see if this eventually happen. – Timst May 7 '14 at 14:52
2

Have the people inputting data use Google Forms instead. This way they will not overwrite each other. The form can submit to the sheet into a single Worksheet called 'Form Responses' by default. Create another worksheet and use the "Query" function to query and sort and clean the data as you desire. For example... Create a new worksheet in the sheet you are capturing responses in. select the A1 cell. Use something like =Query('Form Responses'!A:L,"select * where A <> '' order by A desc"). You can also create pivots and such but you probably already know that.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.