Combining Data

ScreamsAtBirds

Hello,

I have two sheets within a folder. I would like to be able to automate the combining of the data from these sheets into a new sheet which I could think use to build a report. Is there an easy way to combine the data? The sheets have different columns so replacing the data is not needed.

Brian_Richardson

You should probably take a look at Control Center. Any time you're scaling a consistent setup across hundreds of instances, that's what CC is intended for. It will allow you to identify and summarize data to a summary sheet and will create the references and cell links to move data to where you need it.

If you're using API already, you could also do a "poor man's Control Center" and leverage the API to create references for you on a summary sheet using the methods provided for cross sheet references here:

https://smartsheet.redoc.ly/tag/crossSheetReferences#operation/add-crosssheet-reference

You can also leverage API to copy data to a centralized sheet using Add Row or Update Row.

https://smartsheet.redoc.ly/tag/rows#operation/rows-addToSheet

If you have access to Bridge you can leverage Javascript code to format the row bodies to include only the data that you want, vs using automations which bring over everything. Although it's not exactly what you're doing, you can take a look at my instructions for doing that here:

https://community.smartsheet.com/discussion/97621/use-javascript-in-bridge-to-efficiently-import-data-from-an-api

Heather_Verde

You can build a report from multiple sheets, so there may not be a need to combine them.

Brian_Richardson

^^ this is the answer. Use a report to combine your data.

If you need to trigger or change data in one sheet based on another sheet, use cross-sheet formulas to do that. You don't have to physically move the data into the same place.

If you absolutely must combine data onto a single sheet then probably the easiest thing would be setup a third "combo" sheet, then create an automation in each source sheet to Move or Copy rows to the third sheet based on timing or a trigger like "Row added". The move/copy automations will automatically create the columns needed in the third sheet.

ScreamsAtBirds

https://community.smartsheet.com/discussion/comment/453154#Comment_453154

I think the issue I presented initially is more simplified than the issue at hand, which is my fault. The two sheets that are housed in these respective folders are created through an API integration that syncs on a schedule. There are 100+ folders that have these two sheets. I was hoping there would be an easy way to combine the data from the two sheets so that when I was attempting to reference the data there would be less sheets that needed to be referenced.

Additionally I needed to be able to reference the data in the sheets to perform calculations on the data so it could be used for a dashboard.

I ended up adding columns manually to one of the sheets with the data from the other sheet. I then manually copied the rows I needed to a new sheet that I was then able to use to create a report and also perform calculations on.

Brian_Richardson

You should probably take a look at Control Center. Any time you're scaling a consistent setup across hundreds of instances, that's what CC is intended for. It will allow you to identify and summarize data to a summary sheet and will create the references and cell links to move data to where you need it.

If you're using API already, you could also do a "poor man's Control Center" and leverage the API to create references for you on a summary sheet using the methods provided for cross sheet references here:

https://smartsheet.redoc.ly/tag/crossSheetReferences#operation/add-crosssheet-reference

You can also leverage API to copy data to a centralized sheet using Add Row or Update Row.

https://smartsheet.redoc.ly/tag/rows#operation/rows-addToSheet

If you have access to Bridge you can leverage Javascript code to format the row bodies to include only the data that you want, vs using automations which bring over everything. Although it's not exactly what you're doing, you can take a look at my instructions for doing that here:

https://community.smartsheet.com/discussion/97621/use-javascript-in-bridge-to-efficiently-import-data-from-an-api