google sheets
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.
Since you consider yourself and advanced user I'm not going into many details and I'll assume that you know how to copy cells and what's the use of $.
Lets say that we have your example as is in the spreadsheet starting from cell A1. So the first absence of John D. is marked in cell B2.
Following the steps you'll end up in another sheet in your spreadsheet that will have the row with your desired columns.
inputreport, absent, lateabsent in cell B3 paste that and copy it accordingly to the right and bottom: =IF(input!B2 = "a"; CONCATENATE(input!$A2; ", "); "")
absent in cell B2 paste that and copy it accordingly to the right: =CONCATENATE(B3:B100)
Do exactly the same steps for the late sheet by replacing the absence to late and "a" to "l"
Finally, under the report sheet in cell B2 paste that and copy it to the right:
=CONCATENATE(IF(absent!B2<>""; CONCATENATE("Absent: "; absent!B2); ""); IF(late!B2<>""; CONCATENATE("Late: "; late!B2); ""))
The above example scales very well if in the future you would like to add more filters like sick, vacations, etc. You can check the final result in action where I also have another way of representing the report.
I know this is an old topic, but for the number of unique articles, this might work:
=ArrayFormula(counta(unique(filter(A2:A&B2:B, len(A2:A&B2:B)))))
I also tried to work out some formulas for the other questions:
Hope this helps ?
Best Answer
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 !!