Flow
Placing breaks on a shared sheet
1One pile
Every break from every row, as a start and an end.
2Clash test
Two breaks clash when each starts before the other ends.
3Move forward
Jump past the break you hit and test again.
4Add right away
Put each new break in the pile before placing the next.
5One row at a time
The button waits for each row before the next.
6Gaps in the search
No stop at the end of the shift, and no lock for two users.
The first version of the break filler on the stores' schedule sheet just did math. It took the start of a shift, added set amounts of time for two short breaks and a meal, and wrote them in. It never looked at anyone else. Five people on the same shift got the same five sets of breaks. That's a store with nobody at the counter at lunch.
I rewrote the placement three times that day. Each version fixed what the last one missed.
A break is not a start time
The first patch moved a new break later if its start time matched someone else's exactly. That only stops identical breaks. Next it moved a break if its start was too close to another start. Still wrong. A meal is long, and a short break can start well after a meal starts and still land in the middle of it. Each kind was also only checked against its own kind, so my short break could sit on top of your meal.
Ranges, one pile, jump to the end
What worked: treat every break as a range with a start and an end, and put every kind from every row in one pile. Two ranges clash when each starts before the other ends.
A clash means the new break starts before the other one ends and ends after the other one starts.
If the new break clashes, move it to the end of the one it hit and check again. It only moves forward, so it always stops.
Then add each break to the pile as soon as you place it. Place the first break, add it, place the meal, add it, then the second break. If you don't, a person's meal can land on their own second break.
One button, every row at once
At first the filler ran after any edit. That turned into one button that fills the whole sheet, and the first version of the button sent every row at the same time.
Once the server checked other rows, that was risky. Each request reads the sheet, picks times, and writes. If they all read first, they can all pick the same time. I'm reading that off the code, not off a bad schedule. A later change made the button do one row at a time. Two people clicking at once can still clash, since the server has no lock.
The button now redoes everyone
The server used to skip a row that already had breaks. Then I added a way to force a redo, and the button now forces it on every row. One click throws out every break and places them again, even ones a person fixed by hand.
Breaks after the shift is over
Nothing stops the search at the end of a shift. On a busy sheet a break can land after the person has left, and the clock wraps past midnight, so it looks like a normal morning time.
The clash rule is also stricter than I think I meant. Every break blocks every other break, so only one person is ever off at a time, and a long meal pushes everyone after it.