How to autofill fields in Google Forms from a Spreadsheet?

This is a common question from Google Sheets users whose forms ask for details the sheet already has. For example, your staff pick an employee ID and they should not have to type the name, department and office that are already in your Google Sheet. Google Forms does not have a native way to autofill fields from Google Sheets, so people retype them and mistakes get in. Apps Script can't help either, as it can't fill in fields while someone is filling the form. With Formfacade, you can implement autofill in Google Forms without any code. Your staff pick the ID and the form fills in the rest from your Sheet. Even better, you can autofill from more than one sheet and your sheet is never modified.

Let's start. Connect your Google Sheet to your form and follow the steps under How to set it up below.

Autofill vs. Prefill

These two terms are often used interchangeably but describe different things:
Autofill happens while the respondent is filling out the form. When they select or enter a value, related fields populate in real time from your sheet. For example, a respondent selects an employee ID and their name, department and location autofill automatically.
Prefill happens before the form is opened. The form owner generates a unique prefilled link for each respondent, with certain fields already populated when the respondent opens the form. For example, sending a feedback form to customers with their name and order number already filled in. You can manually generate prefill links, use a Google Sheets formula to automatically generate prefill links based on data in your sheet or use Formfacade Prefill to generate secure prefill links without exposing data in URL parameters.
This article covers autofill. If you are looking to generate prefilled links from a Google Sheet to send to specific respondents, see Formfacade - Prefill.

What is Autofill in Google Forms?

Autofill connects a question in your form to one or more columns in your Google Sheet. When a respondent selects or enters a value, the form queries your sheet in real time and populates multiple fields with related data automatically.
For example, when a respondent selects an employee ID from a dropdown, their name, department and office location autofill from the sheet into the corresponding fields in the form. The respondent does not need to enter this information manually.
Or, when a respondent selects a product from a dropdown, the product description, price and available stock autofill from one sheet and the applicable tax rate and shipping cost autofill from a separate sheet.
Autofill can populate multiple fields at once, pulling data from one or more connected sheets.

Can I use Apps Script?

Apps Script can be used to edit a form or trigger actions after a respondent submits the form. For example, it can look up a sheet to update answer choices in a dropdown or send a confirmation email to the respondent. However, Apps Script cannot respond to a respondent's input in real time within a native Google Form.
Autofill requires the form to query one or more sheets the moment a respondent makes a selection or enters a value and populate multiple fields instantly. Apps Script has no way to do this while the respondent is filling out the form.

Import Google Forms via URL

If you have already created your form in Google Forms, you can use the form URL to import it to Formfacade. This creates a copy of your form in Formfacade where you can configure the lookup. When respondents submit the form using the Formfacade link, responses are recorded in Google Forms. You can use the Link to Sheets feature in Google Forms to automatically sync responses to Google Sheets in real time.
Let's take this example. A company uses a Google Form for employee requests. When a staff member selects their employee ID from a dropdown, their name, department and office location autofill from the Employee sheet automatically. The staff member only needs to fill in the details specific to their request.

How to set it up?

Your Google Form needs the question people will answer and a question for each field to autofill. Your Google Sheet needs a column for each.
What do you want to do with your Google Sheets data?, with Autofill fields from Google Sheets marked
  • Paste your Google Form link and click Next. Formfacade will copy your questions
  • Pick connected sheets page will be displayed. Select your sheet or click + Connect another sheet and click Next. If you have not connected a sheet yet, click Connect a Google Sheet and pick your sheet in Google's window
  • "How do you want to connect?" will be displayed with an instruction written for you. Edit it to fit and click Proceed. For example: "When a staff member selects an employee ID, autofill their name, department, and office location from the Employee sheet."
  • "Your form is ready" will be displayed. Click Preview your form to see it
  • Share the Formfacade link with your respondents.
Describe what you want in plain English or any language and it will query your sheet in real time when a respondent makes a selection and populate the answers with data from your sheet. The form stays in sync with your sheet automatically.
Let's take another example. A retail store uses a Google Form to take orders. When a customer selects a product from a dropdown, the product description, price and available stock autofill from the Products sheet and the applicable tax rate and shipping cost autofill from the Pricing sheet. The customer only needs to enter the quantity. For example: "When a customer selects a product, autofill the product description, price, and available stock from the Products sheet, and the tax rate and shipping cost from the Pricing sheet."

Why this approach?

  • No Apps Script needed. Use simple plain English instructions to set up the lookup.
  • Handles large data. Supports data of any size backed by Google Sheets with no performance limits.
  • Flexible UI. Hide lookup fields or mark them as read-only so respondents cannot edit the returned value.
  • Your sheet is the single source of truth. Update data in one place and every connected form reflects it automatically.
  • Your sheet is never modified. Formfacade mirrors your data to serve forms fast and never uses write access to your sheet.

Frequently Asked Questions

Can I autofill fields from more than one sheet?
Yes. You can connect multiple sheets to the same form and autofill different fields from different sheets based on a single selection.
Can I hide or make autofilled fields read-only?
Yes. You can mark autofilled fields as read-only so respondents cannot edit the returned values or hide them entirely so respondents do not see them while filling out the form.
What happens if the selected value does not exist in the sheet?
The autofill fields will remain empty. You can configure an error message to notify the respondent if no matching data is found.
Are responses recorded in Google Forms?
Yes. Regardless of whether you use the import method or the add-on, responses are recorded in Google Forms as normal. You can use the Link to Sheets feature in Google Forms to sync responses to Google Sheets in real time.

Summary

If people retype details that are already in your Google Sheet, mistakes get in. With Formfacade, you connect your sheet and describe in plain English which fields to autofill. Connect your Google Sheet to your form with Formfacade. If you have any questions, write to support.

Last updated: