In this article we’ll explore how to use Import Sheet’s Merge function to merge data from multiple Google Sheets together in a single tab.
Why merge multiple Google Sheets into one?
If you keep similar databases in multiple sheets you’ll likely want to merge them into one place in order to perform analysis and track data at a global level. Some of the use cases we’ve seen with our clients are:
- Purchase order tracking: Each of the 11 regional offices for a Indian shipping company tracks its own purchase orders in a Google Sheet. They merge this data into a single purchase order sheet at their Mumbai headquarters to get a complete picture of accounts receivable
- Student grades and attendance: At a school in Iowa each teacher tracks student attendance and performance in a Google Sheet and the administrative office merges classroom sheets into one Master sheet to track attendance at grades at the school level
- Inventory management: An active wear company in Rio de Janeiro keeps track of its pre-orders from its regional sales reps in Google Sheets and merges this data together manage inventory and make sure they are not over selling individual items
How to use the Merge function of Import Sheet to merge data from multiple sheets
Below is a simple step by step process detailing how to use the Merge function of Import Sheet. Before starting there are a few requirements that you should be aware of:
- The source sheets should have the same columns: Since we’re consolidating multiple sheets into one the columns in the source sheet should match.
- The first row, and first row only, in source sheets should be the column header: Import Sheet assumes that the source sheets have standard data format with the first row as the header and data on row 2 and below.
- Import Sheet can merge a maximum of 30 sheets at one time: If you are merging more than 30 source sheets we recommend breaking them up into smaller pieces and then merging the merged sheets.
- You can merge a maximum of 2 million cells: Google Sheets currently has a limit of 2 million cells per sheet, if you are merging multiple sheets you cannot exceed this limit.
Step 1: Install Import Sheet
If you have the Import Sheet add-on installed for Google Sheets go ahead and skip to step 2. If you have not yet installed it click on this link to install Import Sheet.
Step 2: Configure Import Sheet to Merge your source sheets
- Open the sheet in which you would like to merge your source sheets or create a new sheet if it does not yet exist.
- Open the Import Sheet connection wizard by clicking Add-ons > Import Sheet > Start and click on the gree + button to configure an import connection.
- Select the card for Consolidate and in the next window click on the + button to begin selecting sheets.
- Now select the first spreadsheet you want to consolidate and the source tab from within that sheet and next selec Add to consolidate.
- Repeat step 4 until you have selected all your sheets.
- After selecting all of the sheets that you want to consolidate select Options to configure the periodicity and name the connection.
- Click Connect to finish and import the data.
Congrats you’ve now configured your merged connection!
A note on merging data from Excel Sheets
The process to merge data from multiple Excel Sheets is exactly the same, you just need to make sure the Excel sheets are saved in Google Drive. For more information on how to Import from Excel, check out this blog post.