I have an app on the iOS App Store called myLS. This app shows the somewhat convoluted block schedule we use at Lincoln Sudbury as a list of blocks, and allows the user to show her or his personal schedule superimposed on those blocks. It also allows the user to attach notes to those blocks, and it shows the school’s announcements. The first version of the app, written in a language called Objective-C, was published in 2013. I rewrote the app in 2015 using the newer Swift programming language, and so most of the code dates from then. It has been updated each year to the newest version of Swift. The biggest change since 2015 was the addition of Apple Watch support in 2016.

Inside the app code, the calendar is a single JSON file. It’s pretty long by human standards, but not so big by computer standards. The full year calendar, with every block’s start and end time, is a 165 KB file. Here is a screenshot of a bit of it.

A bit of the cal.json file that holds the calendar for myLS.

I don’t write this file by hand. Up until this past Fall, I created it using a Ruby script that scraped our school management system, called iPass, to get the calendar. This year, however, we migrated from iPass to a new system called Aspen, and I could no longer create this file with my Ruby script. I needed a new way to either create a new calendar file with exactly the same format, or I’d have to change quite a few lines of code in my app to match a new calendar file format. And I’d still have to find a way to generate a file in that format.

I chose the first option, to create a new calendar file with exactly the same format. And I decided to try to do it with Google Sheets to hold the calendar data in human readable form, and with a Google Apps Script to create from that the new JSON file. Google Apps Scripts allow a programmer to use the Javascript programming language to read and write to Google Sheets and Google Drive, while performing the necessary manipulations of the data to put them in the form I need.

My Google Sheet has a list of every day in the calendar, but does not list every block. Our calendar has a ten day rotation, with a few (but not that many) special days interspersed. Days like MCAS exam days, and Midterm exam days each have their own block rotation.

Days of the year, along with their number in the rotation.

The day numbers each have an associated block rotation, which gets its own sheet.

Block rotation for the day numbers. The actual chart extends a good way to the right.

The script itself took awhile to write. I spent most of that time fighting with date formats.

The beginning of my Google Apps Script that turns my calendar spreadsheet into a JSON file.

At first I figured I’d take my JSON string and put it into a cell in the Google Sheet and just copy it from there. But Google Sheets have a 50,000 character limit per cell (who knew!), and my JSON string for a year had close to 300,000 characters. I created a new folder in my Google Drive called “MyLS”, with a folder inside that called “Versions”. Inside “Versions” I create a new folder for each version of the calendar I create. The newest one is “10”. And then the cal.json file goes in that folder.

Overall, I’m pretty impressed with how easy it was to use Google Apps Scripting. In particular, Google’s documentation for same is very well organized and easy to follow. Next year, I’ll have to enter the new calendar, but that won’t take too long. Then I’ll have to tweak the block schedules for a few testing days, and I’ll be able to generate the cal.json file for 2019-2020.