Power BI can provide a wealth of business insights. However, you’ll need to import your data from different data sources first. You can use one of Power BI’s native connectors – unless you use Google Sheets. So, if you want to connect Google Sheets to PowerBI, you’ll need to look at a few other options.
How to Connect Google Sheets to Power BI Manually
Your first option would be to connect Google Sheets to Power BI manually.
Step 1: Publish Your Google Sheet as a Web Page
The first step in connecting Google Sheets to Power BI is to publish your Google Sheet as a web page:
- Share your sheet by going to “File”
- Select “Share” in the drop-down menu
- Set sharing options to “Anyone with the link can view”
- Click “Done”
Then, follow the next steps to connect Google Sheets to PowerBI:
- Click on “File”
- Select “Share”
- Click “Publish to the Web” from the drop-dwn menu
- Choose “Entire Document” and “Web page”
- Click “Publish”
On the window that opens, you can then copy the URL for your Google Sheet.
Step 2: Import Data into Power BI
With your URL generated and copied, you’ll be able to load your data into Power BI.
To do this, you’ll open Power BI Desktop and click on Get Data from the menu.
This will open a menu where you can select different options for importing data. Here, you’ll choose Web.
A window will open where you can paste the URL you copied earlier. Once done, you can click on OK.
In the next window, you can choose your level of access and then click on Connect.
Power BI will then connect to the web page and, once completed, will open the Navigator window. On this window, you’ll select the table from where you want to import the data and then click Load.
Once your data has been loaded into Power BI, you can also clean it by removing blank columns, changing the data format, and making sure the columns are named what you want them to be.
Is There a Better Way to Use Google Sheets for PowerBI?
If you’ve followed the process described above, you’ve probably realized that it can be quite inefficient and time-consuming.
Fortunately, there are other options.
You could, for instance, use a third-party connector to import your Google Sheet data into Power BI.
Another option is to use sheet.best. It allows you to create a REST API from your Google Sheet in seconds. You can then connect this API to Power BI to import your data.
Another benefit of using sheet.best is that once created, you can use your API for several other purposes, transforming it into the backend of your mobile and web applications or using it to turn your spreadsheet into a powerful database.
To learn more about sheet.best and how you can get the most value out of your spreadsheets, get started for free today.