Combine Google Sheets by merging multiple sheets
Google Sheets has enabled us to use spreadsheets in a more collaborative and efficient way. Like the other G Suite tools, Sheets allows us work together with colleagues in the same file, in real time. Thankfully, sending spreadsheets as an email attachment is now a thing of the past. As a result, it’s easier to control your company’s data: files 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. Nevertheless, managing your data in Google Sheets still involves some manual work. Gathering or collecting data is one of the most time-consuming. Did you know you can combine Google Sheets and merge multiple files into one automatically?
Because Google Sheets are stored in the cloud, it’s possible to create dynamic connections between them. This means you can:
- Combine data from multiple Google Sheets into one file
- Pull data from colleagues or clients into a central sheet
- Gather data for reporting and dashboards — automatically
- Share data with others without sharing your spreadsheet
- Create an automated workflow of interlinked spreadsheets
How to combine Google Sheets
Step 1: Install Sheetgo
- Install Sheetgo here.
- Once inside the web application, click +Create workflow and Connect to start creating a connection. In this example, we’ll create a consolidate connection, pulling data from multiple Google Sheets files into one central sheet.
- Give your workflow a name. In this example, it’s “Combine Google Sheets.”
Step 2: Select multiple Google Sheets as your data source
- To create a consolidate connection and combine multiple Google Sheets into one, select Multiple files as your data source. Your source files are the spreadsheets that Sheetgo will import data from. Note that data is copied to the destination sheet, but your source files will remain intact.
- Click +Select file(s) to find a Google Sheet inside your Drive. Then click +Add another source file.
In the following example, I will merge the gradebooks from my 3 classes into one, to get a clear overview of all my students’ results.
- If your source files contain multiple tabs (sheets) make sure to select the correct tab. In this example, my source tab is “Sheet1”.
- Every source tab should have the same format (e.g. the same column names and the same size header row) so that Sheetgo can correctly consolidate data from every sheet into one.
Option: combine Google Sheets in a folder
You can also Consolidate from a folder with Sheetgo.
If your source files are stored in the same folder, this is a quicker and more efficient way to merge data from multiple files. Sheetgo will automatically combine data from every file in the folder directly into the destination spreadsheet. Plus if you add another Google Sheet to the folder, it will be included in the connection automatically.
Adjust the settings
Before you click Continue, you have the option to configure the connection.
Under Settings you can enable the Identify source feature. Sheetgo adds 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.
In the following example, I will enable Identify source and choose File name as the identifier. You can see the name of the source sheet appears in the column next to the students grades.
Step 3: Select your destination file
Now choose which file you want to send the data to. This is the file where the consolidated data from your source files will appear.
Select an existing file from your Drive, or let Sheetgo create a new one for you.
In this example, I’m using a new Google Sheet as my destination file.
Finally, click Finish and save to create the workflow. You will see that Sheetgo creates a new tab in the destination sheet, containing the consolidated data from your source spreadsheets.
Step 4: Automate the workflow
You can update (refresh) the data manually at any time inside Sheetgo. If you want to automate the workflow, and ensure that changes to the source files are regularly updated in the destination file, schedule automatic updates.
Go to the floating menu bar on your workflow and click Automate. Here you can choose to have the workflow updated every hour, day, week, or month. You can also specify the time of day and the day of the week.
Automated data merging in Google Sheets
That’s how you combine Google Sheets and automatically merge data from multiple files into one central spreadsheet.
Looking for more timesaving automation solutions? Learn how to transfer specific data automatically by filtering Google Sheets from one file to the other.