Using an Automation to Duplicate a Row in the Same Sheet

Jeff Kline

I have read several posts that suggests that this is not currently an option.

I have a sheet which contains a list of evaluations. Sometimes we have to complete a second, or even third evaluation of the same person. Each row represents a person and their evaluation. If we have to do a second evaluation then much of the data in the row is unchanged. So we want to duplicate that row in the same sheet and update a few fields.

So my staff receives a report to process, and use a Report that pulls the pending evaluations. They enter some values. I want them to enter a value (a checkbox in a column named "Duplicate Row"). I want that checkbox to trigger an automation that Duplicates the row in the same sheet, puts it at the bottom, and then changes a few values - specifically unmarking the "Duplicate Row" box.

Is there a work around that does not involve programming? Is there a way to create an automation maybe opens a "Temp Sheet" and then moves the row back to the original sheet? Maybe by triggering a second automation? I could have them do it in the original sheet with a simple Copy and Paste, but that sheet contains 1000s of rows. Also the report allows me to filter out the unneeded columns and focus on this task.

Paul Newcome

You would need to set up a second sheet with an automation. Basically you would use a Copy Row automation to move it off of the main working sheet, use a Copy Row automation to move it off of the second sheet and back onto the main sheet, then use a Clear Cell automation to delete the data you want removed.

Jeff Kline

https://community.smartsheet.com/discussion/comment/365153#Comment_365153

Paul, How would I trigger the automation in the second sheet? Also, if I triggered this from a report (using a checkbox) would a simple refresh to update the report cause all the automations to trigger and update the report with the new row?

Thanks for your help.

Andrée Starå

Hi @Jeff Kline

I hope you're well and safe!

You'd trigger it by a Workflow that triggers by added rows.

It would trigger when you save the changes in the Report.

I hope that helps!

Be safe, and have a fantastic week!

Best,

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

✅Please support the Community by marking the post(s) that helped or answered your question or solved your problem with the accepted answer/helpful, Insightful/Vote Up/Awesome. It will make it easier for others to find a solution or help to answer!

Paul Newcome

@Jeff Kline You would se the automation in the second sheet to trigger when rows are added so that you shouldn't have to go into the second sheet for anything.

Checking the box from a report should work as that feeds directly to the sheet itself.

Jeff Kline

https://community.smartsheet.com/discussion/comment/365482#Comment_365482

Paul and Andree,

I was able to trigger 2 automations by using a "temp" sheet to store the row.

Here is the flow:

I check a box in a column called "Duplicate Row"

It copies that row to a "temp" sheet.

That triggers an automated work flow in "temp"

That workflow is supposed to:

uncheck the "Duplicate Row" box,

Change the value of a couple of rows

Then move that row to the main sheet, "Client" Sheet.

But the second automation will not allow me to uncheck the boxes and change cell values then copy the row.

I cannot figure out why I cannot change the cell values and then move the row? I can move the row, but not both steps. Any ideas why of the limits?

Paul Newcome

Have you tried setting up two separate automations?

Jeff Kline

Paul and Andree, you have both been a big help. Paul the two automations do seem to work. I think it is a bit clumsy. I am adding it to my suggestion list for the developers. I think they should make it so that I would not have to have two autmations and maybe make it easier to do these types of procedures. Or just add row duplication to Reports with a right click.

Andrée Starå

@Jeff Kline

Excellent!

You're more than welcome!

✅Please support the Community by marking the post(s) that helped or answered your question or solved your problem with the accepted answer/helpful, Insightful/Vote Up/Awesome. It will make it easier for others to find a solution or help to answer!

Paul Newcome

Happy to help. 👍️

johngraham78

I have been using this same workflow to duplicate rows within a sheet. However, a problem occurs when you have a column that is an auto number and need a new number created. Copying from sheet to sheet keeps the auto number value, but copying a row within a sheet assigns a new value -- it's inconsistent behavior.

Not sure if anyone has come across this but I can't seem to find a workaround that will remedy this. Of course I could manually change the column type in the helper sheet, delete the auto number, then turn it back to an auto number field. But I'm looking for something automated.

Mark Rojas

@johngraham78 I have run into the same issue. Did you ever find a solution?

johngraham78

@Mark Rojas, yes! I just figured something out 2 weeks ago.

It is very convoluted, requires datamesh, and will take some time to understand/set up, but it works perfectly for us (and hopefully for you).

Here's what I did....

You'll need the main sheet where you want a row duplicated, and not 1, not 2, but 3 helper sheets.

Basic File Explanation:

