How to autofill Google Forms with data from Google Sheets?

"use data in an existing Google Sheet to populate a set of Google Forms"- Google Docs Editors Community

This is a common question, and 185 people on Google's forum have asked it. Your customers, students or staff are already rows in a Google Sheet, and each of them should get a form with their own details filled in. Google Forms can't read a Sheet. Its Pre-fill form makes one link at a time, and a formula that builds the links in Sheets puts every value in the link, where anyone can read it and change it. With Formfacade, you enter the details in Google Sheets and it makes a prefill link for each row. Each person opens their own link and sees their details already filled in, and you can make those fields read only so they come back the way you sent them.

Below are three ways to prefill a Google Form from a Google Sheet, and where each one falls short.

3 ways to prefill a Google Form from Google Sheets

  1. Google's Pre-fill form, one link at a time
  2. A formula in Google Sheets that builds a link for each row
  3. Formfacade, which makes a link for each row of your Sheet

1. Google's Pre-fill form

In Google Forms, click ⋮ More, then Pre-fill form. Fill in the answers for one person, click Get link and copy it. Read How to prefill answers in Google Forms?
Where it falls short: You repeat it for every person. Each link carries the values in its address and the person can change any of them.

2. A formula in Google Sheets

Copy one prefilled link and, in a new column, join its address with the values from each row, using the entry number of each question.
Where it falls short: Someone has to write the formula and fix it. On Google's forum, people report links too long to send, dates that turn into numbers and answers cut short at an & or a space. The values are still in the link and can still be changed.

3. Formfacade: a prefill link for each row

  • Click Create your form, name your form, click Create form and click Proceed.
  • In the Prefill & email dialog that asks "Do you want to prefill this form and send them as email to your contacts?", click No.
The Prefill and email dialog asking Do you want to prefill this form and send them as email to your contacts?, with No marked
  • Click the Share tab
The tabs Edit, Preview, Share and Responses, with Share marked
  • Under More options, click Create prefill links
The More options panel on the Share page, with Create prefill links marked
  • Click + Create new prefill
The Prefill links page saying No prefilled links yet, with + Create new prefill marked
  • Enter a unique name for your first link and click Create prefill link
  • Click Create prefill, then Import prefill, then View Sheets
  • In the Google Sheet, enter the Prefill Name, email and name of each customer
A Google Sheet with a Customer details tab and the columns Prefill Name, Name, Email and Customer ID, with three customers
  • The first time, click Extensions > Add-ons > Get add-ons, search for "Formfacade - Prefill & email" and click Install
  • Click Extensions > Formfacade - Prefill & email > Import prefill
  • Check the rows and click Proceed
Formfacade makes a prefill link for each row. They are listed on the Prefill links page, each with its own Copy link. To email each person their link, read How to send bulk emails with personalized google forms prefill link?
The Prefill links screen listing customer-1001, customer-1002 and customer-1003, each with its own link, 3 fields, Preview and Copy link, with one Copy link marked

Summary

Google Forms can't read a Google Sheet, so prefilling a form for each person means making one link at a time or writing a formula that puts every value in the link. With Formfacade, you enter the details in Google Sheets and it makes a prefill link for each row, with the values kept out of the link. Create your form and complete the steps above. If you have any questions, write to support.

Last updated: