Power BI can get data from Google Sheet now. This functionality is released just yesterday and announced in both Power BI and Power Query blogs. The feature is still preview (Beta) but it is worthwhile looking at how it works in a quick article and video.
Google Sheet’s URL
To get data from a Google Sheet, you need the Sheet URL, which you can get from the browser’s address bar when you have the Google Sheet open. Just copy the URL from the address bar.
Now that you have the URL, you can use it in the Get Data window of the Power BI Desktop
Get Data from Google Sheet
Use the Get Data in the Power BI Desktop and select More;
Now search for Google Sheet;
You will be asked for the URL of the Google Sheet (the one you copied from the browser’s address bar in the previous step);
If this is the first time you are connecting to this Google Sheet from Power BI, then you need to sign in,
Continue the sign-in process in the browser
and once done, you see this message;
You can close the browser tab if you want, and get back to Power BI Desktop
You should see that you are currently signed in now, and you can connect. This will bring the Navigator window and you will see all the sheets in that Google Sheet.
The rest of the steps looks very much like getting data from Excel in Power BI. Choose the sheet(s) you want and go to the next step (either Load or Transform Data). I strongly suggest to Transform Data always so that you can get the data prepared with things such as Use First Row As Headers and filtering data etc before loading it into Power BI.
Power BI Service and Schedule Refresh
Once you visualize the data, you can then publish it to the Power BI Service and set up a scheduled refresh.
The Scheduled refresh will be an easy setup because you don’t need Gateway to connect to the Google Sheet. All you need to set up is the credentials and login for the GoogleSheets connector.
And then you can schedule it to refresh;
As you see, this does not only work in Power BI Desktop, but it also works in the service once you publish the dataset, and it won’t need a gateway. Now, let’s learn about it a bit more.
Limitations and upcoming changes
This connector is just released yesterday and it is in Beta edition yet. We expect a lot of improvements for it in the upcoming months and many of the limitations below might be lifted by then. At the moment, however, these are known limitations;
- If you want to connect to multiple Google Sheets you need to sign in for each of the individually, as they use different URLs.
- At the moment this connector is not available in Dataflow. But the expected timeline is mid-2022 for it.
- This won’t work for Get data from Folder. You cannot just use a Google Drive folder path and get all the Sheets from it. They have to be populated one by one.
- There will be an ability to BROWSE for the Google Sheet in the future from Power Query. No timeline for that yet.
- to learn more about limitations and future features, read this article from the Power Query team.
The ability to connect to the Google Sheets from Power BI has been one of the most wanted features, and the idea had over 3,000 votes. From my point of view, this enables many users with their data in Google Sheets to be able to use Power BI in an automated way. Special thanks to the Power Query team for building the connector. Like many other preview features, there are limitations, however, we can expect many of them to be lifted in the upcoming months. Let me know your experience with the Google Sheet connector in the comments below.