Using "Modified" to Trigger Automation

aschneiderheinze1025

In my sheet, I have a column labeled [Status] that indicates the current stage of development for a program. The values can be Stage 1, Stage 2, Stage 3, Active, On Hold, or Archived.

I also have two columns labeled [Stage 1 (Decision)] and [Stage 2 (Decision)]. These are used to capture the decisions by management at the end of each stage. Values would be "Approved", "Request Revisions", "Not Approved".

The goal is for [Status] to update automatically based on the values in these columns. For example, if [Stage 1 (Decision)] is "Approved", [Status} should change from "Stage 1" to "Stage 2". If the [Stage 1 (Decision)] is "Not Approved", [Status} should change from "Stage 1" to "Archived Idea". Same logic for [Stage 2 (Decision)].

Updates to [Stage 1 (Decision)] and [Stage 2 (Decision)] occur via a form on another sheet. In [Stage 1 (Decision)] and [Stage 2 (Decision)], I have cross-reference sheet formulas that look in the other sheet to see if a form was submitted that includes a decision for either column, matching the [Program Code] in both sheets.

Since changes to [Status} were not happening, I created a column [H-Status] that takes the current value of [Stage 2 (Decision)] first, then if that is blank, it takes the value of [Stage 1 (Decision)]. If both are blank, [H-Status] is blank. Then I have another helper column [H-Status (Update)] with a checkbox that compares [H-Status] with [Status}, and if different, the box is checked.

I created an automatic workflow that is supposed to run when the row is modified and when [H-Status (Update)] is checked to change the value of [Status] based on the value in [Stage 1 (Decision)] or [Stage 2 (Decision)]. I did this because [Status] shouldn't say "Revised Required" but remain the current stage.

Anyway, the challenge I have is that nothing is actually triggered to run the automation. If I click "Run" in the animation screen, the updates to [Status] occur as they should. However, I keep checking over time, and [Status] is not updating, even if I go through the process of submitting approvals (on my source sheet) and I see either [Stage 1 (Decision)] or [Stage 2 (Decision)] updating, the [H-Status] and [H-Status (Update)] columns updating, but I don't see [Status] updating.

I tested manually changing another column (outside the list of columns I included in this post), saved the sheet… and within seconds I saw a notification that I should refresh the sheet. I did, and [Status] updated correctly.

Is [Modified] a good column to use to trigger an automation when nothing is manually triggering the automation in the sheet? Is there a simpler way to do this?

Kerry St. Thomas

Since you have a bit of a circular reference, it can be kind of complex to solve. It's a little convoluted, but it makes sense depending on what you're after.

I could be going out on a limb here. But I think that the problem could very well lie in HOW formulas update. Since it sounds like both your sheets have formulas that ultimately drive this Status update, you need to wait until the formulas update, AT WHICH POINT you can do a final comparison and then there's another formula that needs to parse before your status can show. For a formula to calculate, the sheet has to be activated somehow: the sheet is opened(or a sheet with cross-sheet formulas is opened), new rows are added or deleted, or certain other changes tied to workflows or automations.

My personal solutions whenever I need a formula to recalculate is to use what I call a "Date Intelligence" sheet. I use a Record A Date automation on that sheet, which triggers every day at super early in the morning. (I use the sheet for today, the first of the month, the first of the quarter, and a few other things - so I can use it as a reference for many projects.) I then add a field to the Sheet Summary as a cross-sheet reference to the daily automation.

The limitation is that this isn't an immediate update; it will be time-limited in that it'll only trigger once per day and not immediately on trigger. Ultimately, my workaround is to FORCE a change to the sheet that's not cached and waits until there's some kind of human intervention. So it's not necessarily going to be the solution to your issue, but might send you down the path of exploration. Good luck!