The Power BI desktop app provides several native connectors to different data sources, including Excel files, BigQuery, Google Analytics, QuickBooks Online, etc. However, you won’t see Google Sheets on this list. Nevertheless, it’s very doable to import Google Sheets into Power BI without much resources involved. In this article, we’ll introduce the two most actionable options for you to consider.
How to get data from Google Sheets to Power BI
There is no direct connection between Power BI and Google Sheets, but there are two workarounds to do the job:
- Import data from an Excel workbook. In this case, you’ll need to synchronize your Google Sheets spreadsheet with your Excel workbook stored on OneDrive and then import data from it.
- Import data from a web page. In this case, you’ll need to publish your Google Sheets spreadsheet as a web page and then import data from it.
Let’s check out both solutions in detail.
How to integrate Google Sheets with Power BI using Excel
The first option includes two steps:
- Connect Google Sheets to Excel
- Connect Power BI and Excel
Google Sheets -> Excel -> Power BI
Step 1: Connect Google Sheets to Excel
Note: works for Excel files only stored on OneDrive.
This can be easily done with Coupler.io, a solution for the automated data import from multiple sources to Excel, Google Sheets, or BigQuery on a schedule. In our case, Google Sheets is the data source where we will take data, and Excel is the data destination to load data to. Sign in to Coupler.io using your Google account and click “Add new importer“. Then take the following steps:
Set up source (Google Sheets)
- Choose Google Sheets as a source application.
- Connect to your Google account
- Select your Google Sheets spreadsheet and a sheet to load data from.
- Optionally you can select a specific range to import.
Set up destination (Excel)
- Choose Microsoft Excel as a destination application.
- Connect to your Microsoft account.
- Select an existing Excel Workbook on OneDrive and a worksheet to load data to.
- Optionally you can select the first cell where to import your data from Google Sheets.
- Select an Import mode:
- Replace – to fully replace all data on the sheet
- Append – to add new data below the existing data on the sheet.
Set up schedule
The last step is to enable the Automatic data refresh and customize the schedule for syncing data between Google Sheets and Excel.
Click “Save and Run” when you’re ready, and you will have your Google Sheets records in Excel.
Now, we need to link this Google Sheets integration to Power BI.
Step 2: Connect Power BI and Excel
On the Home ribbon of your Power BI desktop app, click “Excel Workbook“.
Select the Excel file from your OneDrive folder. Then, in the Navigator window, select the worksheet to load data from and click “Load“.
There you go!
Read our detailed guide on how to connect Excel to Power BI.
Now let’s answer a few questions you may have.
FAQ #1: Can I connect Power BI service and Excel?
Power BI service seems to provide a direct connection to OneDrive. And, apparently, users can easily connect their Excel files on OneDrive to Power BI. However, we failed to do this. Here is the flow we used:
- Click “Get data“, then click “Get” in the Files block.
- Select OneDrive Personal or OneDrive Business depending on the Microsoft account you used for syncing Google Sheets and Excel.
- Choose the Excel file on the OneDrive folder and click “Connect“.
And here is the result:
So, we recommend you load data from Excel files to the Power BI desktop. Then you’ll be able to publish your dataset to make it accessible via Power BI service. We explained how to do this in our Power BI BigQuery connection guide.
FAQ #2: Will the dataset be refreshed automatically?
Import data from Google Sheets to Excel can be automated on a custom schedule. Power BI automatically refreshes data from Excel on OneDrive about every hour. You can always manually refresh the dataset by clicking the respective button on the Home ribbon.
Now, let’s check out the second solution for using Google Sheets with Power BI
Import data from Google Sheets to Power BI
Another solution for getting data from Google Sheets to Power BI consists of these two steps:
- Publish a Google Sheets spreadsheet to the web.
- Get data from the Google Sheets published link into Power BI.
Step 1: Publish Google Sheets to the web
- In your Google Sheets file, go to File => Publish to the web.
- Select the entire document or specific sheet to publish and click “Publish“.
- Copy the link to the published Google Sheets spreadsheet.
Now you can share your Google Sheets with Power BI.
- For this, in your Power BI desktop app, go to Get data => Web.
- Insert the link to your published Google Sheets doc and click “OK“.
- In the next window, you need to choose how to access the web content: anonymously, using credentials, API key, or organizational account. The easiest way is to choose the anonymous access and click “Connect“.
- After the connection to the web page is established, the Navigator window will open. Since our Google Sheets spreadsheet has several sheets and we published the entire document, Navigator displays a set of HTML tables fetched from the web page, as well as a set of suggested tables based on the fetched HTML content.
- The Table 1 corresponds to the Sheet that we want to import to Power BI. So, we select it and click “Load“.
There you go! Your Google Sheets records are imported into Power BI.
However, in the imported dataset, the column headers have not been recognized, and three new rows were added from the top. This can be fixed before loading data if you click “Transform Data“. A Power Query Editor will open where you will be able to tidy up your table.
Click the “Save” icon in the top left and apply the changes you made. Now your Google Sheets table looks right in Power BI.
Which is the best way to connect Power BI and Google Sheets
Since we introduced two methods for importing Google Sheets to Power BI, a logical question arises: Which method is the best one? The method of publishing Google Sheets to the web seems to be the easiest one. However, this makes your data publicly accessible. Besides, you’ll have to use Power Query Editor to transform your data before loading it to Power BI.
In the case of the Coupler.io method, the flow looks quite smooth. However, the data won’t be refreshed live, but only on a schedule that you’ve set up (hourly, daily, etc.).
We know that you’ll choose the best option for your purposes. Good luck with your data!Back to Blog