Automation: 'Copy Rows to Another Sheet' doesn't really work as advertised.
I have a project plan with a "Weekly Update" column for people to use when appropriate on a task-by-task basis. I want those updates to be saved in another sheet (the reason is that after updates are copied over, I will run another workflow to clear the contents of those cells so that they can be used again for another round of updates in subsequent weeks).
So I created an Automation to run when triggered so that rows with an update will be copied to another sheet.
So far, so good. When I run the workflow, I am prompted to choose between triggering the workflow on the entire sheet or on specific rows.
I want to copy only those rows that have a Weekly Update, but if I choose the "specific rows" option, I have to manually enter the rows where updates appear (see below)…
Well, this makes no sense. I don't want to write down row numbers to do this.
So I try the "entire worksheet" option, thinking that since the "copy rows" action is based on the trigger that the workflow should run only when there is a change a "Weekly Update" cell, my destination sheet will receive only the rows that have changed.
No so. When I run the workflow - after having selected "Trigger workflow on the entire sheet" - the entire sheet gets copied over. Clearly, the trigger is not a conditional function, it is rule. There is no intelligence in the workflow that identifies only those rows that have changed.
I poked around and know that the only way to copy specifically selected rows is to manually enter row numbers, which (from an automation perspective) works only if the rows I want copied are always the same.
If anyone knows a workaround to this or a different solution to register weekly updates and consolidate them in a separate sheet, please let me know.
Now you may be thinking of a report, however, since I planned to run another scheduled workflow that would clear the all cells in the "Weekly Update" column so that people can enter new updates in the following week... a report will not work.
Some exasperation in this post (apologies), but I didn't think it would be difficult for an automation to copy over a row of data based on a condition, but apparently that is not how workflows work.
Thanks for reading…
Best Answer
Answers
-
AS you can see in your screenshot, the "Run Now" feature ignores triggers and only evaluates condition blocks (if any) that are built into the automation. Try inserting a condition block of [Weekly Updates] not being blank.
-
Hello @Berto_DA
Others may have a more elegant solution, however, I have had a very similar situation. If I am understanding you correctly, you want to copy our the rows that were updated to another sheet, then you will clear all of the contents so the following week they can be populated again?
I suggest using a helper checkmark column in your sheet. The helper column would populate with a checkmark based on certain criteria — whatever criterion you are using to make the decision to include the rows when you run this workflow manually. So, build a column formula for the helper column. Then, your workflow condition would say HelperColumn is Checked. Only the rows with a check will be copied out.
Helper column formual would be something like — ("Criteria" can also be another column in that row, if you have a column to reference)
=IF([Column Name]@row = "Criteria", 1, 0), " ")
When it works on one row, right-click and select Convert to Column Formula — that way, after you clear the data, the formula will remain for the next round of updates.
I use a system similar to copy rows from sheets that are formula-driven, to capture the historical hard data each month at a specific point in time, and retain those values. It works really well.
I hope this was helpful.
Pauline
-
Thank you, Pauline. You do understand my goal correctly. I'm a big user of helper columns, so I understand your approach. I will try your solution, but as I'm doing that, I have a question:
"Then, your workflow condition would say Helper Column is Checked. Only the rows with a check will be copied out."
As I create the automation, it is possible to configure things so that only the rows with a check will be copied over? I'm stuck on that point. When I run the workflow, do I choose, "Trigger workflow on the entire sheet" or "Trigger workflow on specific rows"?
Appreciate your help!
-
Whoo, baby! It worked. Pauline, I incorporated your helper column as well.
Thank you both! Next time, I shouldn't give up so quickly.
Help Article Resources
Categories
Check out the Formula Handbook template!
