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 working on the web for over a decade and am passionate about building great products.

My last job was with Airbnb, where I focused on internal products that helped teams measure the quality of the software they were building. I also built internal tools for employees to stay more connected, especially after the COVID-19 pandemic. Before that, I was lead engineer at Mystery Science, the #1 way in which science is taught in U.S. elementary school classroms. For a while, I also taught with General Assembly, teaching aspiring developers the basics of front-end web development.

I was born in Boston, grew up in Salt Lake City, and spent many years living in Chicago. Now, I call San Francisco my home and Mariposa my home away from home.

I enjoy the great outdoors and absolutely love music and dance. Cars have been an lifelong obsession of mine, especially vintage BMWs and Volkswagens. I’m the proud owner of a 2002 E-250 Sportsmobile van, and he and I have enjoyed many trips to beautiful and remote parts of the West Coast to create good vibes.

What can I do for you?

Read my other posts or get in touch: