How to Use Google Sheets Data in Microsoft Power BI

Many users turn 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 may need to import data from Google Sheets into Microsoft Power BI. The ability to do this has been around for almost a year, so learning how could be beneficial. Luckily it’s easy.

SEE: Google Workspace vs. Microsoft 365: A Parallel Analysis with Checklist (TechRepublic Premium)

In this tutorial I will 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.

How to upload an Excel file and save it as a Google Sheets file

Google Drive lets you upload and save an Excel file without having to convert the .xlsx file to a Google Sheets file. After all, Google Drive is about storage, not formatting. If you later decide to save the .xlsx file as a .gsheet file, Google Sheets will not 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 to 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 drop-down list, select File Upload. You can also upload a folder, which is handy, but we won’t do that now.

4. Locate and select the Excel file you want to upload and click Open (Figure A). You can use one of your Excel files or the demonstration file PBIGoogleSheets_ExcelDemo.xlsx after saving it locally.

Figure A

5. From the resulting submenu, select 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 will keep 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 see how to convert a Google spreadsheet to an Excel spreadsheet.

How to convert a Google Sheets file to an Excel file

Converting a Google spreadsheet to an Excel file is even easier because you can download and change the format at the same time. To demonstrate this, let’s convert the Google Sheet we just saved to Excel as follows:

1. Open Google sheet.

2. Click the File menu and select 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, saving you one click.

How to import a Google Sheets file into Power BI

The long way to get data in a Google Sheets file in Power BI is to save the file as an Excel file and then open that file in Power BI. This is no longer necessary. To transfer data from Google Sheets to Power BI, all you need is the URL of the sheet in Google Drive. To demonstrate, let’s use the Google Sheets file from the previous section as follows:

1. Go back to or open your Google Drive.

2. Locate the Google Sheets file that you want to use in Power BI.

3. Copy the URL of the sheet (Figure E) to the clipboard by selecting it and then pressing Ctrl + C.

Figure E

4. Sign in to your Microsoft account and open Power BI as usual. If you’re applying this to your own work, open the .pbix file you want to add the Google Sheets data to. If you currently have a .pbix file open, click the File tab and select New to close the open file and avoid pasting this sample data into a working file.

5. Click the Get Data tab.

6. From the More (Figure F).

Figure F

7. In the window that appears, type Google in the search box.

8. From the list on the right, select Google Sheets (Figure G) and then click Connect. If prompted to allow a third party to connect, click Connect.

Figure G

9. Enter the URL into the clipboard (Figure H) by pressing Ctrl + V.

Figure H

10. Click OK.

11. If prompted to sign in to your Google account, do so.

As you can see in it Figure Ithe navigator displays all sheets in the Google Sheets document.

Figure I

If you are using the demo data, just click on the sales sheet and then click load. If prompted to save pending changes, click Apply Changes. Power BI converts the data into tabular form (Figure J).

Figure J

You still have a little to do:

• Remove the first column named Column 1. This is Column A in the Google Sheets file.
• Align the date column to the left.
• Apply currency formatting to the Amount column.

Now that the data is in Power BI, you can use it like any other data.

Importing Google Sheets data into Power BI is not a lot of work. Just copy the URL into Power BI, load the data and start creating visualizations.