Track Expenses in Google Docs via SMS

Track Expenses in Google Docs via SMS

Tracking expenses is a pain in the butt. At Sqoot, we use Google Docs for almost everything, including expenses, but entering anything on the go is cumbersome. Today, I got frusted and decided to take an hour and hack something together.

I wanted to be able to text something like this:

  • “35 Burgers with the team”
  • “420.15 Hosting costs”

Those text should become entries in a spreadsheet like this:

Description Date Amount
Burgers with the team 5/14/2012 $35.00
Hosting costs 5/12/2012 $420.15

Twilio

I had never worked with Twilio before but it was a cinch.

I set up a new account and funded it. For $1.00/mo, I got myself a phone number that I could start texting. Twilio will POST to whatever URL you specify when a text comes into that number. So getting texts into my app was basically done.

Eventually, I would also need to send back a confirmation text. Turns out that’s pretty trivial as well with the Twilio Ruby gem:

TWILIO = Twilio::REST::Client.new "twilio-account-sid", "twilio-auth-token"

TWILIO.account.sms.messages.create({
  to:   "from-phone-number",
  from: "to-phone-number",
  body: "Success!"
})

Google Drive Gem

After a little sleuthing, I found a Google Drive gem that would let me interact with our expense spreadsheet with just a few lines of Ruby:

require "google_drive"

session     = GoogleDrive.login("avand@sqoot.com", "would-you-like-to-know")
spreadsheet = session.spreadsheet_by_key("...")
worksheet   = spreadsheet.worksheets[1]
row         = worksheet.num_rows + 1

worksheet[row, 1] = "Burgers with the team"
worksheet[row, 2] = "5/14/2012"
worksheet[row, 3] = "35.00"

worksheet.save

This simply grabs the spreadsheet, opens up the worksheet, and adds a row with three columns to end.

Brioche

Finally, I wrapped up all this code into a very simple Sinatra app called Brioche. It responds to a POST to /expenses and expects the parameters Twilio sends over. Brioche then parses the body of the text, connects to Google Docs, and adds a row to a worksheet. Finally, it composes a confirmation message and sends it back via the Twilio API.

It doesn’t handle errors and you’ll need to set your Google password as an environmental variable on Heroku (shudder), but for an hour’s worth of work, it works pretty well!

Cheers!

Thanks for reading! I'm Avand.

I’ve been building and designing products for a decade. Today, I’m the Director of Product at Mystery Science working alongside a small team of educators and creators to revolutionize how kids learn science. I was born in Boston, grew up in Salt Lake City, spent several years in Chicago, and now call San Francisco my home. When I’m not at my computer, I like to ride bikes and make photographs personally and professionally.

Read my other posts or get in touch: