How to use Google Sheets data in Microsoft Power BI – TechRepublic

How to use Google Sheets data in Microsoft Power BI – TechRepublic

How to use Google Sheets data in Microsoft Power BI – TechRepublic 0 0 Alan Dickson

Register for your free TechRepublic membership or if you are already a member, sign in using your preferred method below.
We recently updated our Terms and Conditions for TechRepublic Premium. By clicking continue, you agree to these updated terms.
Invalid email/username and password combination supplied.
An email has been sent to you with instructions on how to reset your password.
By registering, you agree to the Terms of Use and acknowledge the data practices outlined in the Privacy Policy.
You will also receive a complimentary subscription to TechRepublic’s News and Special Offers newsletter and the Top Story of the Day newsletter. You may unsubscribe from these newsletters at any time.
All fields are required. Username must be unique. Password must be a minimum of 6 characters and have any 3 of the 4 items: a number (0 through 9), a special character (such as !, $, #, %), an uppercase character (A through Z) or a lowercase (a through z) character (no spaces).
How to use Google Sheets data in Microsoft Power BI
Your email has been sent
Learn how using data stored in Google Sheets is now as easy as copying a URL.
Lots of users are turning to Google Sheets because it’s free and easy to use. Microsoft is probably feeling Google’s influence because the latest version of Microsoft Edge interfaces with Microsoft 365, and the dashboard looks a bit like Google Drive.
With many users and even organizations turning to Google Sheets, you might find yourself needing to import data from Google Sheets into Microsoft Power BI. The capability to do so has been around for almost a year so it might be beneficial to learn how. Fortunately, it’s easy.
SEE: Google Workspace vs. Microsoft 365: A side-by-side analysis w/checklist (TechRepublic Premium)
In this tutorial, I’ll show you how to import data from Google Sheets into Microsoft Power BI. We’ll also review how to import Microsoft Excel data into Google Sheets and vice versa. I’m using Google Sheets in Microsoft Edge and Microsoft Power BI on a Windows 10 64-bit system. You can download the demo file for this tutorial.
Google Drive lets you upload and store an Excel file without forcing you to convert the .xlsx file to a Google Sheets file. After all, Google Drive is about storage, not format. Later, if you decide to save the .xlsx file as a .gsheet file, Google Sheets won’t replace the Excel file. Rather, it creates a new Google Sheets file — you end up with an .xlsx and .gsheet file in Google Drive.
To demonstrate, let’s upload an Excel file into Google Drive and then save it as a Google Sheets document:
1. Open your Google Drive.
2. Click the New button.
3. In the resulting dropdown, choose File Upload. You can also upload a folder, which is convenient, but we won’t do so now.
4. Locate and then select the Excel file you want to upload and click Open (Figure A). You can use one of your Excel files or the demonstration PBIGoogleSheets_ExcelDemo.xlsx file after saving it locally.
Figure A
5. In the resulting submenu, choose Open With, and then select Google Sheets.
The file in Google Sheets is an editable Excel file. If you make changes to the file, Google maintains the .xlsx format. If you decide to convert the data to Google Sheets, click the File menu and then choose Save As Google Sheets (Figure B).
Figure B
At this point, you have two distinct files: One is an Excel file and the other is a Google Sheets file (Figure C). If you make changes to this file, Google will not save them to the .xlsx file and vice versa.
Figure C
Now let’s look at how to convert a Google sheet to an Excel sheet.
Converting a Google sheet to an Excel file is even easier because you can download and change the format at the same time. To demonstrate, let’s convert the Google sheet we just saved to Excel as follows:
1. Open the Google sheet.
2. Click the File menu and choose Download.
3. In the resulting submenu (Figure D), select Microsoft Excel (.xlsx).
Figure D
Or right-click the Google Sheets document and choose Download. Google Drive will download and convert the Google sheet to an Excel .xlsx file, and you’ll save a click.
The long way to get data in a Google Sheets file into Power BI is to save the file as an Excel file and then open that file in Power BI. That’s no longer necessary. To get Google Sheets data into Power BI, you need only the sheet’s URL in Google Drive. To demonstrate, let’s use the Google Sheets file from the previous section as follows:
1. Return to or open your Google Drive.
2. Locate the Google Sheets file that you want to use in Power BI.
3. Copy the sheet’s URL (Figure E) to the Clipboard by selecting it and then pressing Ctrl + C.
Figure E
4. Sign into your Microsoft account and open Power BI as you normally do. When applying this to your own work, open the .pbix file to which you want to add the Google Sheets data. If you currently have a .pbix file open, click the File tab and choose New to close the open file to avoid adding this sample data into a working file.
5. Click the Get Data tab.
6. From the dropdown, choose More (Figure F).
Figure F
7. In the resulting window, enter Google into the Search control.
8. In the list to the right, select Google Sheets (Figure G) and then click Connect. If prompted to allow a third-party to interface, click Connect.
Figure G
9. Enter the URL in the Clipboard (Figure H) by pressing Ctrl + V.
Figure H
10. Click OK.
11. If prompted to sign into your Google account, do so.
As you can see in Figure I, the Navigator displays all the sheets in the Google Sheets document.
Figure I
If you’re using the demonstration data, click only the Sales sheet and then click Load. If prompted to save pending changes, click Apply Changes. Power BI will convert the data into table form (Figure J).
Figure J

You still have a bit of work to do:
Now that the data is in Power BI, you can use it as you would any other data.
Getting Google Sheets data into Power BI isn’t a lot of work. Simply copy the URL into Power BI, load the data and start building visuals.
Learn the latest news and best practices about data science, big data analytics, and artificial intelligence.
How to use Google Sheets data in Microsoft Power BI
Your email has been sent
Your message has been sent
TechRepublic Premium content helps you solve your toughest IT issues and jump-start your career or next project.
The technologies could enable immersive experiences, accelerated AI automation and optimized technologist delivery in the next two to 10 years, according to the firm.
Are you an IT manager or executive trying to make the case for a new ERP vendor? Compare the top ERP software solutions with our list today.
Learn about the new features available with macOS 13 and how to download and install the latest version of Apple’s flagship operating system.
Get great deals on developer and Linux training courses, Microsoft Office licenses and more through these TechRepublic Academy offerings.
This comprehensive guide covers the use of services from multiple cloud vendors, including the benefits businesses gain and the challenges IT teams face when using multicloud.
Recruiting a Scrum Master with the right combination of technical expertise and experience will require a comprehensive screening process. This hiring kit provides a customizable framework your business can use to find, recruit and ultimately hire the right person for the job. This hiring kit from TechRepublic Premium includes a job description, sample interview questions …
Knowing the terminology associated with Web 3.0 is going to be vital to every IT administrator, developer, network engineer, manager and decision maker in business. This quick glossary will introduce and explain concepts and terms vital to understanding Web 3.0 and the technology that drives and supports it.
While the perfect color palette or the most sublime button shading or myriad of other design features play an important role in any product’s success, user interface design is not enough. Customer engagement and retention requires a strategic plan that attempts to measure, quantify and ultimately create a complete satisfying user experience on both an …
IIoT software assists manufacturers and other industrial operations with configuring, managing and monitoring connected devices. A good IoT solution requires capabilities ranging from designing and delivering connected products to collecting and analyzing system data once in the field. Each IIoT use case has its own diverse set of requirements, but there are key capabilities and …


    Would you like to receive notifications on latest updates? No Yes