Google Apps Scripts with Python

If I didn’t know better I would think the title above was the attempt to use a bunch of buzz words and string them together.
¯\_(ツ)_/¯

I wouldn’t do that to you, though. Apps Script is a JavaScript-based language that Google provides to run code on their different productivity tools. If you’re used to Excel, think of it as “Excel Macros, but for Google Sheets.” (Also, the Excel Macro language is one of the few languages uglier than JavaScript, so consider it an upgrade of sorts.)

Instead of just running the Apps Script (“Macro”) within a spreadsheet, though, you can also run it with code from a computer somewhere–my language of choice is Python.

This isn’t just some cool party trick or online Rube Goldberg process. Here are some of the ways we use this at Noble:

• Create a set of Google Sheets, one per high school, from a raw data file that has data for many students across many high schools; in this case, the Google Sheet for each campus has one tab with the student data and other tabs that summarize them
• Create a set of Google Sheets, one per graduate (1,000s of them), where students can track progress against their goals and we can monitor that progress (and reach out to help as needed)
• Create one Google Sheet per high school with college applications for every senior and a place to log the college aid awards for each student; this system syncs with other systems every week

In the latter two examples, what we’re doing is essentially a replacement for a traditional database-driven website that interacts with users. It’s less robust, but has a few advantages:

• Prototyping can be faster and done by more people because spreadsheets are easier than coding for most people
• Users can add stuff to the Google Sheets–new tabs, new columns, comments; this is important for adoption because many users will give up on a website if it doesn’t do “that one thing”
• You can still download the data for other uses (including a website)

Setting up systems like this takes a bit of configuration time that can be a little tricky. To make it easier, I’ve created a starter project that takes care of the basics.

You can see the technical details here, including a detailed “Getting Started” guide in the Readme. If you’re curious about how some of this works, you can also look at the heavily commented example script.