Collecting google forms data and storing in SQL server

Introduction

In this blog, we will discuss how to create a google form and get the google form responses into the SQL server using python. Google forum is a service provided by Google to collect information through simple web forms. The biggest advantage of using google forums is, google takes all the pain to keep the data collected in a consistent manner, even when many people access the forum at a time. (Multiple concurrent sessions). Another useful feature of google forums is, the data is automatically synchronized with google sheets. This feature really makes things simple.

Creating Google Forums

The first step is to create a google forum. Creating a google forum is very intuitive. Go to Google Forums and create a new forum. The landing page of the google forum would be like this.

Once what needs to be collected is decided, create questions accordingly and name the form. On the responses tab, the responses are stored. On the right side of the screen, press the green button to store the data in the google sheets. Now, the responses are stored automatically in the google spreadsheets.

Authentication

The google sheet needs to stored in google drive. In this step, we try to get authentication to access the google drive. Google has many API's available on the developer's console. For our purpose, we need to enable Driver API.

Go to developers console and create a project.

Once the project is created, in the project enable google Drive API.

Click on Explore and enable APIs. In the API library, search for Driver API

Click on the Google Drive API and enable it. Once the Google Drive API is enabled, we need to obtain credentials. To obtain credentials, click on create credentials on the right side. We need to create a service account. The service account ID is for programming scripts.

Click on the service account and create a service account.

Fill in the display name and descriptions and create. Ensure, you generate a json key for the key type.

A JSON file would be downloaded. Make sure to save the JSON key. Finally, a service account is created.

On the service account page, copy the email id. We need this email Id in a bit.

Granting access for the service account to google sheet

So far, we have created a service account and enabled google drive API. Now we need to allow the google sheet to be accessed by the email ID the service account has. To grant access, visit your google drive and share the response sheet with the service account email Id. With this step, the initial set up is over.

Extracting data from google sheets through python.

Install gspread

pip install gspread

The gspread library allows easy access to read and write google sheets. The official documentation for here. Explore the documentation for various methods. We use the OAuth library to get a secure connection.

Try and understand the below code.

Thank you for reading. I hope, this is helpful to you.