Moving data from one spreadsheet to another is one of the most time-consuming and repetitive data management tasks that many of us do on a regular basis. As a Google Sheets user, you’re probably already taking advantage of G Suite’s powerful collaboration functions. Sharing a file with others can reduce the need to move data manually, but sometimes you still need to separate and filter Google Sheets data into other files.
Other than manual copy-pasting (which is cumbersome and prone to errors) there is the option to use Google Sheets IMPORTRANGE formula. This allows you to import a range of cells from sheet A into sheet B, in real-time. This can work well for a one-off data transfer, but if you’re a heavy Google Sheets user and you attempt to connect multiple sheets you will often experience errors or your entire spreadsheet can crash.
One of the biggest shortcomings of Importrange is that you can only transfer data from an entire range to another sheet.
Send filtered data from sheet to sheet
If you want to transfer specific, filtered data from one Google Sheets file to another, you can do that by creating Sheetgo connections between your spreadsheets. This enables you to:
- Move selected data from one Google Sheets file to another — in one click
- Share specific data with colleagues or clients without sharing your personal spreadsheet
- Control data access
- Set up an automated workflow
- Send filtered data to multiple other files
- Merge data from different spreadsheets into one
- Generate automated reports and dashboards with filtered information
How to filter data automatically:
Step 1: Install Sheetgo
Click +Create workflow and then Connect to start your first connection.
Step 2: Select your data source
First, give your Untitled Workflow a name so you can easily identify and edit it later.
In this example I’ll call my workflow “Filter Google Sheets”.
Select your data source. This is the Google Sheets file that you want to import, or pull, filtered data from.
If your file contains more than one tab (sheet) make sure that you select the correct source tab.
Here I will select my “Product Inventory” file from inside my Google Drive
Step 3: Apply a filter
Click Continue to go to the Filter section of the connection editor.
Under Filter data, Sheetgo gives you 3 options: Filter by condition (select data by column, number, date, text or value), Filter by Query (write a query using SQL syntax), or Filter by color (select data by cell color).
In this example, I want to filter out only the products that are in high demand.
I choose Filter by condition: Column G: Demand, Text contains: High.
Step 4: Select your destination file
Once you’ve selected your filter, decide which Google Sheets file you want to send this data to.
You can choose an existing file from inside your cloud storage, or let Sheetgo automatically create a new Google Sheets file for you.
If you choose the latter, give the new file a suitable name (here, I type in the name “Products to order”).
Sheetgo will create a new tab(sheet) in the destination file, containing the filtered data.
Under New File Tab enter a name (here, I’ll call my new tab “High demand”).
Click Finish and save to create the connection.
Take a look at your destination spreadsheet. You will see that a new tab has appeared containing the filtered data. Your source file remains unchanged.
I asked Sheetgo to create a new Google Sheets file for me, so the filtered data from my “Product inventory” spreadsheet has now appeared in a new “Products to order”. This file appears in my Google Drive.
Step 5: Automate the workflow
Now that you’ve created a connection, you can transfer fresh data from your source to your destination sheet by clicking Run on the floating workflow menu button.
To ensure you’ve always got the latest data without having to open Sheetgo (or even open a spreadsheet) click Automate. Schedule automatic updates as often as you need: hourly, daily, weekly, or monthly. You can even specify the precise time of day, or the day of the week.
Automated data transfer in Google Sheets
Here I’ve shown you how to automatically filter data from one Google Sheets file to another. One connection between two files is just the start of an automated workflow. You can now build on the workflow by adding more connections, or creating connections to multiple other files.
If you frequently need to combine or consolidate data from multiple spreadsheets into one central file, read how to merge multiple Google Sheets into one.