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)
