Google Forms Pre-Filled Answers Tutorial

Google Forms Pre-Filled Answers Tutorial

Prefilled Google Forms offer a useful way to streamline and speed up the form-filling process by preloading some areas with previously provided answers.

By making the form more simple for your contacts, this simplified method not only saves time but also enhances the probability that it will be filled out. Users are more likely to interact with your forms if there is less time required to complete the remaining fields.

Additionally, prepopulated elements, such the employee ID, lessen the possibility of incorrect data entry, guaranteeing the quality and integrity of the data gathered. This not only improves the quality of your data but also the user experience as a whole.

Additionally, adding individualized details to the forms, such the respondent’s name, gives them a human touch and increases user engagement. These customization options let users engage with your forms in a more favorable and user-friendly way, which ultimately helps you and your audience create deeper bonds.

Create Pre-filled Google Forms with Google Sheets:

In our hypothetical case, the company uses a Google Spreadsheet to handle its personnel database, and they want to empower their staff by letting them edit their own data on Google Forms.

Google Forms Pre-Filled Answers Tutorial

A deeper look at the Google Sheet’s employment data indicates that certain information about the employees is missing. Prefilled Google Forms are a great solution in this scenario as it would be inefficient and time-consuming to distribute blank forms and ask staff to manually fill out every section.

For instance, take a look at row #2, where we know Angus’s location and gender but not his birthdate. Similar to row #4, Kiran’s employment ID and email are available to us, but other crucial facts are missing.

Create the Google Form:

We will begin by creating a Google Form and matching its fields with the columns in the source Google Sheet in order to start this procedure. Here’s a look at the completed form:

Google Forms Pre-Filled Answers Tutorial

Generate the Prefilled Form Link:

Go to the 3-dot menu in the Google Form editor and choose the ‘Get pre-filled link’ option. Fill in each field in this phase with recognized dummy information that you may later replace. After filling out all the fields, click the “Get Link” button to produce the prefilled URL, which you can then copy to your clipboard.

Typically, the prefilled Google Form URL looks like this:

Google Forms Pre-Filled Answers Tutorial

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

A closer look reveals that, despite its length and complexity, it is really only a collection of name and value pairs attached to the Google Form URL. Each field in the form is given a special identity by Google Forms, and they are added to the Form URL along with your pre-populated information.

For instance, the ‘Name’ field in your Google Form is represented by the URL ‘entry.1663131167’. The Google Form is pre-populated with the value that is substituted for the ‘EMPLOYEENAME’ parameter value in the URL.

With this method, we can tailor prefilled URLs for each row in our Google Sheet, making data entry easier and increasing productivity.

Google Forms Pre-Filled Answers Tutorial

Add Form Formulas in Google Sheet:

Start by adding a new page to your Google Spreadsheet and labeling it “Form Link.” Insert the Google Form link into the first cell (A1) of the newly formed sheet by pasting it there.

Return to your employee database’s Google Sheet now, and add a new column with a possible name of “Google Form Link.”

The following step is changing the placeholder values in our prefilled link to the real data found in the sheet’s rows. The SUBSTITUTE tool in Google Sheets makes this process simple to do.

We’ll create a formula like this, for instance, to replace “EMPLOYEENAME” in the prefilled link with the real names discovered in column B of the spreadsheet:

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

In order to replace more fields, such as “EMPLOYEEID,” the output of this formula will then be utilized as the input for another SUBSTITUTE function.

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

For each Google Form field that has already been filled in, this procedure must be repeated.

If the prefilled information contains spaces, you will need to wrap the results in a second SUBSTITUTE function to convert all instances of spaces to plus signs.

The following will show up in the final prefilled link:

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

Utilizing the prefilled Google Form that is offered will record your form submissions in new rows in the Google Sheet, allowing you to test out this approach.

Copy-down the Google Forms Formula:

There are a number of effective techniques for handling Google Forms that may help you save time and effort. A simple introduction to getting the most from your forms is provided below:

Consider using ArrayFormula to copy down formulae. If your dataset is tiny, another option is to choose the first cell and drag the crosshair down to the final row of the formula column. This quick step effectively duplicates your formula over the selected rows.

Handling Dates in Google Forms:

Make sure your dates in Google Sheets are formatted in a way that Google Forms can understand if you plan to prefill dates in your Google Form. This is how you do it:

  • Choose the Google Sheet column that contains the dates.
  • Open the ‘Format’ menu.
  • Select “Number” and then go to “More Formats.”
  • To guarantee flawless date identification by Google Forms, select the “YY-MM-DD” format under “More date and time format.”

How to Email Prefilled Google Form Links:

Using Mail Merge with Gmail from your Google Sheet might make it easier to send prefilled forms to several email addresses. Take these actions:

  • Choose the email addresses you wish to contact from your Google Sheet.
  • Create a combined email template using Mail Merge in Gmail.

Keep these things in mind when you create your email template for the merge:

  • Any content in the email body can be made into a link.
  • Use the word “Google Form Link” in the title of the column that contains the links to your Google Forms as the hyperlink. This placeholder will be updated by the appropriate Google Form URL for each recipient automatically.

You may use Google Forms to streamline your process by putting these tactics into practice.

Leave a Comment