How to combine Google Sheets by merging multiple sheets

Cloud-based spreadsheets such as Google Sheets are easy to share with others, allowing multiple co-workers to view and edit files together in real-time. Thankfully this means that sending a spreadsheet as an email attachment is now a thing of the past.

As a result, it’s easier to control your company’s data than it was in the past. Google Sheets are always up-to-date, they’re accessible from anywhere (as long as you’ve got an internet connection), and you can be sure that the entire team is accessing the same version.

Data sharing in Google Sheets

While the shift to cloud-based software has made it easier to share data with colleagues, many of us still spend a lot of time gathering and collating information from different sheets and files.

There are many reasons for this.

Firstly, although online collaboration is great, you may not want to share the same spreadsheet with everyone on the team. If you’re a manager, you might need to collect data from colleagues’ personal files or multiple departments. Too many people working in one spreadsheet can be messy, and administrators often need to keep data separated for data protection reasons.

As well as data integrity and privacy issues, you don’t want one single file to become too large. Huge spreadsheets with multiple tabs are hard to navigate and easy to get lost in. Because it’s online software, the performance of Google Sheets may also be limited by your internet connection, and each file has a size limit of 5 million cells.

Combining data from multiple Google Sheets into one sheet can be tedious and time-consuming. Copy-pasting is a risky way to merge spreadsheets. As well as being slow, it’s prone to manual errors. ImportRange can work well inside one file, but if you’ve ever tried to use it to consolidate data from multiple Google Sheets files, you’ll know that it can generate errors.

Merge multiple Google Sheets into one automatically

A more reliable alternative to copy-pasting, ImportRange, or coding is Sheetgo: a no-code automation tool for spreadsheets. It lets you create connections between spreadsheets to push and pull data between different files and consolidate in Google Sheets.

This means you can combine data from different files and merge multiple Google Sheets into one automatically.

• Combine data from multiple Google Sheets into one file.
• Save time — no copy-pasting or manual work.
• Pull data from other Google Sheets into one central file.
• Consolidate Google Sheets data into reports and dashboards.
• Share team data without sharing the same spreadsheet
• Merge Google Sheets automatically with a system of interlinked spreadsheets.

How to combine Google Sheets files

In the following example, I’m going to show you how to consolidate data from three different Google Sheets files into one new spreadsheet.

Follow these steps to merge Google Sheets into one central file, to generate a summary sheet from multiple sources, or to pull Google Sheets data into a report or dashboard that updates automatically.

Step 1: Install Sheetgo

1. Click on the button below to start using Sheetgo and connect your spreadsheets.
2. Login to Sheetgo with your Google, Microsoft, or Dropbox account.

Step 2: Start creating your Merge Google Sheets workflow

1. When you’ve logged in to the Sheetgo web app, click Create workflow. Then, select Connect files.
2. Give your workflow a name at the top of the screen so you can identify it later. Here I’ll call it Consolidate Google Sheets.
3. Under Select source data, choose Multiple files.

Step 3: Select the first file to merge

• Under Source data > Multiple files, select the first file you want to import data from.
• In the file box, click +Select file and select the file from your Google Drive.
• When the file appears on your Sheetgo screen, select the correct source tab (sheet).

In this example, I want to combine January delivery data from three company depots: North, South, and Central. Each warehouse logs daily dried fruit deliveries in their own Google Sheets file. I want to pull all of this data into one management spreadsheet that we use at central office.

To start with, I’ll select my first file: North depot deliveries, and the file tab January.

Step 4: Select further files to merge

When you’ve loaded the first file into Sheetgo, repeat the same process for the other files you want to merge data from.

Note that to consolidate correctly, every source tab (sheet) should have the same name and the same structure. This means that columns and headers should be the same in every spreadsheet.

• Click +Add another source file.
• Select an additional file and the correct source tab.
• Click Done.

Optional: identify source data origin

Before you continue with the connection, you have the option to configure the settings.

In this example, my three source files contain special formatting and data validation in the Date column. To import the dates correctly, I will activate the Transfer number formatting option.

• Click on Settings.
• Under Transfer formatting, enable Number formatting.

When you’re combining multiple Google Sheets files into one, it can be helpful to have the data origin clearly listed in the destination file.

• Click on Settings.
• Enable the Identify source feature.
• Under Identify source method, choose your desired identifier.

