I was tasked recently to create an In and Out Google App Script so users could set their status so that others can see who was in or out of the office using a Google Calendar. Currently users message reception and then they create an event in the Ins & Outs calendar. I have started to leverage the power of Google Apps Script to allow end users to fill out a form and take the burden of the task of reception. I started by creating a Google Form with the proper questions then I opened the forms script editor to throw in some logic to add the data to the calendar. I have used the onFormSubmit(e) function and installable trigger in the past on other Google Forms that store their data in a Google Sheets and the form responses were sent with the event object that is sent to the onFormSubmit() function. For some reason when I setup a trigger on my Google Form to call a function called onFormSubmit(e) I noticed that the responses were not being included with the event object. Womp, womp so begins my journey again…

I searched around the web and found a great post by Taming the Tech that showed how to retrieve the last response from a Google Form and it seems like they had the same issue as me and didn’t want to use a Google Sheet (Why use a Google Sheet when you don’t need one?) After looking at their code I realized that this just returns an array of question answer in an indexed array. So the answer would get returned in the order that they appear in the Form i am guessing? What happens if you move a question or delete a question? Well I would have to change all my code to compensate for that change. Well that’s not good programming then! While the code was useful it did not sit right with me, there has to be a better solution. After searching some more I didn’t not find a better solution just more repeats of using the same code as I found before. So I do what I do every time there is not a solution, make one myself and share it with everyone else so they don’t have to figure it out themselves.

Here is the code, sorry for the long story to go with it but sometimes talking about the journey is just as fun as the destination.

/**
 * Object constructor that constructs an object containing each question and answer
 * in the format of {Question Title : Answer} based on the last form response
 * 
 * @param {object} form A Google Form Object - If nothing is passed the current form is assumed
 */
function getLastFormResponse(form = FormApp.getActiveForm()) {

  form.getResponses()              // Get all the responses for the form
    .pop()                         // Take only the last response
    .getItemResponses()            // Get the Item responses
    .forEach((itemResponse) => {   // Loop over each item response
      this[itemResponse.getItem().getTitle()] = itemResponse.getResponse(); // add it title and response to the object
    });

}

You would call this constructor function like so:

const formResponse = new getLastFormResponse();

This will construct the formResponse object that has properties and values based on the responses from the form. To get a list of all the object property names and values, in a human readable format use the following code after you call the constructor:

const formResponse = new getLastFormResponse();
Logger.log(JSON.stringify(formResponse,null,2));

Your output should look something like this in your Google App Script IDE

{
   "First Name": "Amanda",
   "Last Name": "Smith",
   "Birthdate": "2021-10-09",
   "Age": "35"
}

You can then use these values like so

const firstName = formResponse['Fast Name'];
const lastName = formResponse['Last Name'];
const birthdate = New date(formResponse['Birthdate']);
const age = formResponse['Age'];

As you can see this is very useful when working with code in a Google Form

© Caspan 2021