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


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

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.

Image kindly sourced from Kiss Metrics