Hi Guys,
I want to create automation when status is posted as pictured below
Create all data in lineitem sheets
I tried to play around but unable to do so. Can anyone help?
Hi @hendrik,
This is not a use case that we support (yet) with automations. Automations today can only be triggered by changes to data in the table region of a worksheet, not when cells in the header region change.
However, there are a few things on our roadmap that we expect will help with this in the future:
- We do plan to create a “Button” data type, which would allow you to trigger an automation on click. This is on our medium term roadmap, no ETA yet but we are thinking about it.
- In this case you want a set of data in the table region to be copied to a different worksheet. This will likely either require a custom script automation (which we do plan to support in the medium/long term) or another form of automation that would loop over the table and perform an action on each row (we are considering adding looping capabilities in automations as well).
- It may also be possible to programmatically do some of this using our API when that is made available. Initial beta release of our API is planned sometime in the next few months.
We’ll keep you posted via our product updates and here as these features make it into our development cycle.
Thanks,
Matt
1 Like
Mobile apps are a priority for us but no ETA yet. We’ll share more about this in upcoming product update emails as we get closer to V1 apps this year.
Excellent use case!
- Reacting to triggers from outside the Table region (ie to a change that is not in a ‘database row’ ) is something we may have to consider for automations for the future
- That is the trigger has a ‘Sheet context’ vs a ‘Row context’ that is available today.
- Aesthetically, I may want to try to avoid stuff like 'When $c$7 changes… ’ - We may instead opt for a Named Variable/Range based design/mechanism - ‘When named-variable changes…’
- Do you imagine that you would have to loop through the line items and post only a few of them - say based on some filter conditions?
- I’m also curious about how you plan to manage the invoices in the platform - esp when you have to mail out 1000s of invoices.
This is in addition to the Button based & other mechanisms that Matt described.
2 Likes
- Using named range based trigger is neat!
- Ability to loop based on filter condition will be perfect addition to flexibility spreadsheet.com is famous on.
In addition, you may consider button based trigger that can trigger many actions, rather than only 1 action. In Airtable or Stackby, it is not the case yet. It can be your USP.
In Google Sheets, you can format your Sheet as PDF using apps script. It’s good to consider when you click email button, it can trigger format as pdf , email it, and save it to your attachment field for record keeping purpose.
Btw, I am now doing what I am usually doing in google sheets
=ARRAYFORMULA(IFERROR(INDEX(invoiceitems!$C$2:$C$15, SMALL(IF($F$2=invoiceitems!$B$2:$B$15, ROW(invoiceitems!$B$2:$B$15)-ROW(invoiceitems!$B$2)+1), ROW(1:1))),"" ))
But this doesn’t seem to work. Anything wrong with my formula?
Additional details
This a product issue around evaluating ARRAYFORMULA, we will be addressing it one of the forthcoming product updates. Tracking as #004829.
4 Likes
Thank you for the update!
Issue #004829 has been fixed with the latest upgrade
5 Likes
Thanks for the update!