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
Install the Sheetgo add-on for Google Sheets via the blue button below. It takes you to the Google Workspace Marketplace where you can install Sheetgo on your Google Sheets.
Alternatively, get Sheetgo directly from inside your spreadsheet.
Open your Google Sheets file and navigate to Extensions > Add-ons > Get add-ons, then search for Sheetgo and click Install.
Step 2: Select your data sourceSelect your data source. This is the Google Sheets file that you want to import, or pull, filtered data from. Click in the import data section on select data > Google Sheets > Select file. 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 Done editing to click on the Filter icon 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”).
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.
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.