Automated Workflow in archiving the data in the sheet

Atipol Kanchanapiboon

We are using the Smartsheet form to collect the real-time customer experience survey data. One of the challenges is, after a few months, the number of rows in the sheet will approach the 20,000 rows limit. Therefore, we need to manually go in and move some of the older data to a new sheet to allow the space for additional submission. We don't remove all the data since we still use newer data for our reports and dashboards.

Do any of you have set up any automation to handle this? For example, every two months, move the data that is greater than two months old to a newly created sheet. Any suggestion is appreciated.

Thank you.

Andrée Starå

@Atipol Kanchanapiboon

Happy to help!

It will be simple to maintain as long as it set up thoroughly, but maybe it would be just as easy to get an alert and manually moving the rows?

✅Remember! Did my post(s) help or answer your question or solve your problem? Please help the Community by marking it as the accepted answer/helpful. It will make it easier for others to find a solution or help to answer!

Andrée Starå

Hi @Atipol Kanchanapiboon

You could an auto-number column and when it reaches specified limits it moves the rows to an archive sheet.

Would that work/help?

I hope that helps!

Be safe and have a fantastic week!

Best,

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

✅Did my post(s) help or answer your question or solve your problem? Please help the Community by marking it as the accepted answer/helpful. It will make it easier for others to find a solution or help to answer!

Mike Wilday

Andree's information would work, but you could also do a workflow that checked whenever data is added via a form and use a condition that anything with a created date that is 60 days less than today would be moved to another sheet.

Andrée Starå

@Mike Wilday

Great idea! 👍️

Atipol Kanchanapiboon

@Mike Wilday @Andrée Starå Thanks for your responses. I tried to create the automated workflow and ran into the issue with what sheet (will be used as archived location) to be moved to. Does Smartsheet allow us to create a new sheet in the automated workflow? We can pre-create a blank sheet for this. However, that same sheet cannot be used for the next archive since it will be full as well. Ideally, each archive should be on a separate sheet. For example, the first archive will go to sheet 1, next one may go to sheet 2.

I don't know if I make this too complicated. I will be open to you guys ideas. Thanks.

Andrée Starå

@Atipol Kanchanapiboon

You're more than welcome!

Smartsheet can't create the archive sheets automatically. You'd need to create the sheet(s) and add automation to them all.

Make sense?

✅Remember! Did my post(s) help or answer your question or solve your problem? Please help the Community by marking it as the accepted answer/helpful. It will make it easier for others to find a solution or help to answer!

Atipol Kanchanapiboon

@Andrée Starå I see, thanks. I just feel automated workflow won't be the best solution in this case. We can create several blank sheets manually beforehand. However, the automated workflow will still need to be modified every time we need to move the rows to a different sheet. This will not be different than manually moving those rows.

Thanks again anyway for your prompt support to us.

Linsie Weatherford

@Atipol Kanchanapiboon couldn't you just set up the same automation on your archive sheets? That way when "Archive sheet 1" get full (date has reached X number of days), it would then move it to archive sheet 2? And so on and so on for Archive Sheet 2, 3, 4..etc. Might take a bit to set up but sounds better than having to manually move them.

Andrée Starå

@Atipol Kanchanapiboon

Happy to help!

It will be simple to maintain as long as it set up thoroughly, but maybe it would be just as easy to get an alert and manually moving the rows?

✅Remember! Did my post(s) help or answer your question or solve your problem? Please help the Community by marking it as the accepted answer/helpful. It will make it easier for others to find a solution or help to answer!

Atipol Kanchanapiboon

@Andrée Starå I like the idea of just getting an alert that the sheet is running close to the limit. I think that may be the best solution. It will help us avoid having to consistently go in and check to prevent the user to get an error when trying to submit the data when the sheet size is at the limit.

@Linsie Weatherford thanks for your advice! I will try that as well.