Automation actions across multiple sheets

Kevin M.

Hi all,

I have a client who prepares Smartsheet instances for clients of his own (I'll call them customers) in one very particular use-case. In an nutshell:

The Cast:

• Sheet A, the core sheet
  • Only my client's team can admin.
  • New rows come into this sheet via form submitted by customer.
  • Let's assume we have only three fields, Form Field 1, Form Field 2, Form Field 3, and Calculation Field 0
  • Client needs to protect his process and doesn't want Calculation Field 0 shown to client
• Sheet X, the user sheet
  • Customers can view and edit unlocked columns.
  • Displays Form Field 1 and Form Field 3 from Sheet A
    • These are populated via INDEX/MATCH from corresponding columns on Sheet A.
    • Form Field 2 is not needed for share with the client.

The Problem:

My client's customers will need to have the ability to edit the data that they can see, but they can't be allowed any access to the sheet that contains the data.

The Solution:

Helper Columns

• I've set up each sheet with two new columns, "Update" (Checkbox 1) and "Clear Update Checkbox" (Checkbox 2).
  • On Sheet X, Checkbox 1 is editable, Checkbox 2 is INDEX/MATCH-ed with Sheet A
  • On Sheet A, Checkbox 1 is INDEX/MATCH-ed with Sheet X and Checkbox 2 is editable.

Automated Workflows

• Sheet A
  • IF Checkbox 1 changes to Checked, THEN send Status Update Request to contact in Field 1
    • Allows customer to make needed updates without sheet access.
  • AND change value of Checkbox 2 to Checked.
    • Trigger for Sheet X to clear the check from Checkbox 1.
• Sheet X
  • IF Checkbox 2 changes to Checked, THEN change value of Checkbox 1 to Unchecked
    • Checkbox trigger is ready to be used again.
• Sheet A
  • IF Checkbox 1 changes to Unchecked, THEN change value of Checkbox 2 to Unchecked.
    • Readies Sheet A for next trigger even on that row.

This series of back-and-forth automations works, but not reliably so; around 60% of the time.

Does anyone see a better way to achieve the same result?

-K

(edit: formatting)

Andrée Starå

Hi @Kevin M.

I hope you're well and safe!

Have you explored using one of the Premium Apps, Dynamic View or WorkApps?

Is that an option?

I hope that helps!

Be safe, and have a fantastic week!

Best,

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

✅Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up, Awesome, or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

Kevin M.

Thank you, @Andrée Starå. I meant to include that Dynamic View and WorkApps are both priced out of range for my client, though it sure would eliminate so many workarounds!

Kevin M.

More info that may guide an answer:

As I mention, the automation works about 60% of the time. Where it errors out is in the clearing of the checkboxes. The process will run, and either Sheet A fails to clear Checkbox 2, or it succeeds in clearing Checkbox 2, but Sheet X fails to clear Checkbox 1 after the clearing of Checkbox 2 trigger.

I'm thinking it's a timing issue. The two automations are triggered so close in time that one or the other isn't able to run fully before the next automation is triggered.

One solution that I think will work is to set the "clearing of the checkboxes" process to run hourly, keeping the "Send Update Request" process instant. I'll have to test this out.

Still, does anyone have any similar tricks up their sleeve?

-K