Import filtered data from Excel to Google Sheets
Without a doubt, Google Sheets application has made our lives easier. Given its cloud-based platform, one can access the data and collaborate with colleagues independent of location and device. Despite such advancements, there are still areas where Google Sheets falls short. For example, there isn’t a functionality available to automatically import Excel into Google Sheets using a filter.
Why import Excel into Google Sheets?
To explain this, let us meet Justin, a customer relations manager (CRM) for his region in one of the biggest retail businesses in the country. He is responsible for strengthening the customer experience and thereby help retain its customers. Twice a week, the company conducts online surveys across the country. The participants are randomly picked and not considered for surveys again for the next 3 months. Therefore the company has countrywide data with it twice in a week. While the company knows how their customers are feeling about them on a higher level, it is the responsibility of the regional CRMs to ensure they plan and execute their strategy based on the ongoing results for their region.
But, the company-wide information is pooled in the form of an Excel file. Whereas Justin uses Google Sheets for his spreadsheet needs because he mostly travels on the field. He would rather be interested to see how the customers from his region are responding to his strategy than go through with the entire list of regions. Therefore, he will need to import Excel into Google Sheets with a filter that has information from all regions. How can he do that?
Import Excel into Google Sheets using Sheetgo add-on
The company places this comprehensive “Company survey report” Excel file (snapshot below) on the Google drive using either of the following. And, to enable its CRMs to be able to access this file, they have accordingly granted read permissions.
- Google Drive desktop application or
- Google Drive plug-in for Microsoft Office
To understand the above-mentioned approaches please go through the section Upload Excel file to Google Drive in this blog post.
That said, let us go through the steps required to configure this process.
1) Start Sheetgo and initiate an import link connection
If not already installed, we can get the Sheetgo add-on by clicking the button below.
Having installed Sheetgo on our Google Sheets application,
Doing so will open the Sheetgo interface on the screen as a sidebar, as shown in the image below. Click on the green + button to start creating the connection.
2. Link with the source file where we want to import the data from
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 Company survey report file for the data source, and we want to import the data from tab Sheet1.
We then arrive at the Settings screen. Here we can select the frequency of updates in the Automatic update section. We enable this button and leave it to the default option, ‘Daily’ because the company updates the survey report twice a week. Also, in the future, if Justin doesn’t need the automatic updates
3. Add the filter condition for selective data import, and create a connection
To do this, we scroll down a bit on the sidebar until we see the Filter by section. Enable this button to add a filter condition. Justin needs data corresponding to his region comprising of the states California, Nevada, and Oregon. From the second image on this post, we realize that the location field is in column C. Since we need multiple locations for filtering, we will select Query language option and key in the following query:
SELECT * WHERE C=’California’ OR C=’Nevada’ OR C=’Oregon’
The Filter by section now shows the query that we just keyed in.
Please note that we can opt for a simpler approach if we had a single value condition. For instance, if the region had only California, we could go for Condition instead of Query Language as shown below.
Finally, to establish a link that lets us import filtered data from Excel, we click on the SAVE CONNECTION button. Doing so, we notice that in a few seconds Sheetgo creates a new connection whose status shows “Last update: Just now”. Congratulations! We did import filtered data from Excel to obtain the survey information specific to the regions California, Nevada and Oregon that Justin handles!
