Developers, Start Your (JavaScript) Engines!
“If I have seen further it is by standing on the shoulders of Giants.”
– Isaac Newton, modernized quote
It’s 2017 and after talking with former students and fellow developers across different sectors, I see a pattern of proverbial wheels being reinvented. My goal here is to show the substantial benefits of leveraging existing technology in your projects versus being dead-set on building absolutely everything from scratch. By leveraging existing technology, you can focus on what makes your project unique versus reinventing something that already exists.
There is an argument to be made on the opposite side of the spectrum about developers and shops that will shoehorn a project into solely existing technology, in the end missing the mark. As with most things in life, I think there is a balance to be struck. Finding that balance gives you both a better end product and makes a better use of your team’s resources.
I should probably introduce myself. I’m Quinn Madson, dev/tech lead at Thirsty Boy. Outside of work, I’m part of a slot car league that races a few nights a month. The process of keeping the leaderboard and race statistics up to date was tedious and became a full-time job. We entertained the idea of creating an app to capture the data but, ultimately, decided to utilize Google Sheets. If you’ve never used Google Sheets, it’s a cloud-based spreadsheet app. If you are familiar with it, we are likely using it in ways you never knew existed.
There are two main reasons we decided on leveraging Google Sheets versus rolling our own custom app. First, using a spreadsheet interface gives us more flexibility, especially for tournaments. Sometimes the slot car league will change rules or a process on the fly by a majority vote which can be really hard to manage if the app is only expecting data in a rigid, particular way. Secondly, Google has already built mobile apps for both iOS and Android that make working with spreadsheets surprisingly easy on mobile — no easy task. Google Sheets works great on desktop in a variety of browsers, allows for multiple people to edit the data at the same time on any device, and tracks document revision history. So the question becomes: Why attempt to rebuild all this infrastructure yourself or a less feature-rich substitute when it already exists for free?
Instead, we focused our precious free-time on the features that we really need that currently do not exist: tabulating scores and best lap times. One of the coolest features and also least known features of Google Sheets is Google Apps Script available under Tools >> Script Editor. If you’ve ever gone beyond the basics of any spreadsheet software, you’ve probably used formulas like: =SUM(A5:A27) which would add all the values in column A from row 5 to row 27. With Google App Script, you can build your own custom formulas like: =getTotalLaps() or =getFastestLap() to do all the tedious calculations on the fly.
[Fire up nerd-out accordion track]
The Google Apps Scripting language is essentially a slightly modified version of JavaScript that runs on the server side. Google provides an API to interact with spreadsheets via pre-built JavaScript classes and provides developer documentation on what’s available and how to use it.
Take a look at the code example below:
The getTotalLaps function will calculate all the laps a particular driver has earned across multiple races. Each race has its own sheet tab that the function will loop over. To get the lap count, you give the function the name of the driver and the class. Here class refers to race class or race type, if you will, not class in the programming sense. Example race classes would be GT or LMP which have different rules and requirements. The function also takes a parameter called cacheBuster. Google Spreadsheets is pretty aggressive about caching data as one would expect coming from the engineers at Google. There are certain cases where we want to force a refresh to view the latest race statistics. To make this work, we pass in a cacheBuster parameter which is a date string. When the date changes, it forces the data to refresh. Logger.log() is just like console.log() in the JavaScript world except it will output via the server-side for debugging purposes. Next, I use the SpreadsheetApp class to get an array of sheets and then use a for loop to calculate the lap count. Inside the loop, an if statement checks for the phrase “Points Race” in Column A, Row 101 in the spreadsheet. This is used to filter spreadsheet templates and other sheets that aren’t used for scoring. Next, the processScores() function is called to get the actual lap count information.
Another important section of the code is the onOpen() function. The code above adds a custom “Tiny Riders” menu to the spreadsheet document which allows the spreadsheet user to fire custom code when needed. In this case, refreshLastUpdate() updates a date field on the current spreadsheet which in turn kicks off the data refresh process.
See the full source: https://github.com/quinn-madson/Tiny-Riders-Scoring/blob/master/TinyRidersStats.gs
See the example spreadsheet: http://bit.ly/tiny-riders-example
[End nerd-out accordion section]