Google Form autofill responses from Google Sheet

#googlecloud #tutorial #javascript #productivity

In this article I'll show you how to send multiple responses from a Google Sheet to any public Google Form.

To send multiple responses to a Google Form, we will use the EasyForm tool, a powerful Google Spreadsheet bounded with a Google Apps Script that can:

Analyze every public Google Form page to extract all the questions
Extract all the question properties (is required, response validations ecc.)
Submit multiple rows from a Google Sheet
Submit custom values

So the first thing you need to do is to make a copy of the spreadsheet.

EasyForm Spreadsheet:
https://docs.google.com/spreadsheets/d/1rrhUXSuYQ5wEnSNlawoMtjulsr6XBKVGbMmWgpU7-M0/copy

Github page:
https://github.com/Vryntel/EasyForm

Now that you have your copy, to be able to run the script you need to grant permission to the script (only the first time) by following these steps:

In the Spreadsheet page, go in the top toolbar and click Extensions > Apps Script
After the new page will open up, return in the spreadsheet page and in the top toolbar it will show up the EasyForm option
Click on the EasyForm option in the toolbar, and then click on Analyze Form
At this point Google will ask your authorization to run the script, you need to grant it

Now the tool setup is complete.

As you can see there are multiple worksheet in the spreadsheet:

About, contains general info of the tool
Settings, contatins all the settings of the tool like the spreadsheet url to use as input or the form url where to submit the rows
Form Questions, after analyzing a Google Form, it will contain the form questions
Example Data, contains an example of how the spreadsheet with data should be formatted

Ok, now we can start to use the tool.
First thing you need is the Google Form public url to where the responses will be submitted, that should be in this format:

https://docs.google.com/forms/d/e/123456abc/viewform

So paste your Google Form url in the "Settings" worksheet in the Form Url field. In this example we will use this form:

Now you need a Spreadsheet that contains all the rows to submit to the form. It can be another Spreadsheet file or you can create a new worksheet inside the same EasyForm Spreadsheet.

In this example we will use the "Example Data" worksheet that is inside the same spreadsheet.
So in the Settings worksheet, as Spreadsheet url we write the url of the same spreadsheet and for the Worksheet name we use "Example Data".

Now you need to specify which rows of your source worksheet you want to submit to the form, by using the start row and end row field in the Settings worksheet. The start row should be at least 2, because the first row is reserved for the column header names. In these example our data is between row 2 and row 6, so to upload every row just use as start row 2 and end row 6.

"Stop on error" is a field that can be TRUE or FALSE, and depending on its value it will stop or not the script execution after it finds an error.

Now we can analyze the form and get all the questions. To do this click on the EasyForm option in the toolbar and then Analyze Form.
After the script will finish its execution all the question will show up in the "Form Question" worksheet.

As you can see, for each form question you need to specify the corresponding column of our source sheet or you can specify a custom value to use for every row, by checking the "Custom Value" checkbox and specifying a custom value in column E.

The dropdown in column B will contain all the header names of our source worksheet, while the column C will contains a "X" if the question of the form is required.
If the question is a checkbox, dropdown or multiple choice, the custom value column (Column E) will contain a dropdown with all the available values.
So in our example for the first 4 questions, the script will use the corresponding column values in our source sheet, while for the "Newsletter" question it will use "Yes" as value for each entry row (This will be equivalent to add another column in the source sheet and write "Yes" for each row).

When you have choosen which column/custom values to use, go in the top toolbar, EasyForm, Send Data.

In our example here is the result:

Some notes:

This tool only works with Google Forms that don't require a login access with a Google Account
For checkbox question, if you want to specify more than one choice per row, as separator between the choices you need to use the following special space character:



" "

That's all. Thank you for reading!

Top comments (3)

Châu Anh Nguyễn • Aug 29

Thank you very much!! This does work really well with English, but when I’m using Vietnamese errors keep showing up :(. Do you have any idea how to fix this please?