Skip to content
Is my train fucked?

icon picker



This whole tool is built on
(all-in-one doc platform). Took me about 2-3 hours to build the (integration with the MTA API) and this Coda doc that utilizes the integration. The whole tool is driven off of a table that pulls from the API every hour to get updates on subway alerts. If you care about the details, read the post below 👇
A couple weeks ago I came across by Jonathan Vingiano. I believe the site came out in 2011. 13 years later, it still has cultural relevance because the L train is still gets fucked. I actually don’t even take the subway that much anymore. On the times I do, the line gets re-routed, stations are randomly roped off, and I realize why I dislike the subway so much (and opt to bike instead). I thought I could create a simple resource that take the essence of Is the L Train Fucked? and just expand it to all the other lines. Here’s how I went about building Is my train fucked?

Exploring the MTA API

If you go to the MTA’s main page, you’ll see various feeds like this subway GTFS feed. I only wanted to look at real-time feeds so went to . GTFS is some weird format that’s meant for the transportation industry. Not that useful for me because the output looks like this:
Didn’t feel like spending time trying to find a GTFS parser so just poked around and found the . There are JSON feeds which is a format I can work with so I just took a look at the Subway Alerts:
It’s a JSON object but the format is still super shitty. I think it’s because there’s HTML in the feed so that’s messing things up. Threw this feed into and now you’re able to see a better structure of the data:
This feed shows all the latest service alerts that you would see on the subway platform so this data looks promising. So decided to use this feed as the main data source.

Create a Coda Pack

