Copying, changing and moving row information

Tammy D

Have a samples request sheet that is populated by sales via a form. Once the shipment has been completed (triggered by date and tracking number input), the row is moved to an archive file and locked. Sales would like the ability to 'copy' previously archived rows, change dates (as well as automatically change the status, remove old comments and tracking numbers, etc). I've added a 'copy' checkbox column to the archive file and created a report to enable sales to check the copy box on the row they would like to duplicate. I was then hoping to be able to automate clearing certain columns, changing certain columns and moving the edited row back to the active shipping sheet while maintaining the original content on the archive sheet. I've played around with copying rows from one spreadsheet to another (archive sheet to active shipping request sheet) and clearing/changing cells. A few questions — it appears you can't have both clearing of cells and changing column cells in the same automation (?). For example, I want to change status (as a dropdown) and clear tracking numbers but it only allows either clearing or changing not both options. Secondly, if I lock my archive sheet rows when they are initially moved, will this prevent the above from happening with none owner users? Is there an easier way to do this? TIA for any and all help!

Paul Newcome

My suggestion would be to incorporate a third sheet. This would be a "Formatting Sheet". The first thing to do would be copy the row from the archive to the formatting sheet. This will allow you to maintain your historical data in your archive sheet.

Once there, you can set up your change and clear cell automations (separately unfortunately). Once the formatting has been completed, you can use a move row automation to push this newly formatted row back to the active sheet.

Keep in mind… Attachments and conversations would not be cleared out.

The only way to get this to work while also clearing attachments, conversations, proofs, etc. would be to leverage the API, the premium add-on Bridge (I have used Bridge for this very same thing multiple times), or other similar third party apps.