Devblog

Douglas Gaskell

Category: Programming

Google Apps Script – Limitations

I was going to post about best practices, but I thought that giving some insight into what kind of limitations you as a developer will run into when trying to utilize GAS for your project. This is not a comprehensive list, and are just limitations I’ve personally ran into.

Execution Time Limits

You have a restricted window in which to execute your script, as well as account wide total execution time limits.

  • 6 Minutes for a script ran manually or via an Installable Trigger
  • 30 seconds for a script ran via a Simple Trigger
  • 1 hour of aggregate trigger execution time per 24 hour period for a consumer account (*@gmail.com) *
  • 3 hours of aggregate  trigger execution time per 24 hour period for “Apps for Your Domain”  *
  • 6 hours of aggregate  trigger execution time per 24 hour period for  Biz/Edu/Gov accounts *

* Manually executed scripts do not count towards these totals.

As far as execution speed is concerned, GAS is surprisingly fast for a free cloud based scripting language.  After running the following function a few thousand times I averaged out at 236,538 operations/s :

Limited Access to the Browser with Web Apps

If you want to change the URL or reload the page with JavaScript, you might run into some roadblocks. Apps Script runs in an IFrame sandbox based off of the iframe sandboxing documented here.  Per the Apps Script Documentation it’s equivalent to using  the linked sandbox with  allow-same-origin ,  allow-forms ,  allow-scripts , and  allow-popups . This means you can utilize JavaScript, Forms, and Popups such as  window.alert() and  window.confirm() . However, you cannot redirect the users browser, only the iframe, so if you want automated redirects you might be out of luck.

Persistent Logs

Apps script does not provide a built in method for permanently or even semi-permanently storing logs or execution transcripts. The log viewer is overwritten each time your script is executed. Thankfully someone has already created a library that tackles this very problem, BetterLog. BetterLog enables you to  write logs and stack traces of varying levels to a spreadsheet so you can maintain some permanence.

External Library Usage

If you want to plop in an external library , such as JSON Web Tokens, you will need to make some heavy modifications and have a deep understanding of its workings to port it over to GAS. This is mostly influenced by having server side JS execution, you don’t have access to typical objects or functions you would find in a browser, so any library that relies on those will typically fail to run or even get past the syntax checker.  Some libraries are pretty easy to port, it just takes some time to port each function one at a time.

However, GAS does provide the ability to import other GASs scripts as libraries. Enabling you to use other scripts you have written as external libraries, or to use libraries from other users within your script. This does come with a small performance cost though, so be wary of that.

JSDoc Support

Related to external library usage, GAS libraries only support the @param  and @return  tags for it’s auto-complete and documentation generation. This means if you want your functions to be visible via autocomplete, they must be top-level. You cannot nest functions under other objects in a class-like fashion and still have them show up in autocomplete.

IDE

The IDE is a great tool provided by Google to write and debug your Apps Script, but it definitely has it’s issues:

  • Limited file organization within your project. You cannot organize your project files into a hierarchy, instead you must use a long list of top-level files. You also cannot re-arrange these files, you can either keep them in the order based on their creation, or order them A-Z
  • No ability to minimize code sections to make your code more manageable.
  • No auto-complete for functions or variables within your file
  • Debugging between files will often fail,  it will step over functions located in other files when you try and step into the, and will often miss breakpoints not located in the file execution started in

 

JavaScript/ECMA Script Version

GAS is based on JavaScript 1.6/ECMA v3 and includes some features of JavaScript 1.7 and 1.8. You won’t find many ECMA 5 features in GAS, never mind ECMA 6.  This does come with it’s own issues, as JavaScript and ECMA move forward GAS slowly falls behind in it’s feature sets.

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.

© 2017 Douglas Gaskell

Theme by Anders Noren edited by Douglas GaskellUp ↑