Building a flight-plan creator with Google Cloud Functions and Sheets
I’ve written about my adventures finding the best toolkits and stacks for solo development of tiny web apps. Recently I was pleasantly surprised by how useful Google AppSheet is for very simple use cases, but when I wanted to generate flight plans for a flight simulator out of real world flight records, I needed something a bit more bespoke.
I didn’t want or need a persistent data store, and I wanted to make sure I created something that could “scale-to-zero” (cost nothing when no one is using it). I did need a big chunk of lookup data, so I built my solution from two Google Cloud Functions with Node.js, a Google Sheet, and a small React web app using the recently released Material UI 5.
The finished result runs here https://track2plan.apps.cronin.nz/.
The “business” problem
I’m a plane-geek (I even make and sell plane-geek t-shirts) and I spend a few hours a week flying in Microsoft Flight Simulator (MSFS). The new version (released just over a year ago now) is jaw-droppingly good: photo-realistic scenery, amazing flight models, dynamic weather — you name it — but I digress…
MSFS can create a flight plan if you give it two airports to fly between, but they don’t always reflect what the actual planes up there in the sky get up to. I like recreating real-world flights, and that’s where FlightRadar24 comes in. FR24 is a website and app that aggregates data gathered from hundreds of amateur ADS-B ground stations along with professional systems. If you see a commercial plane passing overhead, you can generally open the app and see its track (where it’s come from and where it’s going), its make and model, its registration, and stats like altitude, heading, and speed. Paying subscribers to the site can also download historical flight tracks in CSV format — and that’s what I wanted to turn into simulator flight plans.
File formats
The FlightRadar24 export is very straightforward, it’s a comma-separated values (CSV) file with seven columns:
The positions are given in decimal degrees, and the altitude is given in feet.
The PLN format used by most third-party flight-planning applications and for import into simulators is not so straightforward. It’s XML and there is some pretty good documentation for it here. Here’s an extract showing how the CSV above maps onto a PLN file:
You’ll notice there’s a bit of mapping to do to get this right:
- The PLN format has the location coordinates in degree-minute-second format
- The PLN requires the departure and destination airports, including coordinates and elevation (altitude) for these, but the FlightRadar file doesn’t include them
- The PLN needs a cruising altitude, but the FlightRadar file doesn’t explicitly include this
CSV parsing
My favourite CSV parsing library for Node.js is “csv-parse”. It’s a one-liner to parse a CSV with a header row into a usable array.
Coordinate conversion
A quick search found the NPM module “formatcoords”, which is a flexible and well-maintained coordinate formatter and converter. It took a bit of trial and error to get the right combination of format string, padding, and concatenation to generate the esoteric PLN format, but this did it:
Departure and destination airports
I’ll get into the integration with Sheets later on, but I needed to guess the closest airport to the start and end of the flight track. I settled for taking any airports that were plus or minus one degree of latitude and longitude of the start and end coordinates as a shortlist and then sorting these by the Haversine distance from their location to those points. The closest airport is the winner. The Haversine formula is straightforward, but there’s no point reinventing the wheel (or adding lines of code) so I went with the “haversine” module.
Cruising altitude
A long flight doesn’t necessarily keep to a single cruising altitude (where it levels out after climbing and stays there until it’s time to descend) but the PLN wants one. A reasonable guess is the maximum altitude, rounded to the nearest 500 feet.
XML
I does cause me flashbacks of the dark days of trying to resolve dependency conflicts SAX and DOM, Crimson and other such things when I was a Java developer in the early 2000s, but anyhoo — I used “xmlbuilder2” to construct the PLN doc. It offers a nice fluent builder pattern that suits building XML docs.
Google Cloud Functions
In a previous story on building tiny APIs, I explained how I used Google Firebase Functions to build an API. Firebase functions are great, but they are overkill for this use case, because they are optimised for tight integration with Firebase and I don’t need a database. The raw version of these on Google Cloud Platform are Google Cloud Functions. I’m an AWS fan, but as I explained in the earlier story, Cloud Functions outperform Lambda on cold startup times by a pretty serious margin, and that’s important for this application.
I only needed two endpoints:
- One for guessing the starting and ending airports
- One for taking the CSV, along with the starting and ending airport IDs (ICAO codes) and converting this to PLN format
GCP provides an all-in-one command-line tool called “gcloud”. The process of standing up a new function is very straightforward. After running gcloud init to configure access to the Google Cloud project, it’s just a matter of running gcloud functions deploy [function name]with enough parameters to tell the platform how to run the function. The tool will look for an exported function in any .js files in the working directory that matches the function name given. Here’s a simple health check in my index.js file:
…and here’s the command to deploy the new function with an HTTP(S) endpoint:
gcloud functions deploy healthcheck --trigger-http --runtime=nodejs14
Once deployed, the URL to trigger the endpoint appears in the “Triggers” section of the GCP console.
Hosting functions locally for testing is very straightforward. A deployment takes around two minutes, so it’s worth setting up as per these instructions.
Airport data in a Google Sheet
To be able to guess the starting and ending airports and to correctly define their coordinates I needed to integrate airport data. There are a lot of airports in the world, but fortunately there are also plenty of public data sources for these. I could integrate an API, but I couldn’t find one that allowed free usage, or for those that did have free usage, they had crippling usage caps.
OurAirports maintains CSVs with all known public airports in them. I could just bundle one of these into my function, but then I’ll need to read and parse a gigantic CSV on each call (there are over 68,000 airports), and keep it up to date with my build and deploy pipeline. Instead I dropped the CSV into my Google Drive and “opened as Google Sheet” (which converts it to Sheet format).
I searched for the best way to integrate a Cloud Function with Sheets, and discovered that the top answer to a StackOverflow question was too convoluted, and the second-to-top answer had a much better solution. The function can happily “auto-authenticate” using the default service account for the GCP project. Making calls for local development is just a matter of downloading the service account JSON credentials file and pointing to it with an environment variable. The approach is described in full in the Google Node.js docs for the Google API.
To expose the my sheet to my service account, all I had to do was share my sheet in the same way I’d share with another Sheets user by email, but share it with the “client email” for my project’s service account. This can be found in the GCP console or in the credentials file and it follows the form [project id]@appspot.gserviceaccount.com
The latest version of the Sheets API has terrible documentation, but with much trial and error, I managed to retrieve the important columns and rows from the sheet like so:
The front end
I’ve written about my front end approach before. I’m not a good UX or UI designer, and I’m a very average front-end engineer at best, so I rely on scaffolding (Create React App) and a design system with React bindings (Material-UI). I use React Hooks and SimpleR state for state management. SimpleR also provides nice easy to use persistence into local storage for user defaults.
My getting started process (including my favourite HTTP client library) looks like this:
I wanted to make the app as simple as possible to use, so I went with a drag-and-drop approach for the file upload. I used “react-dropzone” to create a surface to drop files on. It’s not pretty, but it works.
Tidying up
When I first tested the results in MSFS, I found the density of the waypoints in the flight plans was too high. I create one user waypoint for each track waypoint, and there were too many of these. I used the Haversine distance again, and simplified the route by dropping any points that were less than five miles from the previous point.
Deployment
As I’ve written about many times, Github Actions have never let me down for CI/CD. For this project I created two Github workflow files: one for the functions and one for the web app deployment to S3 and Cloudfront. Google provides and Action for Cloud Functions, which works as advertised.
Summary and next steps
Using the Sheets API from Google Cloud Functions is a great way to manage large amounts of lookup data. The Cloud Functions tools are easy to use and integrate nicely with Github Actions for CI/CD. I suspect I’ll be using this stack in the future.
The next steps for my actual tool will be to add a few settings the UI to control some of the extra customisation available in the PLN format, and to have a go at guessing real world waypoints, rather than creating my own user waypoints.
