How to Send Google Forms with Pre-filled Answers

Pre-filled Google Forms, where some of the fields in the form already contain answers you have, make your forms easier and faster to complete. In this tutorial, we’ll use a Google Sheet to create a personal link for each person, with their known answers already filled in, and email those links in bulk.

Pre-filling your forms has three benefits:

1. Your contacts are more likely to fill out the form as it takes less time for them to complete the remaining fields.
2. The form respondents are less likely to type incorrect data in fields, like the employee ID, that are pre-filled.
3. The form feels more personal when people see their name and other personalized information already filled in.

Create Pre-filled Google Forms with Google Sheets

This step-by-step video tutorial explains how you can create pre-filled Google Forms with dynamic information from a Google Sheet. You can then use Mail Merge or Document Studio to automatically send the pre-filled forms to your contacts in bulk with Gmail.

In our example, the organization maintains their employee database in a Google Spreadsheet and they want employees to review and complete their missing details with the help of Google Forms.

If you look at employee records in the Google Sheet carefully, you’ll find that only some details of the employees are missing in the sheet. This is a perfect use case for pre-filled Google Forms as it would waste employee time if we sent them a blank Google Form and required them to fill out every single field.

For instance, in row #2, we know the location and gender of Angus but his date of birth is unavailable in our records. For row #4, the employee ID and email are known but Kiran’s other details are missing.

Create the Google Form

To build this workflow, we’ll create a Google Form with fields corresponding to the columns in the source Google Sheet. Here’s how the final form would look:

Generate the Pre-filled Form Link

Inside the Google Form editor, click the 3-dot menu and choose the Get pre-filled link option. Here, fill in every field with dummy data that is easy to recognize and replace later. For multiple choice and dropdown questions, pick any available option. Once the fields have been filled, click the Get Link button to generate the pre-filled link and copy it to your clipboard.

The link to the pre-filled Google Form would look something like this.

https://docs.google.com/forms/d/e/xxxx/viewform
   ?entry.1808207196=EMPLOYEEID&entry.1663131167=EMPLOYEENAME
   &entry.1819275928=2020-06-03&entry.2071782719=Female
   &entry.175059757=Hyderabad

It’s long and complex but if you take a closer look, this is simply a collection of name and value pairs appended to the Google Form URL. Google Forms will assign a unique id to each field in the form and these are appended to the Form URL with your pre-filled value.

For instance, the Name field in your Google Form is internally represented as entry.1663131167 in the form URL. If we replace the parameter value EMPLOYEENAME in the URL with another value, that would be pre-filled in the Google Form.

And this is exactly what we’ll do to create personalized pre-filled links for all the rows in our Google Sheet.

Add Form Formulas in Google Sheet

Inside your Google Spreadsheet, create a new sheet and rename it Form Link. Paste the pre-filled Google Form link in the first cell (A1) of this blank sheet.

Next return to the Google Sheet that has the employee database and create a new column, say Google Form Link. In our sheet, the other columns are ID (A), Name (B), Gender (C), Location (D), and Birth Date (E).

Now we need to replace the dummy values in our pre-filled link with the actual values from the rows in the sheet and this can be easily done with the SUBSTITUTE function of Google Sheets.

For instance, we need to replace EMPLOYEENAME in the pre-filled link with real names that are in column B of the spreadsheet. The ENCODEURL function makes the name safe to use in a link so that spaces, ampersands, and other special characters don’t break it. Our formula would be something like this:

=SUBSTITUTE('Form Link'!$A$1, "EMPLOYEENAME", ENCODEURL(B2))

We’ll feed the result of this formula into another SUBSTITUTE function to replace another field, say EMPLOYEEID.

=SUBSTITUTE(
   SUBSTITUTE('Form Link'!$A$1, "EMPLOYEENAME", ENCODEURL(B2)),
   "EMPLOYEEID", ENCODEURL(A2))

This has to be repeated for every pre-filled field in the Google Form.

Our final pre-filled link would be:

=SUBSTITUTE(
  SUBSTITUTE(
   SUBSTITUTE(
    SUBSTITUTE(
     SUBSTITUTE('Form Link'!$A$1, "EMPLOYEEID", ENCODEURL(A2)),
    "EMPLOYEENAME", ENCODEURL(B2)),
   "2020-06-03", IF(E2="", "", TEXT(E2, "yyyy-mm-dd"))),
  "Female", ENCODEURL(C2)),
 "Hyderabad", ENCODEURL(D2))

Copy-down the Google Forms Formula

You may use ArrayFormula to copy down formulas or, if you have only a few rows, select the first cell and drag the crosshair to the last row in the formula column as shown below:

Handling Dates in Google Forms

If you plan to pre-fill dates in the Google Form, the dates must be in the YYYY-MM-DD format that Google Forms can recognize.

The formula above takes care of this. The TEXT function writes the date in column E in the correct format, and the IF function leaves the date field empty if the birth date is missing, so you don’t have to change the date format in your Google Sheet.

Before You Send the Form

Keep these limitations of pre-filled forms in mind:

1. Form responses are added as new rows in the form’s response sheet. They won’t update the original row in your employee sheet.
2. Answers for multiple choice and dropdown questions must exactly match one of the options. For instance, New York will only be pre-filled if it is listed in the Location dropdown.
3. Respondents can edit the pre-filled answers, including fields like the Employee ID.
4. The pre-filled answers are visible in the link itself, so anyone who receives the link can see them. Be careful when pre-filling personal details like birth dates.

How to Email Pre-filled Google Form Links

You can use Mail Merge with Gmail to send the pre-filled forms to all the email addresses in one go from the Google Sheet itself.

When composing the email template for merge, select any text in the email body and convert it into a hyperlink. You can put the title of the column - {{Google Form Link}} as the hyperlink and this would be replaced with your Google Form link.

Please watch the Mail Merge tutorial to learn more.

Also see: Create PDF from Google Form Responses