Our formula would be something like this: Now we need to replace the dummy values in our prefilled link with the actual values from the rows in the sheet and this can be easily done with SUBSTITUTE function of Google Sheets.įor instance, we need replace EMPLOYEENAME in the prefilled link with real names that are in column B of the spreadsheet. Next return to the Google Sheet that has the employee database and create a new column, say Google Form Link. Paste the prefilled Google Form link in the first cell (A1) of this blank sheet. Inside your Google Spreadsheet, create a new sheet and rename it Form Link. If we replace the parameter value EMPLOYEENAME in the URL with another value, that would be pre-populated in the Google Form.Īnd this is exactly what we’ll do to create personalized prefilled links for all the rows in our Google Sheet. Google Forms will assign a unique id to each field in the form and these are appended to the Form URL with your pre-populated value.įor instance, the Name field in your Google Form is internally represented as entry.1663131167 in the form URL. It’s long and complex but if you take a closer look, this is simply a collection of name and value pairs appended to the Google Form URL. The link to the prefilled Google Form would look something like this. Once the fields have been filled, click the Get Link button to generate the prefilled link and copy it to your clipboard. Here, fill in every field with dummy data that is easy to recognize and replace later. Inside the Google Form editor, click the 3-dot menu choose the Get pre-filled link option. Here’s how the final form would look like: To build this workflow, we’ll create a Google Form with fields corresponding to the columns in the source Google Sheet. For row #4, the employee ID and email is known but Kiran’s other details are missing. This is a perfect use case for using prefilled Google Forms as it be wasting employee productivity if we send them a blank Google Form and require them to fill out every single field.įor instance, in row #2, we know the location and gender of Angus but his date of birth is unavailable in our records.
If you look at employee records in the Google Sheet carefully, you’ll find that only some details of the employees are missing in the sheet. In our example, the organization maintains their employee database in a Google Spreadsheet and they want to give employees an option to self-update their details in the spreadsheet with the help of Google Forms. You can then use Mail Merge or Document Studio to automatically send the prefilled forms to your contacts in bulk with Gmail.
This step-by-step video tutorial explains how you can create pre-filled Google Forms with dynamic information from a Google Sheet. The forms feels more personal when people see their name and other personalized information pre-filled in the form.Ĭreate Pre-filled Google Forms with Google Sheets.The form respondents are less likely to type incorrect data in fields, like the employee ID, that are pre-populated.Your contacts are more likely to fill out the form as it takes less time for them to complete the remaining fields.
Prefilled Google Forms, where some of the fields in the form are pre-populated with answers you already have, make the process of filling out your forms easier and faster.
Published in: Google Forms - Formulas and Functions - Mail Merge for Gmail - Document Studio
0 kommentar(er)
