Automatically add rows with data if condition is true

[Igor_Korablev]

I need to prepopulate the spreadsheet with multiple rows corresponding to amount of months specified by user.

Initial screen asks user for [Input1] and time span in months (FROM and TO Date cells).
I want the button to populate the spreadsheet with corresponding Input in a column by creating X rows, where X = number of months.

I'm doing planning application, where long term planning is done.

Is there a way to use Automation to add multiple rows with 1 action using iterative/repeat process?
Is there a way to Automate rows creation by running Automation until condition is False?

Thank you!

[AndrewB]

Is there a way to use Automation to add multiple rows with 1 action using iterative/repeat process?

Yes. The Run Options for automation steps allow you to check a condition, and to run a step once for every row returned by a formula.

Based on your use case, I'd try something like...

1. Create a blank table, just to hold some temporary values, maybe name it "values".

2. Automation on click...

3. Calculate the number of months, "X" in your example.

4. Write "X" into values!A1

5. New automation, from the left nav.

6. Start when cell changes =values!A1

7. Add row to table

8. Overwrite Step, with "Run if this condition is met" set to IF(values!A1>0).... take data from SUM(values!A1, -1) and write to values!A1.

I think this would create a hacky loop with a counter. The button press would set the number, and then the table automation would detect the change, add a row, and then decrease the number by 1 so long as it hasn't hit zero.

[Igor_Korablev]

would let you know if it works, appreciate your help!

[Igor_Korablev]

Don't seem to work.

The implementation is like this.

Button automation:
Takes the current value in A1 cell and increments it to 1.

This part works every time the button hits.

Automation on a spreadsheet:

1. Check if A1 changed.
   
   
2. Add a new row to Values and populate Column2 with the value (just dummy implementation)
   
   
3. Update the cell A1 if condition is true:
   
   

Basically the automation fails silently and no rows at all are created.

Any thoughts?
I'd try to create rows in separate table now and update with progress.

[AndrewB]

My bad, try writing it to values!A2. I think HC doesn't like messing with column headers.

[Igor_Korablev]

Yeah, just tried to work on A2 level... still the Automation don't trigger still.
Wish it would throw an error or something... looking where might be the problem.

The updated automation logic is:

Interestingly it doesn't trigger on manual edit as well.

[AndrewB]

Time to isolate the problem.

1. Does it work if you remove the "Run step is this condition is met" formula?
2. Does it work if you have it take data from ="hello" instead of a formula?

And yes, just see if you can get it to trigger with manual table edits for now.

[Igor_Korablev]

Using this trigger condition (Values!A2 change):

This trigger should add "hello" to Rows table:

but this is not happening still, even if no specific conditions are defined:

Disabled this overwrite step for now (it wasn't working anyway):

[Igor_Korablev]

Ok, it started to add "Hello" when I changed from =Values!A2 in trigger condition to A2.
looking further

[Igor_Korablev]

Ok, so now behavior is fixed, but the end goal is not achieved.

but, the Overwrite step don't trigger the repetition of Automation even though the Cell gets decremented (modified).

With initial input for 10, Automation created the row in Rows and decremented A2 to 9 and stopped there.

My take on it, that the trigger is not hit if the change was made by Automation itself making iterative process impossible. That's a good "safety" mechanism for infinite looping prevention, but don't allow for the target scenario to execute. Unless I'm wrong with my assumption.

[Kamran]

@Igor_Korablev

You are right. The automations currently do not support recursive triggering to avoid run away automations. Do any of your projects go over a year? I have a solution that might work but want to understand your use case better.

[Igor_Korablev]

well, this is a financial planner app. As initial step I'm asking user to input
Current Monthly Income
Average Monthly Expenses (total)
And duration of the planning to be generated.

once the button is hit --> the Automation should create the row for each month for the duration specified.

This allows user to see the cashflow running totals for specified period as a baseline for further planning, where they would eventually update the rows with actual numbers.

Feel free to ask questions if any

[Kamran]

Thanks for the info.

We'll look into providing a better way to iterate over a numeric value to add rows to a table. Here is a potential workaround:

Create a table called months (Maybe added 120 rows for 10 years worth of planning)

In the app, you can use the customer provided dates to iterate over this table and add the relevant dates to your planning table:

The add button has a add row automation:

which is using the following filter:
=Filter(Months,"Months[Month]>=$[StartDate] AND Months[Month]<=$[EndDate]")

and then stores the date from the table for each found row as:

You can use the $[SYS_USER] variable to store the user info and any additional provided info to add the information to the planning sheet.

Please let me know if you have any questions.

[Igor_Korablev]

Going to test it, but it sounds like a fit healthy workaround!
Thanks!