How to automatically export filtered data to other spreadsheets

Today, it is very difficult to imagine our work without spreadsheets. The conventional spreadsheets were predominantly used by a single user in the offline mode. And, Google Sheets has been pioneering the transition to online-multi-user mode. Certainly, life is much easier with such spreadsheets. We can store them online and manage them across the team using appropriate access controls. While these advancements have made a lot of things hassle-free, not everything is taken care of. For instance, we can’t automatically export filtered data.

Why export filtered data?

To illustrate this, let us assume that a sales manager, Mike, has a list of sales leads across all the regions. And he needs to share the appropriate data with corresponding salespersons in-charge. For example, Nathan is the salesperson who takes care of the North region. While Nathan would be interested in North region’s sales leads, he doesn’t really need other regions’ data.

So, what does Mike do? Every time he has a new list, he filters it to retain only the North region’s data and accordingly shares that filtered list to Nathan. Easy, isn’t it? What if there are a considerable number of salespersons and regions to deal with (let’s say about 50)? And, if he has to do that every day with the new list? Imagine the effort that Mike has to put in to get out all the necessary information to the concerned parties. Not so easy, now! To say the least, it gets highly cumbersome. Certainly, it isn’t an ideal working scenario for Mike.

Solution: Automate the process with Sheetgo

Before we proceed any further, please be advised that we’re assuming that you are using Google Sheets application to handle your spreadsheets. We will need a helping hand, in the form of the Sheetgo add-on, to accomplish this automation setup. Sheetgo’s add-on for Google Sheets that boosts the connectivity of spreadsheets to the next level. With Sheetgo we can import, export, merge, filter, and append data. It can automatically manage all the heavy-lifting that Mike had to manually do (explained above). Sheetgo allows us to perform all these functions. We can get it by clicking the button below:

With this rather extremely handy tool, we can configure Google Sheets to automatically export filtered data subject to predefined conditions. And also at a set frequency – hourly, daily, weekly or monthly. The export takes place from the current spreadsheet to other spreadsheets – both ours and other users’.

Configuration: Steps to automatically export filtered data

For the purposes of demonstration, we will use five spreadsheets as listed below.

1. Sales Leads – All Regions
2. Sales Leads – North
3. Sales Leads – East
4. Sales Leads – West
5. Sales Leads – South

As you may have guessed already, we will try and configure auto-export functionality from the first file (Sales Leads – All Regions) to the last four files. It is even more interesting to note that the North, West, and South sheets are external files (i.e. another user shared them with edit access control). Therefore, we will attempt exporting the data to files not only created by us but by other users as well.

We will try with the North file first. Below is the sequence of steps to do so.

1. Start Sheetgo and initiate an export link connection

Now that we have installed Sheetgo on our Google Sheets application, we now open the “Sales Leads – All Regions” file. To start the add-on, we navigate to Add-ons > Sheetgo > Start.

Doing so will open the Sheetgo interface on the screen as a sidebar, as shown in the image below. Hover the mouse pointer over the green circle with a plus, and click on the Export option.

2. Link source sheet with the destination file that we need to send the data to

Clicking the Select file(s) button leads us to the list of most recently updated files by default. If we don’t see the file we want, we can use the search module to lookup a file within Google Drive. For this example, we select Sales Leads – North file for ‘Destination spreadsheet’, and then for ‘Source Sheet‘ choose Sales Leads.

We then arrive at the settings screen. Here we can select the frequency of updates in the Automatic update section. We leave it to the default option, Daily, because of Mike, the sales manager, updates the list every day. Also, in the future, if Mike doesn’t need the automatic updates anymore, he can turn them off in this section.

3. Add the filter condition for selective data export, and create a connection

To do this, we may have to scroll down a bit on the sidebar until we see Filter by section. By default, it shows “No active filter”. Click on ‘edit’ to add a condition. From the second image on this post, we realize that the region field is in column A. Since we need to create a filter based on Region, we ensure it is Column A: Region in the first drop-down. And, since we need North Region’s data to go to the “Sales Leads – North” file, we ensure the second and third options are Text is exactly and North respectively. Then we click on OK.

The Filter by section now shows Column A: text is exactly North. Finally, to establish the link that can automatically export filtered data, we click Connect button.

Doing so, we notice that in a few seconds Sheetgo creates a new connection whose status shows Last update: Just now.

Congratulations! We’ve just exported a filtered list of sales leads specifically to the north region! Let’s check the Sales Leads – North file to see if the data has flown in.

Sure enough, it has, and rightly so! We see that Sheetgo creates a new sheet (the name will be same as that of the connection), and accordingly puts only North data in it.

It might be a good idea to use a connection name that bears some meaning, which is particularly useful if we manage a lot of connections in the future. So, we change the name to North_Region using the edit option against the connection name field.

Create other connections

We repeat the above steps to create connections for other files, in order to automatically export filtered data, but with a couple of differences.

• We choose Sales Leads – East, Sales Leads – West and Sales Leads – South files to export East, West and South region-specific data.
• The filter criteria for the East, West and South files in the Filter by section are Column A: text is exactly East, Column A: text is exactly West and Column A: text is exactly South“

Here’s how the list of connections looks like.

Thus, we have configured the Sheetgo add-on to automatically export filtered data, on a daily basis, subject to specific region filter criteria. With this setup in place, all that Mike, the sales manager, has to do now is just update the Sales Leads – All Regions file. And, he doesn’t have to worry about the other stuff.

If you want to learn how to do the exact same process in Excel, and thus automatically import and export filtered data from Excel, check out this blog post.