Power BI Get Data from Google Sheets

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 on FacebookReza Rad on LinkedinReza Rad on TwitterReza Rad on Youtube
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 nine 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.
His articles on different aspects of technologies, especially on MS BI, can be found on his blog: https://radacad.com/blog.
He wrote some books on MS SQL BI and also is writing some others, He was also an active member on online technical forums such as MSDN and Experts-Exchange, and was a moderator of MSDN SQL Server forums, and is an MCP, MCSE, and MCITP of BI. He is the leader of the New Zealand Business Intelligence users group. He is also the author of very popular book Power BI from Rookie to Rock Star, which is free with more than 1700 pages of content and the Power BI Pro Architecture published by Apress.
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.

6 thoughts on “Power BI Get Data from Google Sheets

  • Sorry for my English.
    With tables, everything is fine, if you do not merge them with other sources.
    The update does not work in the cloud.
    Invalid source combination.

    • Hi Valery
      What other sources are you combining the data into? are those on-premises? if yes, then you may need to enable the setting in the gateway that allows on-prem and cloud sources to be combined.

      • Hi Reza.
        The second source was SQL Server. Of course, it connects through a gateway. As long as the data is used independently, report updates perfectly. When I merge them together, the update stops working.

        • That is what I thought.
          You need to set a setting under the gateway to combine on-prem data and cloud-based data, read this article for more information
          Cheers
          Reza

  • Everything is fine, but for “Data source credentials”, it always expired after 1 hour, which cause schedule refresh failed, and I need to re-enter the credentials

Leave a Reply

%d bloggers like this: