Using an Automation to Duplicate a Row in the Same Sheet

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

Have you tried setting up two separate automations?

@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!

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

@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?