My favorite Google Apps feature: scripts

I was disappointed when I learned that we use Google Apps as our main collaboration and productivity suite at Ministry of Supply, until I discovered Google Apps Script.

I’ve found that Google Apps is convenient for creating and collaborating on basic documents, but as a power Excel & Powerpoint user (thanks, consulting!), it’s frustrating to lose all of the accumulated knowledge and advanced functionality that I’ve come to rely on. And while I’ll dive into Google Apps to create simple throwaway documents (it’s faster than starting Office or Office Online on my Mac), I still do the bulk of my work in Office. But I’m increasingly looking for ways to make GApps work for me, just so that I can use Apps Script.

Apps Script is a Javascript cloud scripting language that’s embedded in Google productivity apps. The great thing about it is that you don’t need to set anything up to start using it. You just go to the script editor, which has been hidden in plain sight in the toolbar all this time.

Script editor in toolbar

Finding the Script editor in Google Apps

As long as you know a bit about programming, it’s fairly easy to start scripting things and adding new functions to the apps that anyone you’ve shared the doc with can also use.

Google Apps Script Editor

Google Apps Script Editor

Within one day of discovering Apps Script, I was able to automate a task that would have sucked up more and more of my time as we opened more retail stores: maintaining a central events calendar.

Every store participates in and hosts events on a fairly regular basis, and it’s helpful to have a view of everything in our events pipeline. I created a form to let the stores add events to a shared spreadsheet, and I thought I was going to have to manually add those events to the calendar every week. That would have been super tedious and error prone, especially given the different timezones. That’s what made me start looking for ways to automate things in Google Apps, leading me to Apps Script.

portion of the form for submitting events

Portion of the form for submitting events

We still use the form for submitting events, but everything that happens after that is now wonderfully automated. Once a new event is submitted via the form, the data is moved over to a consolidated list of events in a different sheet that we can export to do any analysis. The event is automatically added to our shared events calendar, color coded for our different locations, and adjusted to the appropriate timezone for whoever is looking at the calendar. Finally, the person on point for that event is sent an email from yours truly with a link to a feedback form created specifically for that event.

Event feedback email automatically sent to the person quarterbacking it

Event feedback email automatically sent to the person quarterbacking it

Although it took some time up front to set this up, it was completely worth it, because I was able to put this part of my job on autopilot, allowing me to focus on other things. 

I’m still playing around with Apps Script and using it whenever possible. Although I have started coming across some limitations, I still love the fact that it’s there, and I didn’t have to do anything to get it.