This is the second part in a series of articles on integrating with Microsoft Power BI. Please read the introduction before reading this part. We deal with the simplest case here, using Google Sheets for connecting to Power BI.
As you may already know, SurveyCTO supports Publishing data to Google Sheets, opening a wide range of opportunities to work with your data. One of them is integration with Power BI. If you’re looking for a simple alternative to monitor or analyze your incoming data with Power BI, then this connection may be the best fit for you.
|If you’re working with an encrypted form, you will not be able to publish your data. In this case, you can either publish a subset of your data by configuring some fields as publishable, or explore other possible integrations with Power BI via the API, which can return encrypted data.|
In Google Sheets…
- Click on File > Publish to the Web.
- Choose to Publish the Entire Document as Microsoft Excel (.xlsx)
- Click Publish and then OK to any Google pop-up
- Copy the URL Link generated
In Power BI Desktop..
- Select Get Data from the Introduction Window. Otherwise, go to the Home tab and click on Get Data (select the symbol above, not where it says Get Data with the arrow).
- Click on Other and choose Web, then Connect:
- Paste the URL link generated in step 4 and click OK.
- Select your Data and click on Load.
- From here, you’ll be able to create Reports and Dashboards using the dataset imported via Google Sheets, published as an Excel workbook. Whenever you want to refresh your tables and graphics so that they’re all illustrating the latest data available on the Server, click on Refresh in the menu bar:
Clicking Refresh will refresh the URL link connected, which will get the latest data in your Google Sheet. Your dataset will be updated, as well as all associated graphics and tables. You can learn more about refreshing in these help topics from Microsoft:
- Refresh a dataset from a local Power BI Desktop file
- Refresh a dataset from a cloud Power BI Desktop file
There are three other options that we have documented for integrating with Power BI. Click here to view part three of this guide on integration using Excel.