Lifelogging with Reporter and Airtable

I never thought I was interested in a cloud-based software solution that, at its core, is a supercharged spreadsheet, but here we are. After being tipped off by a recent MacStories article, I’ve been tinkering with Airtable for the last few weeks as a journaling app.

I’m not sure that’s what the designers had in mind, but since it’s a relational database, it’s actually a really good tool for the job. It’s power is in “linked records,” which allow individual cells in one entry to link (as the name would suggest) to their own record. The results are cross-referenced and really robust entries. See it in action in this video:

Linked records make Airtable great for something like a beer log. By setting both the brewery and the style as linked records, it’s easy to see every IPA you’ve had recently, or how often you choose something from Bell’s. And since you can have multiple “bases” (Airtable’s shorthand for “database”), this journal can live alongside others in the same app.

I wanted to push it a bit and see if Airtable could keep a basic record of my day—a “lifelog,” if we must. To date, one of my favorite apps for this has been Reporter, which uses randomly timed surveys to create small snapshots of your life. Unlike Airtable, Reporter’s linked records (which it calls “tokens”) cannot be further edited or expanded. So, in this case, it seemed like Airtable could fill in that gap, as well as be able to run some more advanced queries.

But as powerful as Airtable is as a database, I soon learned it has some serious limitations when it comes to data entry. For starters, it’s not built for speed. The entire app is a webview, so you need to have a data connection for it to even load. Secondly, Airtable’s iOS app doesn’t pull in data from your phone’s sensors. So while it will auto-populate the current time, it can’t pull in your geolocation, step count, or other related data.

If you’ve read this far, you’ve probably already guessed that my next thought was to combine the two—using Reporter’s quick data entry and sending it to Airtable for more detail and analysis. Thanks to Airtable’s very well-documented API, this is possible. It’s not exactly elegant, but it’s possible.

The Tools

  • First of all, I run this script on a home server, and I know that’s not something everyone has lying around. If you run it on a laptop that you often carry around with you, this should still work, but it’s possible you’ll miss some data. I’m sure you could make adjustments to the script to account for that, but I haven’t made those adjustments here.
  • The script relies on the indispensable Hazel.
  • Reporter and Airtable, obviously.

Airtable Setup

First, you want to make sure that you set up your Airtable base to grab everything you want from Reporter. My reports ask 4 questions: “What are you doing?,” “Where are you?”, “Who are you with?”, and asks for a “Tag.” These are easy enough to create in Airtable rows; I called mine Activities, Location, People, and Activity Tag (no clue why I didn’t just go with “Tag”). I set “Activities” as the “Name Field” in Airtable, which is the default first column. I then set Location, People, and Activity Tag as linked records.

Reporter also pulls down a ton of data in the background, without any user input. I decided to grab timestamps, latitude, and longitude for sorting and later analysis.

Lastly, I created some Airtable-specific columns that exceed what Reporter can do. I added an “Attachments” column, which allows for a picture, and a long-text field called “Notes.” All said and done, it looks like this:

click to enlarge

Script Setup

First, visit the Airtable API docs, select your base, and find your base id and your key. You’ll need those later.

Second, go to your Reporter settings, and in Export Settings, make sure “Save to Dropbox” is toggled on. This dumps your reports to your Dropbox in real-time.

Third, set a Hazel rule that looks for changes in your Dropbox->Apps->Reporter-App folder. I use the handy rule “Date last modified” is after “Date last matched.”

With these two things in place, the script can read from your Reporter JSON and send it over to your base.

Script Explanation

The script grabs my information from my Reporter reports and attempts to match them with data in my Airtable database. The emphasis is on mine, as you can see, because unless both your Reporter and your Airtable match mine exactly, this won’t fly. (And even then I’m not sure.) This is really more of a proof-of-concept that universal, generalizable script. I wanted to share it with the hopes that other people could make it work for them, but it is NOT a one-size-fits-all solution.

Because I’ve found Reporter’s JSON to be a little inconsistent (they’re working on it), the script actually uses grep searching to find questions, instead of hard JSON parsing. This allows for some flexibility if your questions are worded differently than mine. Look at lines 43, 47, 49, and 51. Those just look for specific words in the questions. You’ll notice that in my case, tags and people can contain multiple entries, which is why they are set up as arrays, not single variables.

Starting at line 73, I’m basically just scraping the Airtable API to find IDs that are associated with entries. The API uses these IDs instead of human-readable names, so things like “John Doe” need to be converted. There might be a way to do this with less API calls, but this is working for me so far.


This specificity of both of these apps makes even posting a script like this a little difficult. But I’m hoping it might provide a rough template for your own ideas about how to connect these tools and work with your data. I probably won’t be of much “help” to the nearly infinite amount of use cases out there, but if you have some specific thoughts or feedback, please don’t hesitate to leave a comment or find me on Twitter.

Create Your Own Menu Bar Time Tracking App with Doing and TextBar

Here’s a thing I’m learning about myself: I like my applications local. Don’t get me wrong—I use a handful of “services” that have great APIs, sync across multiple devices, and generally earn their $4.99/month price tags or whatever, but apps just feel better when they’re not a web wrapper. And when they are cheap or free.

So after messing around with nearly every time tracking app and service in the world, I realized that my favorite is the incredibly bare bones Doing by Brett Terpstra.

Doing is a command-line tool with a massive amount of customization and power, but in essence you simply tell it what you’re doing and then you tell it when you’re done. You do that from Terminal, and it writes it to a plain text file. Simple.

My doing file as viewed in Sublime Text

The problem, though, is that it’s hard to get meaningful real-time feedback without keeping said text file open all day. Enter TextBar, an incredibly handy menu bar app that displays, you guessed it, arbitrary text in your menu bar. However, it can also show the output of scripts—and it can handle emoji.

