The ubiquity of the internet and deluge of wireless devices certainly made our daily lives easier. They paved the path for cloud-based applications such as Google Sheets. This application lets us collaborate and work on the spreadsheet data regardless of the location and device. In terms of functionality, Google Sheets has come very far in a relatively shorter period of time. But, as it happens, Google Sheets doesn’t solve every problem. For example, we don’t yet have the means to automatically import filtered data from other Google Sheets files.
Why do we need to import filtered data?
There are many business cases where this scenario is applicable. But let us consider a simple example for the purposes of demonstration. Consider the technical support department within a software product company. They receive customer incidents through phone and web on a daily basis. All these incidents are stored in a centralized spreadsheet file – where a specific problem area is noted against each customer incident. For each problem area, there is a respective backend support person that looks into the corresponding customer incidents.
It doesn’t make sense for a support person to have access to all the incidents that are beyond his area of expertise. Therefore, he will need to import filtered data from the centralized Google Sheets file that stores all the incidents. How could he accomplish that? This is doable with the combination of QUERY and IMPORTRANGE formulas. But to go ahead with this approach, the user has to have advanced knowledge of these formulas. Another downside is that the source data is usually variable in size, so we can’t exactly define the source range boundaries in these formulas. Therefore, we need to keep changing the formula time and again.
Alternative: Automatically import filtered data using Sheetgo add-on
Imagine a large file where the company receives all customers incidents, coming from the reports. As you can see, every different type of incident is stored in this single spreadsheet. In this example, let us imagine that we are the person that tackles all “Run time errors”. Then, it would be very useful to only receive these type of incidents to our personal spreadsheet.
Install Sheetgo
Install the Sheetgo add-on for Google Sheets by clicking the button below.
Sheetgo add-on
Step 1:
Having our personal “Run time errors” spreadsheet open, we can start Sheetgo by navigating to the Menu Add-ons > Sheetgo > Start.
We’ll see the Sheetgo sidebar open up within the Google Sheets interface (please see the screenshot below). Hover the mouse onto the green + button, and we’ll see the Import and Export options. Click on the Import button to start creating the connection.
Step 2:
Click on the SELECT FILE(S) button within the Data Source section. Now we’ll see all the spreadsheet files available within our Google Drive. If we can’t find the source file right away, we can try scrolling down or use the search feature.
Step 3:
Go to the Settings tab. Here we have the option of changing the connection name, which would be the name of the sheet that Sheetgo creates in the destination file. To automate and alter the frequency with which the data import happens, keep the Automatic Updates section enabled.
But, in the interest of the example discussed in this post, we’ll focus on the Filter by feature. We’ll check this box true and it will ask us if it should filter by Condition or by
For the purpose of this example, we will add here:
“Filter by Condition > Which match all of these conditions > Column G text is exactly Run time error”.
Step 5:
Finally, to establish a link that lets us import filtered data from the source Google Sheets file, we click on the SAVE CONNECTION button. Congratulations! We’ll shortly see that Sheetgo creates a new connection. It creates a new sheet (named after the connection by default), that has the required filtered data from the source file.
Revisiting the connection settings:
If in the future, there might come a need to alter one or more of the existing connection settings. To do so, start Sheetgo while on the file that we need the settings changed on. Click on the 3 dots next to the connection name (in this case “SG_Connection”) > Edit connection.
Once done with the modifications, click on SAVE CHANGES.
