How to send Google Forms with pre-filled answers

by

How to pre-populate responses to Google Forms using data from Google Sheets and send pre-populated Google Forms as personalized emails.

Prefilled Google Forms, where some form fields are pre-filled with answers you already have, make filling out your forms easier and faster.

  1. Your contacts are more likely to fill out the form as it takes less time for them to complete the rest of the fields.
  2. Form respondents are less likely to type incorrect data into fields, such as employee ID, that are pre-populated.
  3. Forms feel more personal when people see their name and other personal information pre-filled on the form.

Create pre-filled Google Forms with Google Sheets

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

In our example, an organization maintains a database of its employees in Google Spreadsheet and they want to give employees the option to self-update their details in the spreadsheet with the help of Google Forms.

If you look carefully at the employee records in the Google Sheet, you will find that some details of the employees in the sheet are missing. This is the best use case for using prefilled Google Forms because it ruins employees’ productivity if we send them a blank Google Form and require them to fill every single field.

For example, in row #2, we know the location and gender of Angus but his birth date is not available in our records. For Row #4, Employee ID and Email are known but Kiran’s other details are missing.

Create a Google Form

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

Within Google Forms Editor, click the 3-dot menu and select Select Get pre-filled link option. Here, fill each field with dummy data that is easy to identify and replace later. Once the fields are filled, click Get Link button to generate the pre-filled link and copy it to your clipboard.

The pre-filled Google Form link looks 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 complicated but if you look closely, it’s just a collection of name and value pairs appended to a Google Form URL. Google Forms will assign a unique ID to each field on the form and these are appended to the form URL with your pre-populated value.

For example, the name field in your Google Form is represented internally entry.1663131167 In the form URL. If we change the parameter value EMPLOYEENAME Another value in the URL, which will be pre-populated in the Google Form.

And what we’re going to do is create personalized prefield links for all the rows in our Google Sheet.

Add Form Formulas to Google Sheets

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

Go back to the Google Sheet containing the next employee database and create a new column, say Google Forms Link.

Now we need to replace the dummy values ​​in our prefield link with real values ​​from the rows in the sheet and this can be done easily. SUBSTITUTE Functions of Google Sheets.

For example, we need a replacement EMPLOYEENAME In the pre-filled link with the actual names in column B of the spreadsheet. Our formula will be:

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

We will feed the results of this formula into the next one SUBSTITUTE A function to replace another field, say EMPLOYEEID.

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

This should be repeated for each pre-filled field in the Google Form.

If your prefilled data contains spaces, you must wrap the results in another SUBSTITUTE function that will replace all occurrences of spaces with a plus symbol.

Our final pre-filled link will be:

=SUBSTITUTE(
  SUBSTITUTE(
   SUBSTITUTE(
    SUBSTITUTE(
     SUBSTITUTE(
      SUBSTITUTE('Form Link'!$A$1, "EMPLOYEEID", A2),
     "EMPLOYEENAME", B2),
    "2020-05-31",E2),
   "Female", C2),
  "Hyderabad", D2),
 " ", "+")

You can test the workflow by using this pre-filled Google Form where you write your form submission on a new row of this Google Sheet.

Copy-down the Google Forms formula

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

Handling dates in Google Forms

If you plan to pre-populate dates in Google Forms, you must rewrite your dates in Google Sheets in a format that Google Forms can recognize.

It is easy to implement. Simply select the column that contains the dates in your Google Sheet, then go to the Format menu, choose Number > More Formats > More Date & Time Format and select it. YY-MM-DD Pattern.

See also: Create PDF from Google Form responses

You can use Mail Merge with Gmail to send pre-filled forms to all email addresses right from Google Sheets.

When composing an email template to merge, select any text in the body of the email and convert it to a hyperlink. You can put a column title – {{Google Form Link}} as a hyperlink and it will be replaced with your Google Forms link.

Please see Mail Merge Tutorial to learn more.

Leave a Comment