Home » Google AppScript27 February 2017

Google AppScript: Create Form using FormApp class, Publish Form as WebApp, Email & Save Responses to Spreadsheet

This article shows:

1) How to create Google Form using AppScript’s “FormApp” class
2) Send Form URL to any email address using MailApp or GmailApp class
3) How to use Triggers in AppScript
4) How to send Form submit Responses to any email address
5) How to save Form submit Responses to any particular Google Spreadsheet

Create Google Form and email the Form URL

We create a Form with a text field, a date field and a text area.

Create Google Form as WebApp

About publishing your script as web app is given here: https://developers.google.com/apps-script/guides/web

To publish a script as a web app, the script needs either a doGet(e) or a doPost(e) function in it. Also, the doGet or doPost function should return either an HTML service HtmlOutput object or a Content service TextOutput object.

In the code below, we have a doGet() function.

In this doGet() function:

1) We create a Form with a text field, a date field, a text area and a select list.

2) We populate the select list with data from a particular Google Spreadsheet. The spreadsheet contains list of district names. We fetch the distric names from the spreadsheet using a getDistricts() function and add those district names as option values of the select list in the form.

Below is the sample of the Spreadsheet containing data about Districts. The districts name are present in the 2nd column of the spreadsheet. We have to keep this in mind while fetching data from the spreadsheet.

ZoneDistrict
MechiJhapa
KoshiIllam
SagarmathaMorang
Sunsari
Siraha
Saptari

3) We also allow the form response to be editable using setAllowResponseEdits(true) function. When a user submits the form, he/she will see an ‘Edit response’ link in the confirmation page. Users can edit their response by clicking on that link.

4) We create a new Trigger named formSubmitReply and set it to be created/called after the form is submitted.

5) Before creating the trigger, we call deleteTrigger() function which deletes the previously created forSubmitReply trigger.

Triggers let App Script run functions at occurence of certain event like opening a document, submitting a form, etc. Our onFormSubmit Trigger will run the function named forSubmitReply(e).

This forSubmitReply(e) function:-

6) Fetches the submitted responses
7) Sends email to a particular email address with the response data
8) Opens a particular Google Spreadsheet and saves response data into it

9) Finally, HTMLService class is used create and serve HTML output.

When we publish our script as web app, the doGet() function of our script will run automatically. So, it will run from Step #1 to #9 as mentioned above.

To get the spreadsheed id:

– Open Google Spreadsheet
– See the URL in your browser’s address bar
– The URL will be something like this: https://docs.google.com/spreadsheets/d/9tXNUNpM9tXNUNp0BydbYIIYLMxxxxxxxxxx/edit#gid=0
– So, in this case, the spreadsheet id is “9tXNUNpM9tXNUNp0BydbYIIYLMxxxxxxxxxx”

We save the form response data into an already existing spreadsheet. This spreadsheet will contain the header row with five columns:

Timestamp Name District Birth Date Message

After we submit the form, the submitted data will be saved in the spreadsheet in the following format:

TimestampNameDistrictBirth DateMessage
2016-11-01 11:03:54TomLalitpur1984-12-31test 1
2016-11-01 11:05:29JohnBhaktapur1990-10-11test 2
2016-11-01 11:06:35HarryKathmandu1980-12-15test hello 3
2016-11-01 11:07:00SallyKathmandu1974-01-04ktm nepal 4

Hope this helps. Thanks.

Google AppScript

Get New Post by Email

Find me on

FacebookTwitterGoogle+LinkedInRSS Feed

Comments are closed.