Data Shuttle Merge and Append Master Sheet

Jim B

Hi all, hoping someone can clear up a misunderstanding or provide a workaround here.

I have a number of 'sub-sheets' that I would like to merge into a Master Sheet. They are all the same format have the same columns and have a common key column that can be referred to when SS is working out whether to add a new row or update an existing one.

Everything works as expected when offloading/uploading from a single sub-sheet, let's call it 'Sheet1' into the Master Sheet.

However, when I create upload/offload workflows for Sheet2, Sheet3 etc. I find that Data Shuttle wipes the Master Sheet before an upload. Meaning my Master Sheet only ever contains data from Sheet1 or Sheet 2 or Sheet 3. It does not merge/append rows from all the sub-sheets, only one at a time. EVEN though the key in the key column is unique across all the rows in the sub-sheets.

Am I doing something wrong or is this just not possible with Data Shuttle?

Thank you.

Jim

Kerry St. Thomas

Make sure that in your Shuttle configuration(s) under Target, you have "Merge data into target" radio button chosen, and the "add rows" checkbox chosen. First time around, it can be difficult to populate your final destination sheet - you MIGHT need to force it to populate by choosing "Replace all Target" radio button - and then once it runs ONCE, you can update the Target and manually trigger the Shuttle.

Kerry St. Thomas

Make sure that in your Shuttle configuration(s) under Target, you have "Merge data into target" radio button chosen, and the "add rows" checkbox chosen. First time around, it can be difficult to populate your final destination sheet - you MIGHT need to force it to populate by choosing "Replace all Target" radio button - and then once it runs ONCE, you can update the Target and manually trigger the Shuttle.

Jim B

Excellent tip, Kerry! Thanks so much. It worked.

My worry is though that I will need to do the 'Replace All' loop every time I want to merge a new sub-sheet in. And doing so which will scrub the sheet and all existing values which I need to retain in order to sustain lookups across other sheets.

But maybe once the first 'Replace All' has run the sheet will be ready to accommodate both new rows from new sub-sheets as well as amendments to existing rows on active sub-sheets.

Guess I just need to trial it and find out. I'll report back.

Thanks again.

Jim

Jim B

Thanks again, Kerry.

In case anyone else comes looking for this issue, new sub-sheets are getting merged in as expected. I have not needed to run the 'Replace All'.

All the best,

Jim

Danielle Wilson

@Jim B if you're looking to merge data in from multiple sheets into a master one, I'd definitely recommend using DataMesh instead of Data Shuttle. Data Shuttle is great if you're bringing in files from external systems but if you're just looking to merge & consolidate within sheets / reports in Smartsheet, DataMesh is definitely the way to go.