Main Sheet -- when a check box is ticked (or some other trigger), then an automation copies this row to Helper Sheet A. The checkbox should quickly be cleared afterwards, using automation. This main sheet has an autonumber field called Row ID. This sheet should have at least one field that is a formula. Duplicated rows coming into this sheet will not be allowed to create a value in that field. Let's call that field Formula1 :-)

Helper Sheet A -- Nearly identical in construct to Main Sheet. However, Row ID should have the first row with a permanent entry. I called this value in Row ID "Permanent 1". (I changed the field type to text, changed the value in row 1, then back to autonumber). The sheet also needs a column called Row Number (text field). The value in the first row of this column should be:

=COUNTIFS([Row ID]$1:[Row ID]@row, OR(ISBLANK(@cell), NOT(ISBLANK(@cell))))

The reason you have this permanent row is to allow this formula to always be present. You cannot make it a column formula because is has an @cell reference. However, just putting it into the first cell makes it propagate to new rows as they copied from the Main Sheet. So if the permanent row always exists, then the effect of a column formula persists. Not sure why this works, but it does....

Helper Sheet B -- this sheet is very similar in construct to the Main Sheet and Helper Sheet A, but it does not have a column called Row ID. It instead has the column Row Number, and it is text type. I always keep 20 values/rows in place, in case people want to create a duplicate row up to 20 times per day. Just to be clear, rows 1-20 have values 1-20 in Row Number column. Also in this sheet is a field called Formula1 that has an index/match formula:

=IFERROR(INDEX({Row ID}, MATCH([Row Number]@row, {Row Number}, 0)), "")

--> Row Number@row will always be 1-20, because you always keep them there.

--> Row Number is a cross sheet reference pointing back to Row Number in Helper Sheet A.

--> Row ID is a cross sheet reference pointing back to Row ID in Helper Sheet A.

So, this index/match pull in Row ID values from Helper Sheet A, but they are not in an autonumbered field.

Helper Sheet C -- This acts as a trash bin and can just be a blank file.

So far, you get the correct Row ID value of our desired duplicated row propagated to Helper Sheet B, but in a field not called Row ID. What good does that do?? Well here's where the magic happens, or something like that...

You need to use datamesh to set up a sync between Helper Sheet A and Helper Sheet B.

Lookup values should be:

Row ID for Helper Sheet A, and Formula1 for Helper Sheet B.

Now you can sync over all the fields you care about. If your Main Sheet has 50 values you care about, then datamesh should have 50 values synced. It's a pain, but it works.

Helper Sheet B is starting to look like your desired duplicate row, but without a Row ID in a field called Row ID.

Two more critical sets of automations that pull it all together:

Helper Sheet B should have an automation that is triggered by any row's value changing. It copies the row back to Main Sheet. And since Helper Sheet B does not have a Row ID field, a new one is created for you in Main Sheet. (A temporary Row ID value exists only in the field called Formula1, which is a formula in Main Sheet and will not get copied over).

To prevent these sheets from getting too large and restricting them to 20 rows at most, here are two automations.

Helper Sheet A should have a daily automation that runs in the middle of night. It should move every row with a value in Row ID that does not equal Permanent1. I move my my rows to, you guessed it, Helper Sheet C. Remember, this is just a trash bin.

Helper Sheet B should also have an automation in the middle of the night, but 1 hour after the automation in Helper Sheet A. You don't want this one to remove rows, since you always need values 1-20 to exist. So it just clears values from all critical cells. Again if you have 50 critical fields you want transferred as part of this row duplication, then your datamesh should have 50 values, and your cell clearing operation should have 50 fields cleared.

That's it! I'm sure this is thoroughly confusing. I did put quite a bit of though/trial and error into this, but I welcome feedback in making it better/easier. Happy to send some screenshots if this helps.

@Genevieve P. , I would love it if you could show this workflow to you product team. A simple user story is "As a smartsheet user I want to duplicate rows within a sheet that contain an autonumber field". It's possible to do, but I'm jumping through hoops to accomplish this. I'm sure steps could be reduced to accomplish this much easier than I'm doing.

Mark Rojas

@johngraham78 Some screenshots would be amazing, I got a little lost when you were explaining the relationship of Row ID to Formula 1. Also do you know if the limit to your solution is 50 fields or could I go higher? I know Datamesh can go up to 190 columns, and I have built automations to move/copy up to the 400 column limit.

Mark Rojas

@Paul Newcome Have you ever dealt with the issue @johngraham78 and I have run in to, and if so do you have any solutions that don't bring Datamesh into the mix?

johngraham78

@Mark Rojas , I just picked 50 as a random number. I only have 25ish columns to sync. I suppose the limiting factor is datamesh's 190 column restriction. Images are below. Hopefully this helps.