Submit a Response to Google Form Using Google Apps Script Made Easy

google-apps-script[1]

Okay, a geeky post for the Google Apps Script code lovers.

I love doing a bit of coding in my spare time, and being a lover of Google its a lot of fun playing around with Google Apps Script and seeing what you can do with.  We’ve been building our solution Google Forms to Asana Checklist Pro and in the process of testing this I found it really tedious submitting our forms over and over again.  And I mean we’ve done THOUSANDS of tests.  It just ends up burning so much time!!  So I wanted to come up with a way I could submit a Google Form response programatically without having to go and complete the form each time.

NOW … I looked around lots but no-one seems to have an elegant way of doing this.  I asked on the forums as well and its seems the two main approaches were to either:

Option 1: Pull in some test data from a spreadsheet and then construct it into a Google Form response, or

Option 2: Build a response where you have to declare all your questions/answers in code and then construct your Google Form response. This required reconstructing each item of the response based what type it was like:

var newFormItem = items.asParagraphTextItem();
var newFormItem = items.asDateTimeItem(); etc.

I didn’t like either of these approaches as it felt like too much hard work and then I realised there was a much MUCH easier way!

So this requires a bit of background knowledge. When you submit a Google Form it stores the data in the response spreadsheet.  We all know that. But actually it stores the data in Google storage somewhere up … “there” … in the cloud.  The spreadsheet is just a backup visual copy for you to see.  This cloud store of the Google Form is the real source of truth. If you remove your response sheet and attach a new one you get all your previous responses back.  So they are stored somewhere else.

Now Google Apps Script allows you to pull ALL the Google Form responses ever made by a Google Form into an object with just a couple lines of code.  Its pretty cool.  And then you can select any those responses using code and do stuff with them.  But it occurred to me if it has all that data there, why would I need to pull them in from a spreadsheet like in option 1 above? And secondly, if I’m able pull all that data from Google its already well constructed in the proper formats, so there is no need reconstruct it based on knowing the right response types of asParagraphTextItem() like option 2.

Super … this makes it really easy.  Just grab a previous response, loop through all its answers and push them into a new Form Response and then submit it.  Its that simple!

As a bit of usefulness and fun we’ve made the code so you can either resubmit your last form response again as a new response OR resubmit any previously random selected response as a new response. We find this particularly handy for testing purposes. You just have to go and do a few normal submissions with the Google Form first  (the old school way by filling out for form) so the code has something to pull from.

To make it even easier, I’m going to give you the code.  Simply copy and paste this code into your Google Form script editor project and run either of the functions from your debugger – Sweet!  Choose either of these function  resubmitLastFormResponseAsNew or resubmitRandomFormResponseAsNew

Let me know if you have any questions.

Please reference a link back to this page if you publish or refer to this anywhere else. Thanks.

BTW, I’m aware there are a few variables in the function getFormResponsesDetails () that aren’t actually used here but we use this code for other things so easier to just paste it all.

// Run this to resubmit your LAST Form response again as a new Form Submission
// You need to go and at least do ONE normal form submission the old school way first 🙂
function resubmitLastFormResponseAsNew() {

var [form, loggedTimestamp, thisFormResponses, thisResponse, thisResponseID, thisResponseItems, formEditUrl] = getFormResponsesDetails();

logIt("CALLED resubmitLastFormResponseAsNew");

// Create a form response item
var formResponse = form.createResponse();

// We are going to use just the last response stored in thisResponseItems
// Loop through it and build a form response from the existing form objects
// No fuss required trying to get response types to format them properly
for (var x = 0; x < thisResponseItems.length; x++) {

// Get the last item that was submitted
var response = thisResponseItems[x];

// Respost it back into a the new item
formResponse.withItemResponse(response);
} //END For loop

// Submit the form
formResponse.submit();
}

// Run this to resubmit a random selection from any of your previous form submissions as a new Form Submission
// You need to go and at least do ONE normal form submission the old school way first 🙂
// But you are est to go and post a few submission to get a decent selection of randomness
function resubmitRandomFormResponseAsNew() {

var [form, loggedTimestamp, thisFormResponses, thisResponse, thisResponseID, thisResponseItems, formEditUrl] = getFormResponsesDetails();

logIt("CALLED resubmitRandomFormResponseAsNew");

// Pick and random previous submission
var random = randomIntFromInterval(1,thisFormResponses.length);
var thisResponse = thisFormResponses[random];

// Get all the items for this latest response
var thisResponseItems = thisResponse.getItemResponses();

// Create a form response item
var formResponse = form.createResponse();

// We are going to use just the this response stored in thisResponseItems
// Loop through it and build a form response from the existing form objects
// No fuss required trying to get response types to format them properly
for (var x = 0; x < thisResponseItems.length; x++) {

// Get the last item that was submitted
var response = thisResponseItems[x];

// Respost it back into a the new item
formResponse.withItemResponse(response);
} //END For loop

// Submit the form
formResponse.submit();
}

// Generate a random number between 2 numbers
function randomIntFromInterval(min,max)
{
return Math.floor(Math.random()*(max-min+1)+min);
}

// Get the forms response for this submission and its editable URL and return it
function getFormResponsesDetails (formID) {
logIt("CALLED getFormResponsesAndEditableURL WITH " + formID);

var form = FormApp.getActiveForm();
var formID = form.getId();

// Get the form OB
var thisFormOB = FormApp.openById(formID);

// And Array of Objects with all the responses for this form
var thisFormResponses = thisFormOB.getResponses();

// Get an array of all the items (questions and answers and each item details) in the form
//https://developers.google.com/apps-script/reference/forms/item-response#getitem
var thisFormItems = thisFormOB.getItems();

// Get the last response that came through
var thisResponse = thisFormResponses[thisFormResponses.length-1];

// Get the ID for the latest response
var thisResponseID = thisResponse.getId();

// Get all the items for this latest response
var thisResponseItems = thisResponse.getItemResponses();

// get this submitted form's "Timestamp"
var loggedTimestamp = thisResponse.getTimestamp();

// get the url
var formEditUrl = thisResponse.getEditResponseUrl();

return [form, loggedTimestamp, thisFormResponses, thisResponse, thisResponseID, thisResponseItems, formEditUrl];
}

Image kindly sourced from Kiss Metrics

>