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 Form using FormApp class
 * Send form URL to email address using GmailApp class 
 */
function myFunction() {  
  var form = FormApp.create('Form Example');
  form.addTextItem().setTitle('Your Name').setRequired(true);
  form.addDateItem().setTitle('Birth Date').setRequired(true);
  form.addParagraphTextItem().setTitle('Your Message')
                             .setRequired(true);
  form.setTitle('Feedback Form')
      .setConfirmationMessage('Thanks for your feedback !!');
  
  var url = form.getPublishedUrl();
  GmailApp.sendEmail('your.name@example.com', // YOUR EMAIL ADDRESS
                     'Hello', 
                     'Here is the form URL: ' + url);  
}

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.

Zone	District
Mechi	Jhapa
Koshi	Illam
Sagarmatha	Morang
	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:

Timestamp	Name	District	Birth Date	Message
2016-11-01 11:03:54	Tom	Lalitpur	1984-12-31	test 1
2016-11-01 11:05:29	John	Bhaktapur	1990-10-11	test 2
2016-11-01 11:06:35	Harry	Kathmandu	1980-12-15	test hello 3
2016-11-01 11:07:00	Sally	Kathmandu	1974-01-04	ktm nepal 4

/**
 * To publish script as web app, we need a doGet() function 
 * This function:
 * Creates Form
 * Fetches district names from Spreadsheet and popular select list
 * Allows form response to be editable by submitting user
 * Deletes previously created triggers
 * Creates a new Trigger on form submit
 * The new trigger calls formSubmitReply function
 * which fetches the form submit responses 
 * and send those reponses to a particular email address
 * and save the responses to a particular spreadsheet
 * Finally, creates Html Output using HtmlService class 
 */
function doGet() {  
  var form = FormApp.create('Form Example');
  form.addTextItem().setTitle('Your Name').setRequired(true);
  
  // list district data into a select box
  var districts = getDistricts();
  var districtList = form.addListItem();
  districtList.setTitle('Your District').setRequired(true);
  districtList.setChoiceValues(districts);
      
  form.addDateItem().setTitle('Birth Date').setRequired(true);
  form.addParagraphTextItem().setTitle('Your Message').setRequired(true);
  form.setTitle('Feedback Form').setConfirmationMessage('Thanks for your feedback !!');
  
  // Update the form's response destination.
  form.setAllowResponseEdits(true); // allow users to edit their form reponse
  
  // change form destination to a particular spreadsheet
  //form.setDestination(FormApp.DestinationType.SPREADSHEET, '5bcxxxxxxxxxxSbkwXJCEZwntNodNKTXj9edxxxxxxxx');
  
  var url = form.getPublishedUrl();
  
  deleteTrigger();  
  ScriptApp.newTrigger('formSubmitReply').forForm(form).onFormSubmit().create();  
    
  return HtmlService.createHtmlOutput(
    "<form action='" + url + "' method='get' id='foo'></form>" + 
    "<script>document.getElementById('foo').submit();</script>"); 
}

/**
 * Fetch submitted response from the form
 * send email to a particular email address
 * save responses to particualr spreadsheet
 */
function formSubmitReply(e) {
  //Logger.log(e.response.getItemResponses());
  var name = e.response.getItemResponses()[0].getResponse();
  var district = e.response.getItemResponses()[1].getResponse();
  var birthDate = e.response.getItemResponses()[2].getResponse();
  var message = e.response.getItemResponses()[3].getResponse();
   
  MailApp.sendEmail({
    to: 'your.name@example.com', // YOUR EMAIL ADDRESS
    subject: 'Feedback',
    htmlBody: '<b>Message from:</b> <br> Name: ' + name + '<br> District: ' + district + ' <br> Birth Date: ' + birthDate + ' <br> Feedback: ' + message
  });
  
  // Save form response data into a new spreadsheet
  var sheetId = '8AXNUNpM9tXNUNp0xxxdbYIIYLMxxxxxxxxxx';
  var ss = SpreadsheetApp.openById(sheetId);
  var ssheet = ss.getSheetByName('Sheet1');
  
  responseArray = [];
  
  // This formats the date as Greenwich Mean Time in the format
  // year-month-dateThour-minute-second.
  var formattedDate = Utilities.formatDate(new Date(), "GMT", "yyyy-MM-dd HH:mm:ss");  
  
  for (i in e.response.getItemResponses()) {
    responseArray[i] = e.response.getItemResponses()[i].getResponse();    
  }
  
  // add new element at the beginning of the array
  responseArray.unshift(formattedDate);
  
  ssheet.appendRow(responseArray);  
}

/**
 * Delete previously created triggers
 */
function deleteTrigger() {
  var allTriggers = ScriptApp.getProjectTriggers();
  for (var i = 0; i < allTriggers.length; i++) {
    if (allTriggers[i].getHandlerFunction() == 'formSubmitReply') {
      ScriptApp.deleteTrigger(allTriggers[i]);
    }
  }
}

/**
 * Fetch data from a particular spreadsheet
 */
function getDistricts() {
  var sheetId = '9tXNUNpM9tXNUNp0BydbYIIYLMxxxxxxxxxx'; // ID of spreadsheet containing districts name
  var ss = SpreadsheetApp.openById(sheetId);
  var sheetData = ss.getDataRange().getValues();
  //Logger.log(sheetData);
  
  var headers = sheetData.shift(); // pops out first row (i.e. header row) from sheetData
  //Logger.log(headers);
  //Logger.log(sheetData);  
  
  var districts = [];
  // get the first column data (first column in spreadsheet is district data)
  //for (i in data) {
  for (i = 0; i < sheetData.length; i++) {
    // get second column data 
    // as we have kept district names in 2nd column in the spreadsheet
    districts[i] = data[i][1]; // 1 = second column
  }
  //Logger.log(districts);
  return districts;  
}

Hope this helps. Thanks.