How to look up data from Google Sheets in Google Forms?

This is a common question from Google Sheets users who keep their data in a sheet and collect answers on a form. For example, someone picks a student name or enters a coupon code and you want the form to find the related value in your Google Sheet. Google Forms does not have a native way to do this. You can use Apps Script after someone submits, but it can't respond while the person is still filling the form. With Formfacade, you can implement lookup in Google Forms without any code. Your respondents will see the value appear as soon as they answer. Even better, you can hide the value or mark it as read-only and it is still recorded on your Google Forms.

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

What is a Lookup in Google Forms?

A lookup connects a question in your form to a column in your Google Sheet. When a respondent selects or enters an answer, the form queries your sheet in real time and returns a related value based on that selection.

For example, when a respondent enters a coupon code, the form can look up and return the applicable discount from your sheet. Or, if a respondent selects a student name from a dropdown, the form can look up and return that student's email address from your sheet. The email address is recorded as an answer for the email address field in the form. You can choose to hide this field so respondents do not see it while completing the form.

This is different from populating a dropdown, where the sheet is only used to supply the list of options. A lookup happens after the respondent makes a selection or enters a value and returns data from your sheet based on what they chose.

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.

A lookup requires the form to query your sheet the moment a respondent makes a selection or enters a value and 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 school admin form presents a dropdown of student names. When a staff member selects a name, the student's email address is automatically looked up from the sheet and recorded with the response. This eliminates manual entry errors and ensures the email address is always accurate and up to date. The email field can be hidden so the staff member does not see it while filling out the form.

How to set it up?

Your Google Form needs the question people will answer and a question for the value to look up. Your Google Sheet needs a column for each.

Click on Connect your Google Sheet to your form
Click on "Connect Google Sheets to your form with AI" in the page that opens

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 implement the lookup and click Proceed. For example: "When a staff member selects a student name, look up and return the email address from the Students 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 answer 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 and support discount redemption. When a customer enters a coupon code, the form queries the sheet in real time and returns the applicable discount. If the coupon code does not exist in the sheet, the form displays an error message. For example: "When a customer enters a coupon code, look up and return the applicable discount from the Discounts 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.

Limitations

Requires a subscription to Formfacade - Sheets Lookup.
Responses are recorded in Google Forms as normal, but you must use the Formfacade link instead of the native Google Forms URL to collect responses.

Frequently Asked Questions

Can I hide the looked-up value from the respondent?

Yes. You can choose to hide the field that displays the returned value. The value is still recorded with the form response but the respondent does not see it while filling out the form.

What happens if the value entered by the respondent does not exist in the sheet?

The form can display an error message to the respondent. For example, if a customer enters an invalid coupon code, the form will notify them that the code does not exist.

Can the lookup return a value from a different sheet than the one powering the dropdown?

Yes. The dropdown and the lookup can be connected to different sheets.

Does the sheet need to be in a specific format for the lookup to work?

No. As long as the lookup values are in one column and the related values are in another column, Formfacade can work with your existing sheet structure.

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 you need the form to find a value in your Google Sheet when someone answers, Google Forms can't do it on its own. With Formfacade, you connect your sheet and describe the lookup in plain English. Connect your Google Sheet to your form with Formfacade. If you have any questions, write to support.