Google Form

Idea: Using Google API Console to collect information from Google Form

Step 1: Create a Google Service Account

Go to Google API Console and create a new project. Use this link for more details.
Under the new project, create a new service account key with type JSON. Use this link for more details.
Save this JSON key with name google_key.json, which will be used later for the automation script.
Sample google_key.json file

Step 2: Connect to Google Form

Create a Google Form and choose the response output.
Go to the output Google Sheet and share it with the service account email, which can be found in the google_key.json with the key client_email Use this link for more details.

Step 3: Install requirements

We will use Python 2.7 for our script. If you don't have it, please install as following
We also need pip for package management. If you don't have it, please install as following
Use pip to install two required packages: oauth2client==1.5.2 and gspread==0.6.2

Step 4: Writing script

There are 3 main parts on the script.

Read credentials from the google_key.json created in step 1.

with open('google_key.json') as f:
    config = json.load(f)

creds = SignedJwtAssertionCredentials(
    config['client_email'],
    config['private_key'],
    ['https://spreadsheets.google.com/feeds']
)
return gspread.authorize(creds)

Read the responses row-by-row and process it.

 gc = get_google_client()

 # Open Google Sheet file: replace Sheet ID with actual ID of the Sheet
 doc = gc.open_by_key('Sheet ID')

 # Read Google Sheet tab: replace Tab name with actual name of the tab on the Sheet
 sheet = doc.worksheet("Tab name")

 row_count = sheet.row_count
 # run through all rows of the Google Sheet
 for row in range(2, row_count + 1):
    values = sheet.row_values(row)
    # Blank row
    if not values[0]:
        break

     # process the values collected from Google Sheet
     process_value(values)

Depending on your application, you may process the data accordingly. In our case, we create group responses by projects and write it to the markdown file.

Sample code

Download sample code with full documentation.

Contact

If you have any question about the above process, contact Duong Do for explanation.
We also welcome for any recommendation for our project. Contact at the same address

How To