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 am an engineer obsessed with design. I grew up in Boston, lived in Chicago, and now I live in San Francisco. I built Sqoot, a deal API that was acquired by Clutch in 2013. I worked at TaskRabbit for a hot minute, taught front-end web development at General Assembly and most recently built Canary, an uptime monitoring service. I like to make photographs personally and professionally.

Read my other posts or get in touch: