I have a very automation heavy sheet (60+ automated workflows) that is used to fill in a multi-department collaborative form (pdf). From a logic/programming perspective the automation should run correctly, and it does work most of the time, but occasionally simple automations will become invalid. Sometimes it can go days/weeks without the automation breaking and other times it will break multiple times in one day. I am finding that this is occurring more often recently and is becoming a drain on resources to keep resetting the automation.
Each row in the sheet is one instance of the form being filled out and the form consists of ~20 sections (each filled in by a different team). Since the requirements of the form change depending on the results of certain sections I cannot simply request every team to fill out their section and instead I need to automate the form to be filled out sequentially. For example, if in section 2 a field is filled out as "A" then section 5 is skipped, but if it is filled out "B" then section 6 and 10 are skipped.
To achieve this, I have a helper column that states what the current section is. When the section changes, the associated team is notified to fill in their fields. Each section has a check box that becomes checked (via column formula) when all fields from that section are filled in and then the automation records the date in another column when the checkbox is filled in. The helper column has a column formula that changes the section based on the lowest numbered section that does not have the completed date filled in.
This creates a flow as:
A team is notified (via 'request an update' automation) → they fill in their section→ the checkbox becomes checked → the automation fills in the date →the current section updates → Repeat until the form is complete
In order to skip steps that are not required, there is additional automation that will fill out fields in later sections when an early section is filled in a certain way. If a later section has all of its fields filled out this way, then the checkbox will be checked and the date recorded (which will cause it to be skipped when the current section field would hit that step).
The main issue I am having is that the 'record a date' automations keep breaking. Each automation is very simple (when "section 2 complete" changes to "checked" record a date in "Section 2 end"). I think the culprit is that some automated workflows will fill out fields causing multiple sections to "end" simultaneously and then all of those record a date automations try to run at the same time and break.
If anyone has any ideas on correcting this, or a better way to achieve this I would be very grateful. I can elaborate on any aspect of this sheet; I have just dumbed it down a little to shorten an already long request.
