How to copy and replace data using automation

dkunkel

Background: I have a sheet with a list of projects and their monthly savings actuals+forecast (Jan thru Dec) and prior forecast (Jan thru Dec) where prior forecast is last month's actuals plus forecast. Between the 1st and 10th of every month, project owners will go in and update actuals for the previous month and forecast out to the end of the year.

Goal: Before the PM's update their projects on the 1st, copy the data to another file and use index(collect()) on the main file to fill in the values for prior forecast.

The problem I have is when I copy the data to another file using automation, it won't write over the data already there from last month's copy. Is there another way to do this?

dkunkel

Note, I've tried to use automation to delete all the columns in the copy file before the main file is copied over using another automation but I have columns that don't delete (row Id, Created by and Created date)

Georgie

Hi @dkunkel,

It’s not currently possible to use automation to delete rows or overwrite data in a sheet. As you’ve noted, you can set up automations to clear cells, but these don’t clear Auto-Number/System columns (and they also don’t clear formulas).

I found a similar product idea here - you can see that the status of this idea is currently “On Wishlist”, so it’s something our Product Team wants to provide. I recommend adding your vote to that idea to show your support for it - you’ll then get updates when the status changes.

An alternative, if you have access to it, would be to use Data Shuttle, since it would allow you to upload data to Smartsheet and replace all rows in the target sheet when doing so. More information about Data Shuttle can be found here: About Data Shuttle.

Hope that helps!

Georgie

dkunkel

Thanks Georgie! Data shuttle would be a great option if it allowed me to run it automatically on a certain date. Thanks for the link to a similar question. I'll upvote that idea.

Georgie

Hi @dkunkel ,

It's possible to schedule your workflows in Data Shuttle - check out the following links for more on scheduling upload and offload workflows:

• Schedule workflows - upload
• Schedule workflows - offload

Cheers,

Georgie

dkunkel

Thanks Georgie, understood. However, I would need to run the data shuttle on for example, the first of every month. After the 1st, the data gets updated via project managers. I need to store the history before they update. Data shuttle only allows to schedule up to every 12 hours from what I understand. I need once a month. If I use an automated data shuttle that runs every day, it will write over my snapshot I took from the day before with updated data. Could be I am missing something, but this is how I understand data shuttle's capabilities.

Georgie

Hi @dkunkel,

I see what you mean. I found the following two related Product Ideas - you might want to add your vote to these too:

• Data Shuttle Run Options
• Schedule Data Shuttle for a Specific Day of the Month

Thanks,

Georgie