Sheetgo will add an extra column to your destination file, indicating where your source data came from. The source identifier can be the source location, the file name, the file creation date, or the file update date.

Here I want to see which depot the source data came from, so I’ll select File name as the identifier. The name of the source sheet will appear in a column next to the merged data.

Step 5: Choose where to merge the data

Now it’s time to select which Google Sheets file you want the data to be consolidated into.

You have the option to merge the data into a spreadsheet you’re already using, or a completely new Google Sheets file that Sheetgo will generate for you automatically. In both cases, Sheetgo will create a new tab(sheet) containing the merged data.

• Under Send data, click Google Sheets.
• Go to Destination > Google Sheets, then choose New file or Existing file.
• When sending data to a new file, give it a name in the File name box.
• If you want to save the file to a specific location in your Google Drive, click Change destination folder.
• Option: change the name of the file tab. If you leave this blank Sheetgo will automatically name the new destination tab Sheetgo_SourceTabName.

Here I’ll ask Sheetgo to create a new Google Sheets file for me automatically.

I’ll name the new file Total deliveries. In the new file tab box, I’ll call the tab All depots January. 

Step 6: Create the merge Google Sheets connection

• Click Finish and save to create the workflow.

Your destination file opens automatically inside Sheetgo.

As you can see, Sheetgo has created a new spreadsheet called Total deliveries with a tab called All depots January. This contains the merged data from my three source files.

Column D: Source indicates which file the imported data originated from.

Step 7: Update the workflow

Now that your Google Sheets files are connected, you can update the workflow at any time.

This will update each connection, pulling fresh data from the source sheets into the destination sheet. Any changes to the source files will be synced to the destination sheet.

To update the workflow manually,

• Open Sheetgo, select the workflow from your workflows list on the left of the screen.
• Click Run on the menu bar.

Step 8: Automate the workflow

To ensure that changes to the source files are regularly updated in the destination spreadsheet, schedule automatic updates. This means you don’t need to open a spreadsheet or open Sheetgo. Your data will flow automatically.

• Open your workflow and click Automate on the menu bar.
• Click Update automatically.
• Choose how often you want updates.
• Select the time of day and the day of the week.

How to combine Google Sheets in a folder

Sheetgo allows you to merge data from an unlimited amount of files into one central sheet.

If you want to merge a large number of Google Sheets, try consolidating from a folder. This is a quicker and more efficient way to combine data from multiple Google Sheets files.

On top of that, if you add a new file to the folder, it will be included in the next update automatically.

Instead of manually uploading every file you want to merge, just select one single Google Drive folder.

• Open Sheetgo and create a new workflow.
• Under Source Data, select An entire folder.

Just like when you upload files individually, it’s important to make sure that every source file has a source tab with the same name and structure.

How to manipulate merged data in Google Sheets

Once you’ve set up your data merging system, you’ll probably want to edit and process that data with formulas.

The Sheetgo-connected tab is dynamic, meaning that the data will be refreshed every time the workflow is updated.

To start working with the merged data, copy your imported data to another tab (sheet) using an array formula or a query. Need help? Learn more about queries here.

How to combine filtered data in Google Sheets

Here I’ve showed you how to import the entire source tab from each of the source files.

What if you only want to extract and combine specific Google Sheets data? That’s no problem: just apply a filter to the connection.

Let me return to my previous example to demonstrate.

This time, I only want to import delivery data on one item: Apricots.

• Open the workflow inside Sheetgo and go to Connections.
• On the right of the screen, go to the menu (⋮)
• Select Edit connection.

• Click on the Filter icon

Here I’ll use the condition option and the following settings:

• Data matches one condition (OR)
• Data matches > Condition > Column B : Category: Dried Fruit : Item.
• Criteria > Text is exactly.
• Value >Apricots.

When you’ve applied the conditions for your filter, click Finish and Save.

Here you can see that my Total deliveries file now only contains deliveries of apricots from my three warehouses.

Automated file merging in Google Sheets

That’s the lowdown on how to combine multiple Google Sheets into one. Here I took you through to steps to follow if you want to merge Google Sheets into one central spreadsheet automatically.

Found this post useful? Share it with your network and fellow spreadsheet users via the social media buttons on the left!

Looking for more spreadsheet-based automation ideas? Check out our other posts below.