Are you using the powerful data analysis tool, Power BI, but wondering how to connect it to Google Sheets? You’ve searched the list of native connectors but found that Google Sheets is not included. Don’t worry! We will introduce you to the two most effective options for making this connection.
Exporting Data from Google Sheets to Power BI
Although there is no direct connection between Power BI and Google Sheets, you can use one of the following two methods:
1. Connect Google Sheets to Power BI with Coupler.io: Coupler.io is a report automation solution that offers a direct connection between Google Sheets and Power BI.
2. Import data from a web page: In this case, you will need to publish your Google Sheets spreadsheet as a web page and then import the data from that page.
Now let’s take a closer look at these two solutions.
How to Connect Google Sheets to Power BI with Coupler.io
Coupler.io is a data automation and analysis platform that turns data into meaningful reports. It allows you to connect over 50 applications to multiple destinations, including spreadsheets, data warehouses, and analytics tools. Let’s see how you can use Coupler.io to connect Google Sheets to Power BI and automate the data flow.
To get started with Coupler.io, create a Coupler.io account. You can sign up for free and enjoy a 14-day trial (no credit card required). Then, click on Add Importer and select the necessary source and destination applications: Google Sheets and Power BI.
Follow the configuration process, which involves the following steps:
Step 1. Extract the data
- Sign in to your Google account.
- Select a spreadsheet and a sheet containing the data to extract. You can select multiple sheets if you want to consolidate the data into a single view.
Optionally, you can specify the range within the selected sheet from which to export the data.
Coupler.io allows you to create reports using data from multiple sources. So, you can extract data from different spreadsheets. To do this, click on the Add another source button and configure the connection accordingly.
Now, let’s move on to Step 2: Transform the data.
Step 2. Transform the data
At this step, you can preview the data to load into Power BI and even transform it! Coupler.io allows you to:
- Hide/unhide columns, rename them, and change their data types.
- Filter the data based on different criteria.
- Sort the data in ascending/descending order.
- Add new columns using supported formulas.
Step 3. Manage the data
It’s time to connect Google Sheets to Power BI. Follow the assistant’s instructions to do so:
- Save and run the importer.
- Copy the Power BI embed URL.
- Open Power BI Desktop and go to Get Data => Web.
- Insert the embed URL and click OK. This will open Power Query Editor with the Google Sheets data.
You can now manipulate the data if necessary and load it into Power BI.
Step 4. Schedule the importer
The final step is to automate the data flow from Google Sheets to Power BI. Go back to Coupler.io and enable Automatic Data Refresh. This feature allows you to schedule data updates based on the following parameters:
- Interval (up to every 15 minutes).
- Days of the week.
- Preferred times.
- Time zone.
Your spreadsheet is now connected to Power BI, where you can visualize the data as a report or a dashboard.
Coupler.io also offers a gallery of pre-built dashboard templates with built-in connectors. These templates will make it easier for you to create reports and extract insights from your raw data.
Importing Data from Google Sheets to Power BI
Another solution allows you to integrate Google Sheets into Power BI. It involves the following two steps:
- Publish a Google Sheets Spreadsheet to the Web:
- In your Google Sheets file, go to File => Publish to the Web.
- Select either the entire document or a specific sheet to publish and click Publish.
- Copy the link to the published spreadsheet on Google Sheets.
- Get the Data from the Published Link on Google Sheets into Power BI:
- In the Power BI Desktop application, go to Get Data => Web.
- Insert the link to your published Google Sheets document and click OK.
- In the next window, you need to choose how to access the web content: anonymously, using your credentials, an API key, or an organizational account. The simplest way is to choose anonymous access and click Connect.
- Once the connection to the web page is established, the navigation window will open. Since our Google Sheets spreadsheet has multiple sheets, and we published the entire document, the browser displays a set of extracted HTML tables from the web page, as well as a set of suggested tables based on the extracted HTML content.
The first table corresponds to the sheet we want to import into Power BI. We select it and click Load.
Your Google Sheets records are now imported into Power BI.
However, in the imported dataset, the column headers were not recognized, and three new rows have been added from the top. This can be fixed before loading the data by clicking on Transform Data. Power Query Editor will open, allowing you to organize your table.
Click on the Save icon in the top-left corner and apply the changes you made. Your Google Sheets table is now displayed correctly in Power BI.
FAQ #1: Can I connect Power BI and Excel?
It seems that Power BI offers a direct connection to OneDrive, allowing users to easily connect their Excel files on OneDrive to Power BI. However, we have not been able to do so. Here is the flow we used:
- Click on Get Data, then on Get in the Files section.
- Select either Personal OneDrive or Business OneDrive depending on the Microsoft account you used to sync Google Sheets and Excel.
- Choose the Excel file in the OneDrive folder and click Connect.
And here is the result:
We recommend loading data from Excel files into Power BI Desktop instead. Then, you can publish your dataset to make it accessible via the Power BI service. We have explained how to do this in our Power BI to BigQuery connection guide.
FAQ #2: Will the dataset data be automatically refreshed?
Importing data from Google Sheets to Power BI with Coupler.io can be automated according to a custom schedule, as demonstrated earlier. You can also manually refresh the dataset data by clicking on the corresponding button in the Home ribbon.
FAQ #3: How to connect Power BI to Google Sheets?
We connected Power BI to Google Sheets using Coupler.io, and it took us about 3 minutes. However, if you want to export data from Power BI reports to Google Sheets, you can only do it manually in two steps:
- Download a .csv or .xlsx file to your device.
- Import the file into Google Sheets.
Step 1: Export Power BI to Google Sheets.
-
In Power BI Desktop or Power BI Service, choose the report from which you want to export the data, click on More options, then select Export Data.
-
Choose the data you want to export:
- Data with the current layout: You can export the data with the same layout you currently see, without formatting, only as an Excel file.
- Summary data: You can export summary data in either Excel or CSV format.
- Underlying data: You can export the raw data used to calculate your visualization data.
-
Click on Export => the file in the chosen format will be downloaded to your device.
Step 2: Import Power BI data into Google Sheets
- Open a Google Sheets file, go to File => Import.
- Click Upload and select the exported file from Power BI.
- Choose the desired import location, then click Import data.
And voila, your Power BI data is now available in Google Sheets.
FAQ #4: How to directly connect Power BI to Google Sheets?
Coupler.io is not the only method to connect Power BI to Google Sheets, but it is definitely one of the best.
At the same time, it is also possible to programmatically integrate GSheets and Power BI using the Power BI REST API. However, this would require signing up and authorizing an application for Power BI, then writing 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?
Now that we have presented two methods for importing data from Google Sheets to Power BI, a logical question arises: which method is the best? The Google Sheets publishing method seems to be the simplest. However, it makes your data accessible to the public. Moreover, you will need to use Power Query Editor to transform your data before loading it into Power BI.
In the case of the Coupler.io method, the process is quite smooth. However, the data will not be updated in real-time but only according to a schedule you have defined (hourly, daily, etc.).
We know you will choose the best option for your needs. Good luck with your data!
Article written for Crawlan.com.
