How to merge data from multiple Google Sheets

by Nov 6, 2016

Spreadsheets are one of the most flexible, accessible and universal tools we have for managing data. And Google Sheets has pioneered the transition from single-user offline spreadsheets to multi-user online spreadsheets. We can now store spreadsheets in the cloud and share them with colleagues using access controls (Sheets’ share function to allow other people to view or edit the file). While this shift has made it a lot easier to share data in a team, many of us still spend a lot of time gathering and collating information from different files. If you’re a manager, you might need to collect data from colleagues or multiple departments. Plus it’s not always wise or practical to create one huge spreadsheet; if you’re a finance or HR administrator you’ll need to keep data separated for privacy reasons. In these cases, you may need to merge data from multiple Google Sheets into one central file.

Why merge Google Sheets into one?

  • Collect data from different colleagues, departments or clients
  • Create a master sheet for reporting
  • Power an automated dashboard from multiple source files
  • Eliminate copy-pasting
  • Give team members autonomy to manage their own data in their own spreadsheet
  • Keep data separated for enhanced security and access control

Save time and combine spreadsheet data automatically

Here we’ll show you how to use Sheetgo’s Consolidate function to merge data from multiple Google Sheets together into a single tab in another Google Sheet. In this example, we will use five sales-related spreadsheets.

Destination file:

  • All Regions

Source files:

  • North
  • East
  • West
  • South

We will consolidate data from the regional source files into the destination file. The screenshot below shows the sample sales data for four weeks across each region.

Sample sales data across all regions:

Merge Google Sheets:Sample Sales Data across All Regions

Prepare your source spreadsheets

For the consolidate function to work correctly, the following criteria must be met:

  • The source spreadsheets must have the same columns. Since we’re consolidating multiple sheets into one, the columns in the source sheets must match. For example, the “Name” column should be in the same position in every sheet.
  • Only the first row in the source sheets should be the header. Sheetgo assumes that the source sheets have standard format with the first row as the header. Data appears in row 2 and below.

How to merge Google Sheets automatically

Step 1: Start Sheetgo

If you don’t have the Sheetgo add-on for Google Sheets installed, just click the button below.

Open a new Google Sheet or the file you want to import data into.

In this example, we’re using our Sales – All Regions spreadsheet.

To start the add-on, go to Add-ons > Sheetgo > Start.

Starting Sheetgo Add on to Merge Data

Sheetgo opens as a sidebar on the screen, as shown in the image below. Click on the green button + Import data.

Please note that, when using the Sheetgo add-on for Google Sheets, the destination file is always the file you are working in. So in this case it’s Sales – All Regions.

Starting Sheetgo Add on to Connect Spreadsheets

Step 2: Select your source files

Select the files that you want to consolidate (merge) into your destination sheet by clicking Select source.

  1. Sheetgo displays your most recently updated files by default. If you can’t see the file, click on the search icon at the top of the add-on to find the file in Google Drive.
  2. Select your first source file, and if the file contains multiple tabs, make sure you select the correct source tab. This is the tab/sheet containing the data you want to consolidate.
  3. Click add more and repeat the steps for your other source files.
  4. When all the source files and tabs are loaded onto the add-on, click Save connection.

Take a look at your destination spreadsheet. Sheetgo has created a new tab containing the combined data from your source files.

In this example, we have consolidated our regional sales spreadsheets (North, South, East and West) into the central All Regions file.

Merge Google Sheets: Selecting Files to Consolidate

 The consolidate connection is active

The Sheetgo connection is now merging data from multiple Google Sheets into one. Here’s our destination spreadsheet containing the imported data:

Example of Merged Data in Google Sheets

Automate data consolidation in Google Sheets

Your Sheetgo connection is now set up. To refresh the data in the destination sheet, just open the add-on and click update.

Save time and make sure you always have the latest data with automatic updates. Under Settings, schedule your consolidate connection to update as frequently as you like — from once an hour to once a month.

Data volumes

  • Sheetgo can merge a maximum of 80 sheets. For efficiency, it may be better to consolidate from a folder. Talk to Sheetgo’s support team if you need help managing large volumes of data.
  • You can merge a maximum of 5 million cells. Google Sheets currently has a limit of 5 million cells per sheet so you cannot exceed this limit while merging the sheets.

Merging Excel to Sheets

If you need to consolidate data from Excel files into Google Sheets, the process is exactly the same with Sheetgo. For more information on how to Import from Excel, check out this blog post.

Share This