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.
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.
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