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 files 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.
- At the top of the screen, give your Untitled Worflow a name so you can identify and edit the connections later.
Here I’ll show you how to consolidate, or combine, multiple Google Sheets files into one central spreadsheet.
Step 2: Select your data source
- To 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 the first Google Sheets file inside your Google Drive. Click Done then +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 results for all of my students.
- 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 merge data from each sheet.
How to combine Google Sheets in a folder
You can also Consolidate from a folder with Sheetgo. To use this option, go to Data Source and select An entire folder.
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 Sheets file to the folder, it will be included in the connection automatically.
Optional: identify source data origin
Before you click Continue, you have the option to configure the connection.
If you are merging data from multiple source files it can be helpful to have the data origin clearly listed in the destination file.
Under Settings, enable the Identify source feature.
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.
In the following example, I will enable Identify source and choose File name as the identifier. The name of the source sheet will appear in a column next to the students grades data.
Step 3: Select your destination file
Under Send data to, choose which file you want to send the data to. This can be an existing file (Google Sheets, Excel or CSV) or a new file that Sheetgo will create for you automatically. In both cases, the consolidated data from your source files will appear in a new tab (sheet or worksheet).
- Click +Select file, locate the file in your cloud storage, then click Done. Note that your destination file can be any file type stored in any of the cloud storage platforms supported by Sheetgo.
- Enter a name for the destination tab in the New File Tab box. If you leave this blank, Sheetgo will automatically name the tab Sheetgo_SourceTabName.
- Under Destination, select which type of file you would like Sheetgo to create (Google Sheets, Excel or CSV).
- Select which cloud storage folder you want the file to be saved in by clicking on +Change destination folder.
- Under File Name, enter a name for the new file.
- Under New File Tab, enter a name for the destination tab.
In this example, I want Sheetgo to generate a new Google Sheets file called “Student Grades” in Google Drive. I name the file destination tab “Grades All Classes”.
Click Finish and save to create the workflow.
Take a look inside your destination file — you will see that Sheetgo has created a new tab containing the consolidated data from all your source spreadsheets.
Step 4: Automate the workflow
Now that your files are connected, you can update (refresh) the workflow manually at any time by clicking Run on the floating workflow menu. This will pull the latest data from the source tabs into the destination tab.
To ensure that changes to the source files are regularly updated in the destination file, schedule automatic updates. This means you don’t need to open a spreadsheet or open Sheetgo, and your data will flow automatically.
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 to combine Google Sheets and automatically merge data from multiple files into one central spreadsheet.
Looking for more time-saving automation solutions? Learn how to transfer specific data automatically by filtering Google Sheets from one file to the other.