Form workflow using Google Apps Script

Now that I’m in the dean’s office (Associate Dean for undergraduates in the College of Liberal Arts if you must know) I serve on a lot of committees. Several of them accept submissions from faculty, staff, or students and have to vet them, adjudicate them, and let someone else know how to act on them. We use all kinds of systems to do this, including email submissions, Google forms, home-built web forms, and (gasp) paper. This post is for me to get some thoughts down for how to improve one such process using a combination of Google forms, Google docs, and Google Apps Script.

Here’s the needed workflow:

  1. Faculty member submits a form so that one of their courses can meet one of our general education requirements (we have a distributed system where any course in principle can meet any of the requirements).
  2. The committee vets the submission against a rubric and discusses it. If it’s approved, the registrar (who sits on the committee) makes the change formal. The faculty member is also informed.
  3. If there is a need for more information or a different tack to be approved, the committee sends the original faculty member a note explaining what is needed.
  4. The faculty member can update their submission using the same form as step 1 above. Then steps 2 and 3 are repeated.

Problems with our current system:

  • There’s no easy way to have the committee keep common digital notes on the submissions. That results in everything happening in the meeting with each individual keeping their own notes ahead of time.
  • There is no real re-submission process. Right now both steps 3 and 4 and any repeats are done via email.
  • The submissions are not formatted very well.

Why Google? What I crave the most is the ability for the committee to group comment on a google doc ahead of the meeting. This would both aid in getting us all on the same page and provide a great record for how we made decisions in the past (rather than just seeing what our decision was) for similar submissions. This single craving drives a lot of the workflow I have in mind:

  1. Faculty member uses a single Google form that has multiple pages, having them jump to the appropriate section for each different type of general education requirement they’re applying for (I think I’ll make it that they’ll have to do it once for every different requirement).
  2. Upon submission, a Google doc will be created based on a template (or several, I suppose, given the different nature of the various general education requirements). This will then be emailed and shared with the entire committee. Its name will include the faculty member, the course, and the version number. A link will go in the “version” column added to the results spreadsheet with the version number hyperlinked to the google doc.
    1. Note that you can’t send emails using Google Apps Script based on a form submission. I gather this protects against massive amounts of email being sent. However, you can do a timed trigger and effectively have the system check once a day (or every five minutes, whatever you like).
  3. The committee will use the commenting features of Google Docs to collect their thoughts ahead of the meeting. In the meeting a decision will be made and placed in the “decision” column that’ll be added to the automatic Google Form spreadsheet
    1. “Approve”: This will fire a script to generate a final pdf version (that gets rid of the comments, of course) that gets emailed to the registrar and the original faculty member. A link for the pdf will go in the “final pdf” column of the spreadsheet.
    2. “Feedback”: This won’t do anything, as the chair likes to tailor the feedback emails to folks. However, the chair will send the “edit submission” link to the original form submission so that any changes will be represented in the spreadsheet.
      1. Note that we won’t turn on “send edit link” on the original form, as we don’t want it to be edited while we’re reviewing it. However, this awesome script allows you to gather these links upon the original submission and add them to the spreadsheet.
      2. We’ll keep an additional column of “last timestamp” so that we can do a timed trigger looking for any rows where the submission timestamp differs from the “last timestamp” so we’ll know to do regenerate the google doc and change the “decision” column to “pending”
    3. “Denied”: Frankly this doesn’t happen much, but we’ll have to add it for completeness. This won’t do anything either because the chair will want to tailor that email to the original faculty member
  4. As noted in 2-2 above, if a re-submission comes in, a new google doc will get generated with the appropriate version number and the process repeats. In the spreadsheet you’d see a “version” column with multiple hyperlinks in it, one for each version.

I think I know how to do all of this. Here are the technical highlights:

  1. Mix of triggers: There’s both “upon form submission” and “timed” triggers involved. Both are pretty straightforward to do.
  2. Generate a Google doc based on a template and a form submission. This is pretty easy as well, especially if you use clear template placeholders like <<name>> or <<First learning outcome>>.
  3. Generate a pdf from a Google doc. Easy
  4. Email things to people. This gets harder with good formatting but not really a problem
  5. Generate the “edit submission link”. The link above really figured that out for me.

So I think I’m in good shape. I’ll probably make a dummy system first to test, but I thought I’d post this in case people think I’m missing something or am thinking about things in a dumb way.

Your thoughts? Here are some starters for you:

  • I’m on this committee and can’t wait. My favorite part is . . .
  • I’m on this committee and think this is dumb. The dumbest part is . . .
  • Why use spreadsheets at all? Just do it all with the Google Apps Script Form API!
  • At my school I could never do this because . . .
  • Must be nice to be a Google school (it is!)
  • What happens when you leave the dean’s office?
  • I can’t believe you can’t do this with Mathematica!
  • I thought you were a LAMP expert, why not write this from scratch with PHP etc? (quick answer: note the craving I laid out above. That’s hard to do with PHP)
  • I can’t believe you think generating a pdf from a google doc is easy! It took me months to figure that out.
  • Where’s the chair approval part?
  • You do know that if you’re using an outline form you can’t have a single sub-point, right?
  • Why are you reinventing the wheel? This is already done (and much better) here. (insert appropriate hyperlink, please!)
  • Why not just have them submit the application as a google doc?

About Andy Rundquist

Professor of physics at Hamline University in St. Paul, MN
This entry was posted in dean, programming, technology. Bookmark the permalink.

1 Response to Form workflow using Google Apps Script

  1. Pingback: Google Apps Script Physics Problem Database | SuperFly Physics

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s