I have a Google Sheet that's connected to a form. The form/sheet is an experiment to see if I can use one form and one sheet to record hours worked for "Same Day" shifts (i.e. The end user clocks in AFTER midnight and clocks out BEFORE midnight on the SAME DAY) vs "Overnight Shifts" (i.e. The end user clocks in BEFORE midnight and clocks out AFTER midnight on the FOLLOWING DAY). The first section of the form is a checkbox for the end user to select which kind of shift they worked, and then it will lead them to one of two, mutually exclusive sections to fill out their hours.
The linked Google Sheet Document contains three Sheets:
- The "Raw Data Sheet" from Form Submissions
- A "Function Sheet" to Calculate Hours Worked for a Same Day Shift
- A "Function Sheet" to Calculate Hours Worked for an Overnight Shift
I've run into an issue where there will be a blank row on one or the other of the "Function Sheets", depending on whether the last submission was for a Same Day or Overnight Shift.
I've found a work-around by using a Filter to only show rows that aren't blank, but the issue I've run into is that it doesn't appear to dynamically update when there's a new Form submission (i.e. the new submission appears in the "Raw Data Sheet" but not either the "Function Sheets" with the Filters), so I have to turn the filter off and back on. This wouldn't be such a pain if there was a simple toggle button, but it seems like the only option to "toggle" the filter is to remove it, and then add it all over from scratch. Sure, it only takes a few seconds, but could become an annoying task to have to repeat over months / years, and could cause confusion among larger teams who share the same document.
I know that one work-around could be to just create a whole separate form for the Overnight Shift, still have it link to the same spreadsheet as its own, new "Raw Data Sheet", and then potentially have a third form to act as a "landing page" that would serve the function of the current first section and direct the end user to either the "Same Day" or "Overnight Form", but that seems like such a clunky solution, when I'm so close with the Filter.
Another potential work-around could be to migrate the form to a third party app like Jotform, which seems to offer more customizability, but also has other draw-backs, such as limited numbers of forms and responses for non-paid accounts.
So ideally, I'd like to find a solution that allows me to use one Google Form and one Google Sheets Doc. It could be a Filter option I haven't thought of yet, or it could be something slightly more advanced like Conditional Formatting or a Function. I have limited experience with those options, but I understand their purpose, so I think I could learn how to use them.