Google Apps Script

Google Apps Script

I’m sure anyone that reads this is familiar with Google, and has at least heard of Google Sheets and Google Docs. Both being very handy personal or business tools for document and spreadsheet collaboration.

However, have you heard of Google Apps Script? It’s a cloud based scripting language that tightly integrates into Google’s services. You can manipulate just about any Google service, create Docs and Sheets add-ons, and even host fully featured single-page applications (web apps) using Apps Script. It’s based on JavaScript 1.6, so anyone that’s familiar with a C-Family language and object-orientated programming can quickly pick it up, with tons of existing JavaScript documentation on the MDN and W3Schools.

I discovered Apps Script while working on some reports in Google Sheets for my current employer. I needed some functionality that was fairly hard to reproduce as a single celled formula and eventually found my way into the script editor, a cloud based IDE provided by Google for developing, debugging, and running Apps Script. At the time, I had never before used JS or any dynamic prototype based language, but after some fiddling (and learning how to not declare static types) I managed to get a custom function working.

It’s amazingly versatile, here is an already in use scenario:

Your needs:

  • Automatically parse CSV data from zipped email attachments
  • Filter and validate the data
  • Warehouse the data in Cloud SQL, Fusion Tables, or a spreadsheet
  • Provide on-demand reports and charts on both a spreadsheet and a web app
  • Do all of this automatically without human intervention

What you can do:

You can do this with Apps Script. Find the email, retrieve and unzip the attachment, parse the CSV into anArray, modify the data as needed, and either map it to your columns in a spreadsheet or use built in API’s to insert it into your database of choice. Use apps script to query your database and insert the data into your spreadsheet to generate your reports and graphs. Host a web app in Apps Script and serve HTML, CSS, and JavaScript to a client while providing an Apps Script ran server with built in AJAX functions on the client. Using Google’s charts service to build charts from your data sets. All restricted as needed by Google’s Auth to your Apps domain.

This is just an introduction to my thoughts on Google Apps Script. I’ve been diving into Apps Script for almost a year and will be sharing my experiences and tools over the coming weeks, including full sources and working apps with obfuscated production data.

Leave a Reply

Your email address will not be published. Required fields are marked *