Power BI Get Data from Google Sheets

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.

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.

Summary

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.

Reza Rad

Trainer, Consultant, Mentor

Reza Rad is a Microsoft Regional Director, an Author, Trainer, Speaker and Consultant. He has a BSc in Computer engineering; he has more than 20 years’ experience in data analysis, BI, databases, programming, and development mostly on Microsoft technologies. He is a Microsoft Data Platform MVP for 12 continuous years (from 2011 till now) for his dedication in Microsoft BI. Reza is an active blogger and co-founder of RADACAD. Reza is also co-founder and co-organizer of Difinity conference in New Zealand, Power BI Summit, and Data Insight Summit.
Reza is author of more than 14 books on Microsoft Business Intelligence, most of these books are published under Power BI category. Among these are books such as Power BI DAX Simplified, Pro Power BI Architecture, Power BI from Rookie to Rock Star, Power Query books series, Row-Level Security in Power BI and etc.
He is an International Speaker in Microsoft Ignite, Microsoft Business Applications Summit, Data Insight Summit, PASS Summit, SQL Saturday and SQL user groups. And He is a Microsoft Certified Trainer.
Reza’s passion is to help you find the best data solution, he is Data enthusiast.
His articles on different aspects of technologies, especially on MS BI, can be found on his blog: https://radacad.com/blog.