Devblog

Douglas Gaskell

Author: douglasg14b

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.

Using MahApps To Improve My WPF Visuals

After making my login helper, I realized that it looks pretty crappy. It may  have the behavior I want, but no one it going to look twice at an application that appears to have come from the Windows 98 era. I searched around for a solution, and decided to go with a Metro design. There are a ton of different frameworks for metro-like applications, however MahApps seemed like the best choice for me.

The Setup:

First things first, get the Nuget package via:  Install-Package MahApps.Metro -Version 1.12  this will install the current (as of today) stable release. For more release info go here.  Getting the ball rolling with MahApps is hilariously simple, they have a quickstart guide on their site here that walks you through making your first metro window. Actually, their entire site details most of the frameworks features. I don’t need to tell you how to set this up, just refer to their quickstart as well as their controls guide for that!


 

So I went ahead and stayed up till 4:00 A.M. and made my application look a LOT prettier than it was before. I also played a bit with XAML animations to produce this effect for my password popup:

passwordpopup

Here is a .gif of the new application look: websiteLogins2

I’m happy with the results

Logging Into Websites Programmatically

I recently started a new job providing low-level support for a startup food delivery company.  During my first couple weeks I noticed a small inconvenience that stemmed from the companies disorganized structure.  We have 1/2 a dozen tools and sites that we need to log into when our shift starts, each one requires you to type in your very lengthy company email and password.

Now you may be thinking “wow, 6 logins, no big deal”, but think of it in terms of wasted time. If you have 30 people spending 5 minutes of their day logging into their tools, that’s 2.5 wasted man hours every morning. Not to mention how annoying it is for everyone involved to type out the same login over and over at the beginning of each day. I decided it was my time to shine, put some of my novice programming skills to the test. I went home that night and started working on a WPF application to automatically log into these sites, it only requires you to type in your password each morning.

Most of my coworkers use the same password for all their tools, this means I only need them to type in their password once. For sites they use a different password on, I can just include an option to type in that sites unique password before login. So I’ve figured out what I want to do, now how am I going to actually accomplish this?

If we used Internet Explorer, this would be a piece of cake and I could have just created a PowerShell script to log everyone in, but we use Chrome. This really throws a wrench into my plans, I need to figure out a way to programmatically manipulate Google Chrome into logging into these sites for me. After a little research I stumbled upon Selenium, a library designed to use Web Drivers made for various browsers to programmatically simulate a users input. I wanted to use it for Chrome, according to the documentation this meant I needed to get my hands on the ChromeDriver, an open source Chromoum project.

After many days, lots of research (nearly 400 google searches in 1 week), hacked behavior, and frustrating dead ends. I finally created an application that will log you into almost any website.

Here is a .gif of it in action:

websiteLogins

 

I’ll be making a multi-part post with a general overview of how to make this soon!

 

Alcohol Assisted Coding

(Image Courtesy of XKCD)

I messaged my programming buddy the  other night about creating a server side API for the chat module I’m currently developing for our game. He replies back that he is fairly drunk and that he may or may not make any sense if we get into it. A heated discussion follows while we discuss how the implementation details of the API. Eventually we decided that there was a bit too much alcohol involved and we will pick this back up tomorrow.

It got me thinking back to various jokes about drunk programming , and how the right level of drunk makes for some very productive coding. I decided it was worth putting it to the test on a Saturday night. I grabbed a glass, poured a few shots of vodka into it and filled the rest with OJ, sat down and started work on the chat module.

It was a pretty mundane process since all I was doing was your typical refactoring work. I found myself on youtube more than in visual studio. As the vodka started to take effect, I began to spend less time watching videos and more time in Visual Studio. This continued till I completed my task, as I looked back at my work I couldn’t help but think of myself as a wizard as I evaluated my meticulously commented and structured design.

I ended up making a few types, rewriting all of the methods for the chat box as well as creating a fully commented public API for the serializer in record time. I looked back at my work and couldn’t help but think of myself as a wizard as I evaluated my meticulously commented and structured design.


After some deliberation I believe I figured out why my productivity was so greatly improved under the influence of alcohol. Being buzzed or drunk seemed to have given me a certain sense of apathy towards what I do, I found it easier to stay focused on a relatively boring and tedious task as refactoring.  I was not bothered by the tediousness of the task, regardless of how dull it was my depreciated mind was happy to do it.  After a bit of research it looks like a study was conducted in 2012 regarding alcohol and creative thinking backs up my results. It concludes that the intoxicated group was able to solve creative thinking tasks faster than the sober group. Here is a nice conclusion to the study: How Drinking Makes You More Creative.

All in all, I may end up using a bit of alcohol as a tool when programming. After this experience I am not against recommending others give it a shot and see how it works out for them.


TL;DR: Being buzzed/drunk gave me a certain level of apathy towards whatever task I was engaged in. It facilitated the state of mind that let me focus and refactor my code without distractions. Would recommend, the Balmer Peak seems to have some truth to it. (PubMed)

 

Blog Started

Hello Friends,

My name is Douglas Gaskell, I’m a novice programmer and game developer. I recently started learning programming and game development within Unity “officially” at the beginning of 2015, though I started dabbling in the fall of 2014. I’m an IT Tech by trade and am trying to work my way out of IT support and into a more creative and immersive field such as software/game development.

Throughout this blog I’ll be documenting my programming and game development progress, as well as the specific progress of an unnamed game myself and a team mate are working on under the newly created studio of Verichron Games. The game is set to be a small-scale 2D space RTS with inspiration from The Space Game, StarSector, and Gratuitious Space Battles. Development started in the fall of 2014, and has been steadily moving forward since then.

I will also be posting Unity tutorials, as well as programming assets for Unity developers. Some of these tutorials and assets will cover topics such as object pooling, enemy detection, turret behaviors, and game object reference tracking. If any of you have specific topics you would like to see covered (that would relate to what we have done in an RTS environment) please drop me a note here.

© 2017 Douglas Gaskell

Theme by Anders Noren edited by Douglas GaskellUp ↑