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.
Using the Filter function in Google Sheets
One solution would be to filter the data before importing it to another spreadsheet.
In this case, you can use the Filter function in Google Sheets. With this function, you can filter a range of data based on specified criteria.
However, there are some limitations that come with this solution.
First, setting up a system with two different steps (filtering and importing the filtered data) can be a bit complicated. You need to create the filter in one spreadsheet, and then use the IMPORTRANGE function in another spreadsheet to transfer the filtered data.
Another thing to keep in mind is that relying on formulas to filter and import data can be a little tricky and prone to errors. If the source data changes, the filter criteria or the import range may need to be adjusted accordingly.
Using Sheetgo to filter Google Sheets data from one sheet to another
If you want to transfer specific, filtered data from one Google Sheets file to another, there’s a better solution. With Sheetgo, you can connect multiple spreadsheets and transfer data automatically, without having to rely on complex formulas. This enables you to:
- Move selected data from one Google Sheets file to another — with just a few clicks
- 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 the blue button below to install Sheetgo
Click +Create workflow and then Connect to start your first connection.
Sheetgo
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”).
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 require.
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.
Take a look at our customer stories to learn more about how Make Me PPE Bay Area made use of this feature to streamline data privacy, viewership, organization, and communication while continuing to work from a single collaborative set of data.
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.
