How to Connect Power BI to Google Sheet
Google sheet is one of the most used collaborative spreadsheets that can perform analytics operations and can be used to save data. A lot of companies have come to use google sheet for capturing and sharing data.
If you have your data in google sheet, you can connect it directly to Power BI without first saving your data in an xlsx or CSV format.
Microsoft recently released a new feature that allows you to select Google sheet as one of the data sources to get data from. This feature is still in the beta stage for testing. We will not be connecting our data with this feature, instead, we will be connecting via web
Accessing the google sheet
The first thing to note is ensuring you have edit access to the google sheet. You will not be able to generate a web link without edit access. You can request this access from the owner of the sheet.
Publishing to web
Now that you have the right access to the sheet, you are ready the publish the google sheet to the web. Publishing to the web generates a link that you use to connect the sheet to Power BI.
To publish to the web, go to the File Tab on the Menu Bar, click on Share, select publish to web
The Publish to web dialogue box opens.
In this tutorial, we will focus on the link section of this dialogue box.
The default selection is the Entire Document. You can choose to publish a particular sheet instead. If this is the case, click on the drop-down menu and select the sheet you require.
To publish the entire document, click on Publish
A pop-up screen appears at the top of your screen to confirm your selection.
Click OK to proceed
The link generated is what will be used to connect to Power BI.
N.B: You can always copy the link again if you close the pop-up window by following the same steps above
Connecting your Power BI to Google sheet
Now that you have successfully generated a web link, you can now connect your Power BI to the google sheet.
In Power BI Desktop, click on Get Data
In the drop-down, a few common data sources will be displayed, click on Web.
If Web is not displayed, you can click on more and select web.
Paste the copied link in the dialogue box that pops up and click OK
A dialogue box titled “Access web content” pops up
Choose Anonymous to access the content on the google sheet.
On the drop-down menu, select the last option with the full web link.
Click connect.
The Power BI Navigator screen pops up.
Select the tables you want to import.
Your data is ready to be Loaded/Transformed.
What to note
Data from Google Sheets require transformation before analysis.
The data imports without headers and automatically adds an empty row between the header and the first rows. There are also empty columns. The index column in your google sheet is imported into Power BI as a column.
It is good practice to clean your data before analysis.
