How to Connect Google Sheets to Power BI?

Google Sheets is a cloud-based spreadsheet application provided by Google. It is a good tool for data storage and data analysis. Google Sheets can be used for free. You can open Google Sheets on a web browser of your choice and enjoy the full functionality of a spreadsheet application for doing data analysis tasks. However, Google Spreadsheets doesn’t come equipped with all the tools that you may need for advanced data analysis. 

That’s why it will be prudent for you to connect Google Sheets to Power BI, a more advanced data analysis tool. Microsoft Power BI is a great tool for analyzing huge amounts of data. You can use it to pull in data stored in different data sources, including Google Sheets. Before pulling in data from Google Sheets to Power BI, you have to connect Google Sheets to Power BI. Once the connection is established, you can extract data from Google Sheets into Power BI for advanced analysis. 

How to Access and Use Google Sheets

Google Sheets allows you to create and format spreadsheets and collaborate with other people online. 

To access Google Sheets, follow the steps given below:

Step 1: Open the following URL on your web browser:

https://www.google.com/sheets/about/

Step 2: The following web page will be opened:

Step 3: Scroll down on the web page to the “Get a head start with templates” section. 

The “Go to Google Sheets” button will appear on the top right corner of the window. Click it. 

Step 4: If you are already logged into your Gmail/Google account on the web browser, you will be taken to the Google Sheets Home screen. If you’re not logged in, you will be prompted to log in. Just log in using your Gmail/Google account and you will be taken to the Google Sheets Home screen that looks as shown:

Congratulations! You have logged into your Google Sheets account. 

The window shows a number of sample Google Sheets. If you open any of them, you will know what a Google Sheet looks like. You will also know the kind of data that you can use to create a Google Sheet. All the spreadsheets that you have created will also be shown on the window. For each spreadsheet, you are given the name, the owner and when it was created or last modified. 

If you need to create a new Google Sheet, just click the “ + Blank” button. 

You will be taken to an unnamed spreadsheet.

To give it a name, type the name on the “Untitled spreadsheet” section as shown below:

You can begin to type in your data inside the cells of the spreadsheet. 

If you click the “File” button from the menu, you will see the “Import” button that will allow you to import data into your spreadsheet.  

You can import data from your Google Drive, upload it from your computer or select data that has been shared with you by other users on Google Sheets. 

This is shown in the following image:

How to Use Microsoft Power BI

Power BI is a tool for data modelling and visualization. It was created by Microsoft. Today, companies generate huge amounts of data. The work of Data Analysts is to extract insights from such data to assess business processes and come up with IT-based solutions. If the data is voluminous, the work of Data Analysts will be challenging. The data may span millions of rows in a spreadsheet, and the Data Analysts may take days or even weeks to analyze the data. 

That is why Microsoft developed the Power BI tool. It makes the work of developers, analysts and business users simple. To perform analysis on Power BI, you need data. The good thing about this tool is that it allows you to pull in data from different data sources. After pulling the data into Power BI, you can create beautiful reports and put together millions of data pieces in minutes. You can also visualize the insights that you extract from your data. 

How to Connect Google Sheets to Power BI

It is possible for you to move your data from Google Sheets into Power BI. First, you should publish your Google Sheets as a web page. You can then enter the data into Power BI using the URL of the web page. 

Let’s discuss these steps one by one for Google Sheets to Power BI data transfer. 

Step 1: Publish your Google Sheets as a Web Page

Do the following:

Step A: You should first share your document. So, click the “Share” button located in the top right corner of your Spreadsheet. 

Step B: A new window will pop up. Choose “Anyone on the internet with this link can view”. Then, click the “Done” button. 

Step C: Click the “File” option from the menu then choose “Publish to the web”. 

Step D: A new window will pop up. Choose “Entire Document”, “Web page” then click “Publish”. 

Step E: A window may appear asking you whether you want to publish the selection. Just click “OK”. 

Step F: A window will appear showing the URL of the document. Just copy the URL. You can paste the URL on a text editor such as Notepad. 

You will have published the Google Sheet as a web page. 

Next, you will learn how to enter data into Power BI. 

Step 2: Entering Data into Power BI

We can load the data from the web page into Power BI. 

Follow the steps given below:

Step A: Open Power BI Desktop. Create a new file. 

Step B: Click the “Get Data” button from the menu. 

Step C: You will be presented with options showing the possible sources you can get your data from. Choose “Web”. 

Step D: A small window will pop up giving you some space to paste your URL. Paste the URL that you copied when creating a webpage from your Google Sheet. Click the “OK” button. 

Step E: In the next window, choose the level of access to apply the settings, then click the “Connect” button. The following window will appear, showing that Power BI is trying to connect to the web page:

Step F: The “Navigator” window will popup. Check the table where the data lies then click the “Load” button. 

The table will be loaded into Power BI and you will see the columns under the “Fields” section of Power BI window. 

Step 3: Cleaning Data

The column names aren’t what we want them to be, there are blank columns, and the column data types are string by default.

Step A: To open the Query Editor, go to the top menu and select “Edit queries.”

Step B: Select the “Exercise“-table from the drop-down menu.

Step C: In the top menu, select “Use First Row As Headers.” A new row “Promoted Headers” will emerge in the “Applied steps” panel, as you may have noticed. You can easily remove steps here if you make a mistake.

Step D: Remove any null columns to the right of the first column, which is empty, and the second column, which has row numbers.

Step E: Decimals are correct. If any of your columns contain decimal numbers, you may need to perform a simple replace-action. (In Google, the ‘.’ (dot) is used, whereas in Power BI, the ‘,’ is used) (coma). In other language editions, this could be different. In any case, all decimals with dots must be converted to commas. Select the column(s) containing decimal numbers. Select the “Transform” tab at the top of the page and then “Replace Values.” Replace the ‘.’ with a ‘,’. 

It’s worth noting that the Data key-column should be a whole number as well. The Date Key column in the Time-table should be a Whole number, but the Date column with a true date-format should be format Date. Year, Month, and Day should all be text fields, while offset-fields should be whole numbers.

Limitations of Manual Method

From the steps we have followed above to get data from Google Sheets into Power BI, you can tell that it was a lengthy process. We had to publish the data from Google Sheets into a web page and then connect to the web page from Power BI. Again, the idea of publishing data into a web page is not recommended when you are dealing with sensitive data because of security concerns. 

Conclusion

In this article, you have not only learnt how to use Google Sheets and Microsoft Power BI but also how to connect Google Sheets to Microsoft Power BI. You are now also aware of the limitations this process entails. In case you want to ingest data from Google Sheets or Power BI into your desired Database/destination, then Hevo Data is the right choice for you! 

Simplify your data analysis with Hevo today and Sign up here for a 14-day free trial.

FAQ