Importing Data from Google Form

In this short blog post, I will show how to import responses received in Google Form in Power BI. This will require you to publish the responses to web. Note that this does not mean the data could be searched via a search engine. But if someone has a link to the published data, they will be able to see it (not edit it) and use it. You can always disable the publishing by clicking Stop Publishing in Publish to Web option.

With this method you can refresh the Power BI dashboard and pull the latest responses from Google Form.

Note that Microsoft has Microsoft Form which is free but as far as I know it does not allow publishing the form response data to web. If you have confidential data, Microsoft Form is a better option as Power BI will need the access to Microsoft Form to be authenticated before importing the data. However, in my case, I was working with a nonprofit organization to analyze their survey responses so Microsoft Form was not an option. For more complex workflows ,and large data, I advise using Microsoft Form, Power Automate and SharePoint together.

Steps

  1. Open the Google Form in edit mode and click on Responses

1

  1. Click on View Responses in Sheets

2

  1. File > Publish to the Web

4

  1. Either create a new sheet or select an existing sheet.

3

  1. Under Link, select Form Responses 1, select Commas separated values .csv. Select Publish.Copy the URL generated and inspect it to make sure it ends with gid=123456789&single=true&output=csv*

5

6

  1. Open Power BI Desktop and select Text/CSV and enter the URL generated above

7

  1. Import the data, build the dashboard and publish to Power BI service

8

  1. To schedule the refresh, for Data Source Credentials, choose Authentication Method as Anonymous & Privacy Level as None

9

  1. Submit a new test response and refresh the dataset in Power BI service to make sure dashboard is pulling the latest data.