I’ve been managing an app that does almost exactly this for our COVID testing sites & staff for our healthcare system. Users with scheduling permissions can create shifts at multiple locations and specify the roles and quantity needed for each (a drive-tru might need 6 testers, 2 couriers, etc while a mobile site’s needs vary). There’s also a separate Staff app where they can see their assignments and input their availability a week at a time. Schedulers can see this availability when assigning staff as well as any conflicts and total hours assigned so as not to exceed their pay scale. Users are also filtered based on teams and regions so schedulers can prioritize staff that work in their region first but can look outside that region when necessary.
It’s quite complex and requires a lot of custom javascript running in the Sheet. The biggest challenges have been identifying staffing conflicts and copying multiple rows at a time. Since staff can be assigned to multiple shifts per day and there’s potential for shifts to overlap it gets very difficult to find conflicts without running code.The ability to filter relations in the data view would help a lot. Additionally creating the shifts manually every day got very tedious for our schedulers so I implemented a schedule recurring event feature. They first create a Staffing Model that lists the type and quantity of staff needed (6 swabbers, 2 couriers, 1 shift leader), then submit a new recurring shift form that captures a date range and week days on which to repeat that shift. A script then creates all the individual shifts in that range and all the ‘shift requirement’ entries based on the staffing model. The reason I copy all these requirements individually is because they sometimes change the requirements for one individual shift without wanting to change the entire series.
I haven’t made a template out of this since it’s fairly specific to COVID in a couple places but could probably be done without too much effort. I’d probably want to wait until it could be done with less custom code though.
Here’s a copy of the staff orientation guide with screenshots: