Copy rows within a sheet using automation

johngraham78

I have been using a helper sheet and automation to accomplish this. However, a problem occurs when you have a column that is an auto number and need a new number created. Copying from sheet A to sheet B keeps the auto number value, but copying/pasting 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.

Melissa Yamada

Hi @johngraham78

Could you please provide a little more context? Trying to figure out what you need to accomplish by copying to both the same sheet and a separate helper sheet.

johngraham78

Hi @Melissa Yamada, my ultimate goal is to duplicate a row within a sheet. My users access the sheet via dynamic view, and I'd like to have a Create Duplicate Row button within dynamic view. It should copy every cell/field of the row, except the Row ID (which is the autonumber field).

Options:

1. Give all users access to the main sheet. They can copy/paste rows as needed, and a new autonumber will generate. The downside is that this sheet is huge, and I don't want to give them permissions to edit anything.. Dynamic view doesn't give them access to the entire sheet.
2. Use a helper sheet. Using automation, copy row 1 from sheet A to sheet B. Then, using an automation in sheet B, move/copy row 1 from sheet B to sheet A. Sheet A will now have two rows. The problem is that sheet A now has two rows with identical row ID's (autonumber values).

Unless I provide users access to the sheet, then there's no simple way to duplicate rows.

A few simple enhancement requests:

1. support a duplicate row button within the dynamic view UI.
2. When copying rows to a sheet B with an autonumber field, have an option for the autonumber field to generate a new value or maintain the autonumber value from sheet A.

Itai Perez

Hey @johngraham78 ,

Would you mind providing some screenshots? it might help with understanding and ideas.

Thank you,

MichaelTCA

Hello @Itai@johngraham78,

If you don't need to use an auto-numbering system then don't. When moving rows around with an auto-numbering system column...Uffda, I've seen columns get duplicated multiple times, data doesn't end up in the right locations, and it can get really wacky unless all sheets, columns, and data involved in the workflow are nearly perfectly set up and matching.

Also, if you don't need to move rows, then don't. Data (including columns) is tied to the row. Each sheet you move to will also move the columns and possibly add columns you never desired to have on the target sheet.

I've had similar issues with using auto-numbering systems. Currently, I have been using the auto-numbering system as the initial ID when the row is added to a sheet. (Auto-number systems cannot be in the unique identifier column)

Otherwise it's just a placeholder hidden off to the side. I have a function in the column for the unique identifier to control what is displayed.

Then as data is developed or moved, the ID changes to something the next person can understand.

If you want the rows to have duplicate data but separate ID #s (with the same parent ID to reference), one way to do this is using a conditional statement with a count function as the post-fix.

Ex:

This will work great if you are copying rows individually. Unless you set up a function on both sheets, the target sheet will only contain what is displayed in the source and not the function itself. So the next time a new row is moved, it will keep increasing the post-fix by 1.

=IF(COUNTIF(Range, Criterion)>0,"ID #"+ (COUNTIF()+1),"ID #")

This example is the most static and possibly easiest method based on the movement/workflow of your automations, but you have to move rows individually and not an entire sheet. It's a slow process.

I've used count functions to show duplicates between sheets and a condition in automations to say if it already exists, do not move.

Well within an automation or a function, you can do the same thing when moving a row as well.

With limited amount of "revisions", for example, I have automations with criteria to statically "change cell values automation" to +1 or +2 and so on. Then use a function to combine the values from both cells into 1 ID #. So the display will show Revision 1 or Revision 2 as progress continues.

These will get you your "automated" options.

As soon as you try to get into dynamic changes between 2 sheets, it becomes much more complicated because if you use the method above. If we only want to change 1 cell with the same function that the whole column is using, then every cell involved will get the same post-fix and you'll never get the result you're looking for.

Melissa Yamada

Hi @johngraham78 

Have you considered using a form instead? Then you would not need to give users access to the full sheet and it would create a new autonumber field.

johngraham78

@MichaelTCA , thank you for your suggestions. My main sheet is part of a larger system, where reports collect details from other sheets. All have autonumbers. Changing mine will mess up reports/processes.

This seems like a smartsheet oversight to not have a provision for autonumbers to reset when copied to another sheet, or to inherit the target sheet's autonumber system.

@Melissa Yamada , I'm trying to keep users in the dynamic view ecosystem. How would a form help with creating a duplicate row? It would only create a blank row or contain whatever data the user inputs.

MichaelTCA

@johngraham78 I completely agree. Setting up a workflow for a row to travel multiple sheets (with an automated number column) is an incredibly fragile process. Resetting the starting point can mess up the order and you'll start to receive duplicate ID #s. Then the fun really starts...different starting points can create duplicate columns when moving rows too.

During the creation of 5 sheets that were part of a much larger process. I had to create 1 sheet and move a row to another so it brings the columns with, including the starting point for the autonumbering system, and had to do this a few times while adapting and optimizing the other sheets to keep everything perfect.

I also have other sheets tracking ID #s by copying rows and using a function to present the next value to use. When moving or copying a row, the function behind it doesn't move with it.

Melissa Yamada

@johngraham78 You're correct, using a form would mean that users need to input info rather than duplicate. But it would generate a new ID # which it sounds like you are trying to accomplish. I guess it depends on how many fields need to be re-inputted and how onerous that would be.