Automating with Apps Script


Paper is not bad

Quite frequently at work, I am accused that I "hate paper". Let me go on the record saying that I am quite fond of paper. Being an artist, I probably like it more than the majority of my coworkers. What I do have a passionate disdain for, however, is filling out paper forms. I really can't think of a good reason for this to be done. 

Paper forms are bad

I work in education, so paper form filling is, unfortunately, a frequent activity. Forms represent data that needs to be collected. Google forms and other digital form systems have been available for quite some time. Sometimes a simple form is all you need, but many times the data is being collected in order to perform other actions as well.

Data is usually Actionable

Maybe an email needs to be sent, a document needs to be merged, an event needs to be put on the calendar, a spreadsheet needs to be managed, or a website needs to be updated. These are all areas Google G Suite already specializes in doing.

G Suite is sweet

Our school uses googles G suite for education. There are so many great things you can do with the core apps alone, however, you don't need a blog post to figure that out. The ability to automate programmatically with Apps script is really the most intriguing part of the equation for me. I bet some of you are thinking "Programming? Scripts? Equations? but I'm not a developer and I don't like math!" No need to worry, I barely made it past algebra 2 and I'm an art teacher. However, I am a rather curious and I like to tinker things, so when I saw the following video a couple years ago I was fascinated to learn more.


So what can you automate with scripts?

I started off by politely recreating and then sharing the resulting google form to our secretaries whenever I received a paper form in my box. They caught on pretty quickly. The automation fun began when I helped switch our school to an entirely new Student Information System (SIS). It was missing a few features so I started exploring how I could fill the gaps.

Sometimes less is more

The SIS had some workflow ticketing features but made managing them a little too complicated. Sometimes the easiest solution is a simple spreadsheet with rows and columns. I first created a ticketing system for our IT and Maintenance Directors with the following features. 

  • Web forms limited to our domain for faculty and staff to submit
  • Text or email notifications for the directors based on different priorities
  • Confirmation responses including their position in the queue
  • Weekly summary emails to directors with top tickets embedded
  • "Open", "Closed" and "Pending" sections that will automatically move as the status is changed
  • Ability to generated status updates for the initial ticket requester
  • Ability to share spreadsheets with multiple people

While there have been a few adjustments over the last couple of years, the forms have worked quite reliably. Last time I checked, over 2000 tickets have been closed by our IT and Maintenance staff. 

More people + more approvals = more problems

I get it. People have to approve stuff. But do they really need to sign a piece of paper? Of course not. Red tape can be necessary, but making the approval process a couple clicks makes the whole process a bit more enjoyable. The problem we needed to solve at our school involved getting a way for the division principle, transportation director, and school office to all sign off for an off-campus activity. To make this easier, I made another apps script powered form and spreadsheet that did the following:

  • Google Form that collected all required information
  • Formatted the notification for each person differently to emphasize information that only they would need
  • Linked to approval
  • Notified everyone once everyone had approved

Everyone could easily click on the date in the email and be taken to their calendar to see what else was going on at that time. or they could click the link to approve the event. Better yet, if there were problems or follow up questions they could easily reply and ask the sender a question.

Combining Digital & Print workflows

Sometimes you still have to print stuff. Remember, I don't hate paper. For our school, this was true for the student inquiry application. Once a parent filled out a form online, that information needed to put into a permanent file. As a designer, I would appreciate a well designed official document showing up in my inbox after filling out an inquiry form. Having the first document they received from our school look professional on screen and on paper is a good way to develop trust. The goal then was to design an inquiry form for our website that would do the following:

  • Web form with applied branding accessible from the school website
  • Auto Merge answers to a pdf and archived a version in a google drive folder
  • Conditional fields for sending the result to the appropriate divisional secretary
  • Automatic response to the parent that included the pdf and instructions
  • Charts to analyze data for incoming students

Our system was far from perfect, but at least we were tracking leads digitally and then incorporating that into a print workflow. 2 years later I have been pleasantly surprised to see how effective the system has been. Eventually we would like to build in the ability to schedule schedule a tour using a similar process and have reminders and follow up messages go out for those that attend.

Scheduling is tricky

A large problem we have run up against at our organization is keeping everyone in the know about whats going on. We started using google calendar quite a few years back as it was an easy way to keep everyone on the same page. Unfortunately there are a few aspects of a school that add to the complexity of scheduling.

Problem 1: People plan a lot of activities in a limited amount of space

Problem 2: Every activity requires different things from different people at different times

Problem 3: Too many steps and too many systems will make it too inconsistent

Notifications for next Actions

To solve all of theses problems I wrote a custom apps script that focuses on next actions. People can focus on taking action when the busy work is automated and the reminder fits the action. That way when an event is proposed through the google form that it automatically gets added to a shared calendar so everyone can see it. Then people in charge are notified to make sure they are no problems. Since its already in google calendar, it can easily be moved to the official calendar. When that is done, then the rest of the people with responsibilities get notified with their specific next action.

For some people that is getting a reminder for the day of the event and for others that is setting up other resources to help the event go smoothly. Others may get an email right before the meeting with an agenda and action steps included.

Simple enough

I could continue with a few more examples, but hopefully you get the point. Google Apps Scripts is simple enough to be manageable and powerful enough to be useable. Taking some of the headache out of managing multiple services and employee accounts is part of what makes it enjoyable to use. The added bonus is that it is essentially free for schools and companies with G Suite.


Carlton Riffel