Triggering automated update requests with forms by users not shared to the sheet
I'm looking for a way to allow users to trigger an automated update request from a sheet for rows that are assigned to them in a contact list column. The sheet tracks all transactions that we use for billing our projects. Each row is a specific transaction (Vendor, invoice number, billing code, etc.). We have automations set up to send out update requests on a date an time based intervals but would like to give users the ability to trigger an update request without being shared to the sheet. I tried using a simple form that adds a row to the sheet with a trigger cell but it doesn't work. I don't want to use an on demand report since that would require sharing the sheet to the users. I'm restricting the input from users to web-form only.
Ia it possible to create a helper sheet that gets inputs from users via a form and somehow trigger the automations on my transaction sheet from data in the helper sheet? The goal here is to create "on demand" update requests.
Answers
-
Hi Burt
Help me clarify your request.
You want an assigned individual, who doesn't have permission to a sheet, to initiate an automated update request ad-hoc? I understand that your current update request automation is time based.
Is there a specific condition that can be used to trigger the condition rather than user decision? If yes, perhaps we can build that condition into your current sheet as the actual trigger.
If it must be a user decision (is this a misnomer then to designate it as automated?) then a helper or feeder sheet as suggested is probably the answer. I would then explore the INDEX/MATCH function. It is a function set that looks up information when named criteria is matched. Note: If the Assignees are assigned to multiple rows, you will need a unique piece of row information in addition to assignee name to filter one row from another. Something like Project Name? Or Invoice Number? Something that the user will know and can enter in a form. Again, this is only necessary if assignees are assigned to multiple rows.
New Helper Sheet - can be fed with a form
Contact column (I'm calling this HelperSheet Contact)
Checkbox (I'm calling this HelperSheet Checkbox) [On the form, this can be a hidden form field and defaulted to checked]
Whatever unique field was on your original sheet if needed (like Invoice No, etc). (HelperSheet Unique). This will also need to be on the form
Original Transaction Sheet
Add a new Helper checkbox column (I'm calling this TransactionSheet checkbox) [Do not restrict this to a checkmark only]
In this TransactionSheet checkbox field
=INDEX({HelperSheet Checkbox}, MATCH(TransactionSheet assignee@row, {HelperSheet Contact},[TransactionSheet Unique]@row, {HelperSheet Unique}, 0))
You will need to replace these with the real column names you are using.
You can then create an update request to trigger from your original transaction sheet when a row is changed based on TransactionSheet checkbox being checked
Kelly
