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 many resources involved.
In this article, we’ll introduce the two most actionable options for you to consider.
How to export data from Google Sheets to Power BI
There is no native direct connection between Power BI and Google Sheets. However, you can use one of the following methods to do the job:
- Connect Google Sheets to Power BI with Coupler.io. Coupler.io is a reporting automation solution that provides a direct Google Sheets Power BI connector. It allows you to connect 60+ apps to several destinations including spreadsheets, data warehouses, and BI tools.
- 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 connect Google Sheets to Power BI using Coupler.io
To start using Coupler.io, click Proceed in the form below where we’ve preselected Google Sheets as a source and Power BI as a destination. You’ll be prompted to create a Coupler.io account for free.
Proceed with the setup which requires you to complete the following steps:
Step 1. Extract data
- Connect to your Google account.
- Select a spreadsheet and a sheet with the data to extract. Mind that you can select multiple sheets if you want to consolidate data from them into a single view.
Optionally, you can specify the range in the selected sheet to export data from.
Coupler.io allows you to create reports using data from multiple sources. I.e., you can extract data from different spreadsheets. For this, click on the Add one more source button and configure the connection accordingly.
We won’t do this now, so, let’s get to Transfrom data.
Step 2. Transform data
At this step, you can preview the data to load to Power BI and even transform it! Coupler.io allows you to
- Hide/unhide columns, rename them, and edit their types.
- Filter data by different criteria.
- Sort your data in ascending/descending order.
- Add new columns using the supported formulas.
Step 3. Manage data
Now it’s time to connect Google Sheets to Power BI. Follow the instructions in the wizard to make this happen:
- Save and Run the importer
- Copy the Power BI integration URL
- Go to Power BI desktop and navigate to Get data => Web.
- Insert the integration URL and click OK. This will open Power Query Editor with the data from Google Sheets.
Now you can manipulate the data if needed and load it to Power BI.
Step 4. Schedule importer
The last step is to automate the data flow from Google Sheets to Power BI. Go back to Coupler.io and toggle on the Automatic data refresh. This feature allows you to schedule data refresh according to the following parameters:
- Interval (up to every 15 minutes)
- Days of week
- Time preferences
- Time zone
Now your spreadsheet is connected to Power BI where you can visualize the data in the form of a report or dashboard.
Coupler.io also provides a gallery of preset dashboard templates with built-in connectors. These templates will facilitate your reporting and let you derive insights from your raw data with ease.
Import data from Google Sheets to Power BI
There is another solution that allows you to integrate with Google Sheets. It 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 a specific sheet to publish and click Publish.
- Copy the link to the published Google Sheets spreadsheet.
Step 2: Get data from the Google Sheets published link into Power BI
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.
- 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.
Check out our Power BI tutorial to learn more.
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 the Power BI service. We explained how to do this in our Power BI BigQuery connection guide.
FAQ #2: Will the dataset be refreshed automatically?
Importing data from Google Sheets to Power BI with Coupler.io can be automated on a custom schedule as we demonstrated above. You can always manually refresh the dataset by clicking the respective button on the Home ribbon.
FAQ #3: How to connect Power BI to Google Sheets?
Above we connected Power BI to Google Sheets using Coupler.io and it took around 3 minutes to complete. However, if you need to export data from Power BI reports to Google Sheets, you can only do this manually in two steps:
- Download a .csv or .xlsx file to your device
- Upload the file to Google Sheets
Step 1: Export Power BI to Google Sheets
- In Power BI Desktop or Power BI Service, choose a report to export data from, click on More Options, and select Export data.
- Choose which data you want to export:
- Data with current layout. You can export data in the same layout you see now without formatting only as an Excel file.
- Summarized data. You can export the summarized data in either Excel or CSV formats.
- Underlying data. You can export the raw data used to calculate the data in your visual.
Click Export => the file in the chosen format will be downloaded to your device.
Step 2: Bring data from Power BI to Google Sheets
- Open a Google Sheets file, go to File => Import.
- Click Upload and select the file exported from Power BI.
- Choose the desired Import location then click Import data.
That’s it. Your Power BI data is now available in Google Sheets.
FAQ #4: ?an you directly connect Power BI to Google Sheets?
Coupler.io is not the only way to connect Power BI to Google Sheets but it’s definitely one of the best.
At the same time, it is also possible to integrate GSheets and Power BI programmatically with the help of the Power BI REST API. However, this would require you to register and authorize an app for Power BI and write code in Apps Script, Python, or another language to send requests to the API.
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!
Automate data export with Coupler.io
Get started for free
