Pre-filled Google Forms for verifying student/demographic information

Our school’s PowerSchool data is jacked. Student names that should be separated with first, middle and last names are all inconsistent and we don’t have one field for preferred or “English” name. Parent email addresses and phone numbers change over time and our data has not been updated regularly.

To address this, we will send a pre-populated Google Form out to parents asking them to verify and update the data in our system. Once we’ve received the updates, we’ll upload changes into PowerSchool.

To create the prepopulated forms, we first export our current student and parent information into a spreadsheet. Then we create a Google Form with questions that match up with the various fields from the export. In the Form Responses spreadsheet, we paste the information from our data export and add a custom script (evenBeterBuildUrls()) taken from this stackoverflow question from user Mogstad. The script is also posted here.

This script creates a new column in the Form Responses spreadsheet with a link to a prepopulated version of the form containing our current demographic information (student & parent names, email addresses, street addresses, phone numbers, etc.). The link to the prepopulated form then gets emailed to parents using a mail merge on that spreadsheet.

Parent responses get appended to the spreadsheet and I use a Conditional formatting rule to highlight information that has changed. Changes are pulled into another sheet which we massage a bit before uploading again to PowerSchool.

Once completed, we’ll have correct, current information necessary for communicating with our community. This process will be repeated each fall to ensure that we’re working with accurate, current data.

Leave a Reply