Google Form to Backend (CSV/Google Apps Scripts)

Hi, hope everything is ok !

In this story, we will learn how we can get responses from Google Form and pass it to our Backend application.

We can achieve this with two methods:
1. Export CSV of Google Form Response and Upload to Backend application.
2. Using Google Apps Scripts.

Method 1: CSV Export

It’s a manual process. After user responses are collected in Google Form, you can export responses as CSV.

You have to create API to upload CSV.

Method 2: Google Apps Scripts

Google allows us to run Scripts in Google Form and their other applications.

More: https://developers.google.com/apps-script

Let’s get started.

1. Create a new Google Form or use existing ones.

To create new Google Form, follow: https://docs.google.com/forms/u/0/

2. Open Script Editor.

After you have created Google Form with required details, you need to open “Script editor”. “Script editor” can be found on top right corner, besides profile image, on clicking more options (three vertical dots).

3. Write Script to call Backend API on submitting Form.

Add Script function as below:

To know more on UrlFetchApp to make API calls: https://developers.google.com/apps-script/reference/url-fetch/url-fetch-app

Google Apps Scripts has made the switch to Chrome V8 JavaScript Engine. You can now write your Google Scripts and GSuite addons directly in ES6 but with a few caveats.

4. Run the function in Script.

Save the script to enable run, debug options.

To run the functions in Script, you have to provide permission.

You might encounter this, but worry not.
Click on “Advanced”, click “Go to {App Name} (unsafe)” and allow.

Select the function to run.
On running, you will see in Execution log:

It’s because “e” param with response on function is missing.

5. Add Trigger to invoke this function on Google Form actions.

Click on Triggers (Clock icon) and click on “+ Add Trigger” on bottom right.

Configure trigger to run function and save.

Click on “Save”. This will ask for permission as before. Allow it to create Trigger.

6. Share Google Form.

You can now share your Google Form.
Once User submits the form, you can see the log on “Executions”.

7. Write script that works best for your application.

Here is my final script:

Backend API is setup such that it accepts application/json on POST request.

To test out the script,
Use https://ngrok.com/ to generate url for your localhost application.

Happy Learning !!