So I created a script that reads from the doing file. If you’re not working on anything, it displays the “typing” speech bubble icon for encouragement. If you are working on something, it displays the name of the task, a clock emoji, and the amount of time you’ve been working. Like so:

Here’s the bash script that makes it all happen:

You’ll need to a) change the path to your doing file on line 5, b) make the file executable, c) store it somewhere, and then d) point TextBar to it. I have it set to update every sixty seconds, since it only tracks down to the minute.

Let me how this works for you, or if you have any questions. And thanks to Brett and Rich for the help and feedback—and for making great tools.

Theming Evernote

As I mentioned last week, I’ve been getting back into Evernote as an all-purpose database platform. However, there were still a few features that I missed from some of my favorite text editors: control over the color scheme, and a “distraction-free” full screen writing mode.

However, since Evernote is basically an HTML editor, I suspected there might be a way to style it. And sure enough, after poking around a bit, I found a CSS file which controls how the notes look. (And just the notes—I couldn’t find a way to style the sidebars.)

In less than hour (it would have much less if I knew more about CSS), I was able to both “solarize” my notes (sort of) and create a much more readable full-screen mode by limiting note text width.

Easy, breezy contrast

If you know what you’re doing, then you can find the file in /Applications/ Also, since this is as a hack, be sure to save a backup of this file—it will be overwritten every time the software is updated or needs to be reinstalled.

If you need more help, here’s a step-by-step walkthrough:

Find the File and Change Permissions

  1. Control-click on the Evernote application and choose “Show package contents,” then drill down into Contents -> Resources. In there, you’ll find a folder named “mac.”
  2. Control-click on that folder and choose “Get Info.” At the very bottom of the info panel, you’ll find “Sharing & Permissions.”
  3. Unlock the little lock with your admin password, then add admins to the list using the plus sign. Change the permissions to “Read & Write.”
  4. Finally, click the gear icon and choose “Apply to enclosed folders.”

Modify the File

Once you are in and have write permissions you can open the folder and find a file named “en-mac-min.css.” If you’ve never worked with CSS files then the default program will likely be Safari, but you’ll want to open the file in something like Text Wrangler. Then, you’ll have one very long string of text which controls how your Evernotes look. In order to get something that looks like the image above, you can just delete the entire file and replace it by copying and pasting this whole mess of text.

Good luck—and please feel free to share any additional theming tips and tricks.

Markdownifying your Evernotes - Update

Typically, almost as soon as I published yesterday’s post about writing and backing up Evernote files to Markdown, I started tinkering with it to make it both more robust and easier to run on any individual system.

This new script does three things. First, it now adds support for tags, which requires installing jdberry’s tag script. You can install it with either MacPorts or Homebrew. (And as a reminder, the script also requires Pandoc, which can be downloaded here.) Secondly, it is now designed to run when Hazel detects changes to your Evernote files.

Here’s a screenshot of an extremely simple Hazel rule, which acts on the folder /Users/USERNAME/Library/Containers/com.evernote.Evernote/Data/Library/Application Support/com.evernote.Evernote/accounts/

Finally, this version makes it more obvious to select only certain notebooks to act on. Overall, I think this version just makes a lot more sense, and has been working really reliably for me for the last 24 hours or so. Let me know in the comments how (or if) things are working out for you.

Here is the direct link to the gist, which is also embedded below.

Writing and Saving Evernotes as Markdown

[Update: See this post for a more recent version of this script.]

I think I might have found a way to use—even love—Evernote, in spite of my misgivings. As I’ve noted at least once before, I’ve never been a huge fan of the software because it’s not plain text and is all but impossible to work with your files outside of the Evernote ecosystem. That said, it’s a really powerful ecosystem with lots of third-party integrations. The new “atlas” function is especially cool; when paired with something like Day One you can geolocate much of your life and work.1

So last week I went back to check out it—for the dozenth time—this time with the intention of utilizing a script that watches Evernote for changes so that files can be viewed in Marked 2. I assumed that script had something to do with Markdown, but it actually doesn’t: it just copies Evernote’s HTML into a dummy “markdown” file that Marked can display. As such, any kind of markdown syntax results in errors in Marked.

However, after messing around with the script a little bit, it became clear that it was possible to leverage Evernote’s Applescript and the awesome Pandoc utility to write Evernotes in Markdown format and then save them in near-real-time to a Dropbox folder. Then, you can simply drag that folder into Marked 2, which will update the changes on the fly. It even allows for the use of custom footnotes.2

Evernote file on the left, Marked 2 on the right

There are many caveats to how this works right now, based on my workflow: First of all, the backups happen individually for the note you are currently working on. Secondly, the sync is one way, so it’s really just using it for backup. (I think it’s possible to do it the other way, though I’m not quite there yet.) The second is that if you rename notes you’ll end up with duplicates. Right now, I’m treating that as a kind of necessary evil, and not that big of a deal: the files are small, and I would rather have duplicate data than data loss.

Here is the code, which feels a little Frankenstein, but has been working great for me over the past week. You need to change the path to your Evernote folder in line 7, and you should change the path your Dropbox storage (or local storage, whatever) in line 8. You can also add an “if” statement in the Applescript portions so that it only pulls from a specific notebook. I’ve found that really helpful.3 There is some crazy gsub action that happens because Pandoc escapes everything, but again, it seems to work. This script runs continuously in the background. Let me know if you try this and it works or doesn’t for you. You can find the gist here.

  1. Free idea: What I would really love is an app that takes a page from photo software and geolocates files based on GPX data.

  2. For some posts on my Markdown footnote scripts, look here.

  3. For example, you can add lines like if notebook of item 1 of the_selection is (notebook named "Writing") then / return HTML content of item 1 of the_selection / end if