A quick summary of Coda: it’s a mix between a spreadsheet, doc, and application. Coda Packs are integrations. Most Packs are for work like Slack and Jira, but you can basically build an integration with anything that has an API. For instance, someone created an API for every time Owen Wilson has said “Wow” in one of his movies so I made a
for that. The Pack let’s you write formulas that spit out a “Wow” from Owen Wilson as if you were writing a formula in a spreadsheet.
With the Subway Alerts feed, my goal was to get all the alerts into a sync table in Coda. A sync table basically pulls from any API and outputs the data into a table that you can manipulate, filter, and sort like a spreadsheet. The main thing is that the sync table stays—well—synced to whatever data source you want.
I always start with boilerplate code for the Pack so I don’t have to re-write stuff from scratch. I’d say 90% of the Packs I’ve created just pull from a single API. I’ve lost count on the number of times I’ve used this to start building a Pack. It has all the basic code you need to call an API and start getting the sync table set up. The main code for the sync table is below:
name: "Alerts",
identityName: "Alert",
schema: AlertSchema,
connectionRequirement: coda.ConnectionRequirement.None,
formula: {
name: "SyncAlerts",
description: "Syncs the subway alerts.",
parameters: [],
execute: async function ([], context) {
let response = await context.fetcher.fetch({
method: "GET",
url: '',
let alerts = response.body.entity;
let result = [];
for (let alert of alerts) {
let informedEntities = alert.alert.informed_entity;
let lines = [];
let stopIds = [];
for (let informedEntity of informedEntities) {
if ("route_id" in informedEntity) {
} else {
line: lines,
stopId: stopIds,
alertHeadlineText: alert.alert.header_text.translation[0].text,
alertHeadlineHtml: alert.alert.header_text.translation[1].text,
alertDetailText: "description_text" in alert.alert ? alert.alert.description_text.translation[0].text : "",
alertDetailHtml: "description_text" in alert.alert ? alert.alert.description_text.translation[1].text : "",
createdAt: alert.alert["transit_realtime.mercury_alert"].created_at,
updatedAt: alert.alert["transit_realtime.mercury_alert"].updated_at,
type: alert.alert["transit_realtime.mercury_alert"].alert_type,
return {
result: result,

Basic structure of the sync table code

Near the bottom of the code block you’ll see the main attributes I’m pulling from the feed like line and alertHeadlineText. Each alert is an “entity” in the feed. The hardest parts about structuring this feed to put into a table format were the lines and stopIDs. An alert might have multiple lines and affected subway stops so I just needed to loop through the informedEntity to push this data into an array. Again, 90% of this code comes straight from the cat pictures example, so I didn’t really need to do much except figure out how to get the variable number of subway lines and subway stops to work.

Sync table in action

If you scroll down on the main page, there’s a collapsed section called Admin. If you expand that Admin section you’ll see the main sync table called :
This is the core table driving Is my train fucked? I set the refresh interval to hourly which means the table will pull data from the MTA feed every hour. This is “real-time” enough for me since there’s only a handful of alerts every hour.
One small data thing I had to figure out was how to map a stopID to an actual stop name like “14th Street.” The subway alerts feed only has stop IDs like “A19” but that doesn’t meaning anything to a normal human. I found some random text file someone created that has a mapping of every single stop ID to a stop name and that’s what the table does. This table is not a sync table since these stop IDs and names very rarely should change (I think).

Adding buttons

To make this resource feel interactive, I wanted the main interaction point for the user to be a button. A button with a number and color is enough context for a New Yorker to know this is a subway line. These are the settings on each button:
The Label is simply what text to show on the button. The important functionality is the On click setting. “Set control value” is actually a formula behind the scenes. By pushing this button, you can set the value of some other object in Coda. This is the same On click functionality but viewed as a formula:
So what the heck is currentTrain? It’s actually a text box under the Admin section at the bottom of the page. When I right-click this text box, you’ll see I named it currentTrain:
Normally these text boxes are used as a search box or input box when you want a user in Coda to type something into the text box. In my case, I just wanted to output some value into the text box when someone hits one of the subway line buttons. So if you hit the 1 train button, the value in the text box is “1.” With this text box, I can now filter down the list of alerts to just the train line that matters.

Filtering alerts to a specific train

Under the Admin section there’s a filtered view of all the alerts coming from the MTA feed. A filtered view in Coda is just like what you’d expect. It’s a filtered down list that’s always connected to the main table of data. I can also hide certain columns that don’t really matter to me. The main reason I want this filtered view is to easily output some of the columns into the main display of the page:
If I click on the “Filter” option for this filtered list of alerts, you’ll see the filter I’ve applied to this view:
The Line(s) column has to match what’s in the currentTrain text box (and that text box contain the train based on what button you push). The second condition is that the Fucked when column has to be in the last 24 hours. It’s basically looking at alerts only in the last 24 hours. I figured it wouldn’t make sense to pull alerts from a week ago because I want to know if the train is fucked now.

Outputting the alert details

To get the main message at the top of the page to show whether a train is fucked just requires a basic IF formula. This is formula for the main message:
Formulas don’t just have to be in tables in Coda, they can be on the page too. I’m re-using the currentTrain text box in this IF formula. The numAlerts variable is another thing I put in the Admin section. Let’s take a look at what that formula looks like:
Next to the “Alerts?” text you just see a value of true. Since the view only show rows for the train line you’ve clicked on, I can use that filtered view for my numAlerts formula. If there is more than 0 rows that show up in this filtered view, then I know that this train is fucked. That’s why I have the Count() formula. Now I can use the numAlerts “variable” elsewhere in my Coda doc.
For instance, all the detail about the alert is selectively shown based on that numAlerts value:
When you click on a train line and it is indeed fucked, you’ll see details about the alert below the buttons. This is the formula I use to control whether to show an alert message. Notice how I’m re-using numAlerts here. If numAlerts is false (meaning there are no alerts for that train line), then it outputs char(10). This is just a shortcut to output nothing on the page so that the page looks clean:
Without that char(10), you’ll get these brackets which don’t look nice:


I asked a few people for feedback after I published this doc and so far people have said it’s useful and funny. Someone said they just want to see a list of all trains that are fucked instead of having to click on each line, so I added to the page. This is just a filtered view of the main sync table of alerts to the last 24 hours of alerts. Ultimately this doc took me 3-4 hours to build the
and put this Coda doc together.

Pack code and doc are open

There’s a lot more you can do with the MTA API. I made the source code for the Pack available (see
). This Coda doc is also copyable so you can make a copy of the doc to your account and pick apart everything I’ve built if you’re so inclined.

Want to print your doc?
This is not the way.
Try clicking the ⋯ next to your doc name or using a keyboard shortcut (
) instead.