2020-12-14-Importing-Google-Form-Data-PowerBI.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Importing Google Form Responses in Power BI\n",
    "> Here is a quick way to import responses submitted in Google Form on a refresh schedule.\n",
    "\n",
    "- toc: true \n",
    "- badges: true\n",
    "- comments: true\n",
    "- categories: [Power BI, Google Form]\n",
    "- hide: false"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Importing Data from Google Form"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "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.\n",
    "\n",
    "With this method you can refresh the Power BI dashboard and pull the latest responses from Google Form.\n",
    "\n",
    "Note that Microsoft has [Microsoft Form](https://forms.office.com/) 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."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Steps\n",
    "\n",
    "- Open the Google Form in edit mode and click on *Responses*\n",
    "\n",
    "![1](https://raw.githubusercontent.com/pawarbi/blog/master/images/form1.JPG)\n",
    "\n",
    "\n",
    "- Click on *View Responses in Sheets*\n",
    "\n",
    "![2](https://raw.githubusercontent.com/pawarbi/blog/master/images/form2.JPG)\n",
    "\n",
    "\n",
    "- File > Publish to the Web\n",
    "\n",
    "![4](https://raw.githubusercontent.com/pawarbi/blog/master/images/form4.JPG)\n",
    "\n",
    "- Either create a new sheet or select an existing sheet.\n",
    "\n",
    "![3](https://raw.githubusercontent.com/pawarbi/blog/master/images/form3.JPG)\n",
    "\n",
    "\n",
    "- 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*\n",
    "\n",
    "![5](https://raw.githubusercontent.com/pawarbi/blog/master/images/form5.JPG)\n",
    "\n",
    "![6](https://raw.githubusercontent.com/pawarbi/blog/master/images/form6.JPG)\n",
    "\n",
    "\n",
    "- Open Power BI Desktop and select Text/CSV and enter the URL generated above\n",
    "\n",
    "![7](https://raw.githubusercontent.com/pawarbi/blog/master/images/form7.JPG)\n",
    "\n",
    "\n",
    "\n",
    "- Import the data, build the dashboard and publish to Power BI service\n",
    "\n",
    "![8](https://raw.githubusercontent.com/pawarbi/blog/master/images/form8.JPG)\n",
    "\n",
    "\n",
    "- To schedule the refresh, for Data Source Credentials, choose *Authentication Method* as Anonymous & *Privacy Level* as None\n",
    "\n",
    "![9](https://raw.githubusercontent.com/pawarbi/blog/master/images/form9.JPG)\n",
    "\n",
    "\n",
    "- Submit a new test response and refresh the dataset in Power BI service to make sure dashboard is pulling the latest data."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.6.10"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}