How do you force automation to wait until row is finished calculating before copying over?

Hollie Green

I have an automation set up to copy rows to a new sheet when a row is added or changed. I am not able to set it up for a certain time of the day because the sheet may be updated more than once in the same day and some of the data is deleted. I need all of the data input at any time on the other sheet. When I set up the automation it copied the rows over however on some of the formulas on the sheet it put #Calculating instead of the value I needed.

Jeff Reisman

@Hollie Green

Sounds like you have a lot of formulas in your sheet, and if there are a lot of simultaneous users, this can certainly slow down the formula calculations.

Here's an idea - Use a helper column that counts the number of values in your formula fields, and use that as the trigger to copy your row. For example, say your formulas are in one date field, one number field, and one text field, you could create a formula that adds up the cells that have the correct data format, and when it equals 3, that triggers the automation. =IF(ISDATE(StartDate@row), 1) + IF(ISNUMBER(Total@row), 1) + IF(ISTEXT(name@row), 1)

This way, if any of the three fields are calculating, and therefore not the format they're supposed to be, the helper cell does not equal 3, and the row is not copied until the cell DOES equal 3.

Hollie Green

This worked perfectly thank you for your help

Jeff Reisman

@Hollie Green

Is there some point where the people entering/changing/deleting data know that the row is "done" and ready to go? You could add a manual checkbox that is the trigger to tell automation to execute the row copy.

Only other thing I could suggest is the Data Mesh premium add on that will create new rows in the other sheet and update the data to match the source sheet. (Data Mesh will NOT delete rows from the destination if they are deleted from the source though.)

Hollie Green

I don't want to rely on a person to do it before deleting a row. There are too many people working in the sheet. If it isn't copied over then the data is lost and can't be recovered. Also I don't want the destination sheet to necessarily match the current data on the source sheet that is why anytime a change occurs it copies it over. I just want the formulas to finish calculating and have the actual data at the time of the change instead of inputting the #calculating

Jeff Reisman

@Hollie Green

Sounds like you have a lot of formulas in your sheet, and if there are a lot of simultaneous users, this can certainly slow down the formula calculations.

Here's an idea - Use a helper column that counts the number of values in your formula fields, and use that as the trigger to copy your row. For example, say your formulas are in one date field, one number field, and one text field, you could create a formula that adds up the cells that have the correct data format, and when it equals 3, that triggers the automation. =IF(ISDATE(StartDate@row), 1) + IF(ISNUMBER(Total@row), 1) + IF(ISTEXT(name@row), 1)

This way, if any of the three fields are calculating, and therefore not the format they're supposed to be, the helper cell does not equal 3, and the row is not copied until the cell DOES equal 3.

Hollie Green

Thank you I will try that

Hollie Green

This worked perfectly thank you for your help