A lot of users are switching to Google Sheets because it’s free and easy to use. Microsoft is probably feeling the influence of Google because of the latest version of Microsoft Edge interface with Microsoft 365and the dashboard looks a bit like Google Drive.
With more users and even organizations switching to Google Sheets, you may find yourself needing to import data from Google Sheets into Microsoft Power BI. The ability to do so has been around for almost a year so learning how to do it can be beneficial. Fortunately, it’s easy.
UNDERSTAND: Google Workspace vs Microsoft 365: Side-by-side Analysis with Checklist (TechRepublic Premium)
In this tutorial, I will show you how to import data from Google Sheets into Microsoft Power BI. We will also look at how to enter Microsoft Excel data into Google Sheets and vice versa. I’m using Google Sheets in Microsoft Edge and Microsoft Power BI on Windows 10 64-bit systems. You can download the demo file for this guide.
How to upload and save an Excel file as a Google Sheets file
Google Drive allows you to upload and store Excel files without forcing you to convert the .xlsx file into a Google Sheets file. After all, Google Drive is about storage, not formatting. Then, if you decide to save the .xlsx file as a .gsheet file, Google Sheets will not replace the Excel file. Instead, it creates a new Google Sheets file – you’ll have .xlsx and .gsheet files in Google Drive.
To demonstrate, 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 very convenient, but we won’t do that for now.
4. Locate and then select the Excel file you want to upload and click Open (Picture A). You can use one of your Excel files or the PBIGoogleSheets_ExcelDemo.xlsx file after saving it locally.
Picture A
5. In the submenu that appears, select Open with, and then select Google Sheets.
Files in Google Sheets are editable Excel files. If you make changes to the file, Google will maintain the .xlsx format. If you decide to convert the data to Google Sheets, click the File menu, then select Save as Google Sheets (Figure BUG).
Figure BUG
At this point you have two separate files: One is an Excel file and the other is a Google Sheets file (SIZE). If you make changes to this file, Google will not save them to the .xlsx file and vice versa.
SIZE
Now let’s see how to convert Google sheet to Excel sheet.
How to convert a Google Sheets file to an Excel file
Converting Google sheets to Excel files 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 a Google sheet.
2. Click the File menu and select Download.
3. In the resulting submenu (Visualization), select Microsoft Excel (.xlsx).
Visualization
Or right-click a Google Sheets document and select Download. Google Drive will download and convert the Google sheet to an Excel .xlsx file and you’ll save one click.
How to import a Google Sheets file into Power BI
The permanent way to get the data in a Google Sheets file into Power BI is to save the file as an Excel file and then open it in Power BI. That is no longer necessary. To bring Google Sheets data into Power BI, you just need the URL of the sheet in Google Drive. To demonstrate, use the Google Sheets file from the previous section as follows:
1. Go back 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 in to your Microsoft account and open Power BI as you normally would. When applying this to your own work, open the .pbix file that 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 to avoid adding this sample data to the active file.
5. Click the Get Data tab.
6. From the drop-down menu, select More (Figure F).
Figure F
7. In the window that appears, type Google in the Search control.
8. In the list on the right, select Google Sheets (WOOD Figure) and then click Connect. If prompted to allow third-party interfaces, click Connect.
WOOD Figure
9. Enter the URL in the Clipboard (Family picture) by pressing Ctrl + V.
Family picture
10. Click OK.
11. If prompted to sign in to your Google account, do so.
As you can see in Figure IThe navigator shows all the sheets in a Google Sheets document.
Figure I
If you are using demo data, click only the Sales table, and then click Load. If prompted to save pending changes, click Apply Changes. Power BI will convert the data into tabular form (Figure J).
Figure J
You still have a bit of work to do:
- Delete the first column, named Column 1. This is column A in the Google Sheets file.
- Left align the Date column.
- Apply currency format to the Amount column.
Now that the data is in Power BI, you can use it like any other data.
Bringing Google Sheets data into Power BI is not a big deal. Simply copy the URL into Power BI, load the data, and start building the image.
