Copy Rows Automation Not Firing & Clearing Cell Data

Gordon

Hello,

We are trying to build out a simple employee "check in" sheet that will collect data from all our employees on if they will be in the office, and if so, indicating by a checkbox column. My initial approach was to have a daily, repeating update request automation go out to everyone at 8am every weekday morning (each person has a row on the data sheet), and their response would then be copied to a separate "log" sheet using the copy row automation, with the trigger being the date modified system column changing.

However I'm running into two problems with this approach. First, I'm not aware of a way to "clear" the checkbox automatically every day, which by itself is fine. My second problem is if the person leaves the box checked (from yesterday's update request, because their answer has not changed), then there's no modification, and the automation to copy their response doesn't get triggered.

My first thought was to insert a helper date column with a =TODAY formula, but then I think the automation would be triggered at midnight once the date changes, which is before the person is going to respond for that day.

The update request will go out at 8 am every weekday morning, but I don't want to copy the row until the person submits their update request, even if there's no change.

Thank you in advance for any suggestions!

Kelly Drake

So a couple of things....

I haven't found a way to use a formula to automate a column/cells that also has people manually updating - the manual updates overrride and remove the formula. Also it sounds like its the trigger that is the issue. If the field doesn't change, the trigger won't pull for the automation.

It might be easier to change the checkbox to the Most Recent Date in the Office (or something more succinct) and have them receive the update request with the direction to change to today's date as the Most Recent Date in the Office. Then add a helper checkbox column using a formula like this: =IF([Most Recent Date]@row = TODAY(), 1, 0). This would clear out the helper column when the date isn't today. Then set your automation to trigger when this helper column is checked.

Kelly Drake

So a couple of things....

I haven't found a way to use a formula to automate a column/cells that also has people manually updating - the manual updates overrride and remove the formula. Also it sounds like its the trigger that is the issue. If the field doesn't change, the trigger won't pull for the automation.

It might be easier to change the checkbox to the Most Recent Date in the Office (or something more succinct) and have them receive the update request with the direction to change to today's date as the Most Recent Date in the Office. Then add a helper checkbox column using a formula like this: =IF([Most Recent Date]@row = TODAY(), 1, 0). This would clear out the helper column when the date isn't today. Then set your automation to trigger when this helper column is checked.