Automation to record values from one smartsheet onto a new row on another smartsheet once a week?

honeybear

Hello, I imagine the answer(s) to this question already exist but I haven't had luck finding them, please point me in the right direction, here's my situation:

I have a Smartsheet ("sheet 1") that my team records numbers on during the week every week. "Sheet 1" has 100 rows, and there's one column ("column x") in "sheet 1" that the team enters 100 numbers for (a number in each row).

I'd like to run an automation every weekend that records those numbers in a different sheet ("sheet 2").

Every week "sheet 2" will need to automatically create a new row, automatically enter the week number in the primary column and the numbers from "sheet 1, column x" would be populated in that new row.

"Sheet 2" therefore has 100 columns to store the values from the 100 rows in "Sheet 1" every week on a single row, which repeats every weekend.

There are at least 2 automations or functions needed. Is this possible, and if so, are there a few threads you can point me to? Thank you in advance!

Paul Newcome

You would need to insert the 100 extra columns onto sheet 1. Then you would use basic cell references to pull each row into the appropriate column. Finally you would use a weekly Copy Row automation to copy this single row to sheet 2.

honeybear

Thank you Paul for taking the time to respond!

I think I understand your answer, you're saying that I would handle the row-to-column transposition on Sheet 1, and then set up a weekly automation to copy values from the 100 columns on Sheet 1 to the 100 columns on Sheet 2.

The part I don't understand is how to make Smartsheet copy the values from the 100 columns onto a new row each week? My goal is to automate reporting so that Sheet 2 has the recorded values from every week where each row is a week of data, so it would need to create a new row automatically each week to hold the new weekly data. Is there an automation for that?

Thanks again!

Paul Newcome

It would be the Copy Row automation. You can set it up to run weekly. You would set the trigger to be date based and then select the option to set it as a custom recurrence. That should give you a pop-up that will allow you to set it to run every 1 week on a specified day.