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
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 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”).
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’
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.
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.
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.
