Import filtered data from Excel to Google Sheets

Import Filtered Data From Excel

Written by Valentine Schelstraete

Jun 10, 2020

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?

Filter Excel to Google Sheets Sheetgo

Import Excel into Google Sheets using Sheetgo

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.

To understand the above-mentioned approaches please go through the section Upload Excel file to Google Drive in this blog post.

Import filtered Data from Excel to Google Sheets:Excel File of Company Survey Report

That said, let us go through the steps required to configure this process.

1) Start Sheetgo and initiate an import connection

If not already installed, install Sheetgo here.

Once inside the Sheetgo web app, click on +Create workflow and then Connect to create the Excel to Google Sheets import connection.

Select your Excel file from within your online storage as your data source (here: “Company survey report”).

Import filtered Excel to Google Sheets - source file

2) Add a filter condition for selective data import

After clicking continue, Sheetgo asks if you want to filter your source data. There are 3 options: 1. Filter by condition 2. Filter by query or 3. Filter by color.

We are going to Filter by query (using SQL syntax).

Justin needs data corresponding to his region comprising of the states California, Nevada, and Oregon. From the first screenshot 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’

Import filtered Excel to Google Sheets - filter by query

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.

3) Send the Excel data to Google Sheets

In the next step, select your data destination. Either choose an already existing file from inside your storage, or let Sheetgo create a new Google Sheets for you.

Give the new Google Sheets (and the tab that will hold your Excel data) a name.

Import filtered Excel to Google Sheets - data destination

Finally, click on Finish and save to save the Excel to Google Sheets import.

4) Automate the workflow

Sheetgo has now created your filtered import from Excel to Google Sheets. 

Set the frequency of the automatic updates to hourly, daily, weekly, or monthly by clicking on Automate on the floating bar of your workflow.

Upload Excel to Google Sheets automatic updates

Conclusion

This is how you import selected data from Excel to Google Sheets, automatically.

Learn here how to backup data from Excel to Google Sheets to create a historic track of your data.

You may also like…

Share This