Google Sheets Automation – Auto Create Pre-Filled URLs with Registered Addresses

automationgoogle sheetsgoogle-forms

We use google forms to send out tests to our students. We have a spreadsheet with rosters where we keep the info of the students (email, birthday, gender, guardian etc) for every class in our school. One roster per sheet. After we finalize and approve a form we immediately link the form to a Responses spreadsheet.

We wonder whether we could (from the existing roster spreadsheet) automate the following workflow:

  1. Create and fill-in with one of the email addresses, a single option checkbox question. This could be the first question of the form.
  2. Generate a pre-filled link of the form with the above mentioned email address from the roster.
  3. Append the generated pre-filled link to the Responses spreadsheet.
  4. Rotate the process through all emails in the roster so we end up with a column of the links.

Related

Best Answer

Short answer

Yes, it's possible to automate the workflow but with slight changes. This answer assumes that the OP is using a consumer account instead of a Google Apps for Work of Google Apps for Education account.

Explanation

First Step

  1. Create and fill-in with one of the email addresses, a single option checkbox question. This could be the first question of the form.

REMARK: If you want that to use a unique option to prevent that the student change it, then you will need to create a new form for each student. To automate this you will need to use Google Apps Script or an add-on. This was left out of this answer as it will make it too long for the Q&A model of this site.

Checkboxes are used to allow multiple option selection, radio buttons are used to allow to select only one option but both will show all the email addresses and this could take a lot of space. Instead of using a checkbox/radio button, consider to use a dropdown list as the list of all email address will be displayed only when the user click the dropdown button.

If the list has hundreds of email addresses, instead of a dropdown use text question to make the form load faster.

Second Step

  1. Generate a pre-filled link of the form with the above mentioned email address from the roster.

Pre-filled links use URL parameters with special ID for each question. To keep the things simple, get the link from the form at least to take on URL to be used as template.

To get the link to be used as template follow the instructions of Prepopulate form answers - Google docs editors Help

Third step

  1. Append the generated pre-filled link to the Responses spreadsheet.

Assuming that the OP is referring to the roster spreadsheet, add a column to hold the pre-filled URL for each student.

For a form with two questions, one for the email address and pre-filled, the pre-filled URL will look like the following

https://docs.google.com/forms/d/form_id/viewform?entry.29450426=name@example.com

The form id will look like the following

1awKpg_diniayS6360kNXrcgihk36azQ3DJEaZqXDY7A

The pre-filled email field will look like the following:

entry.29450426=name@example.com

The part between entry. and = could be different for each form, and obviously the email could be anyone as in this step the purpose is to find the URL to be used as template.

Forth step

  1. Rotate the process through all emails in the roster so we end up with a column of the links.

REMARK: In the automation terminology, instead of "rotation" the term used is "iteration" but also could be referred to this kind of task as "doing a loop" or "looping through...".

There are several ways to automate this. You could use formulas, Google Apps Script and Add-ons.

Using a formula.

In this step, the pre-filled URL corresponding to each student will be generated.

In the roster spreadsheet, assuming that the first row holds the column headers and the column B holds the student email addresses, in the new column add the following formula in the cell in the second row

="https://docs.google.com/forms/d/form_id/viewform?entry.29450426="&B2

Then fill down as necessary.

REMARKS:

The following formula will fill the required cells, but it requires that the there aren't data below the students data range.

=FILTER(
    "https://docs.google.com/forms/d/form_id/viewform?entry.29450426="&B2:B,
    LEN(B2:B)
 )

Using add-ons

I don't think that there is an add-on that implement the whole workflow, so it's very likely that could be necessary to use several or to mix formulas, scripts and add-ons.

One of the add-ons that could be helpful is FormMule. It's a very popular addon among teachers that could be used to send the pre-filled form URL that correspond to each student.

Using Google Apps Script (GAS)

As this could require a very long explanation for someone that doesn't know the basic this be excluded of this answer.

Related Topic