Also, I’ve gotten some questions on the sharing numbers. They are robust and you can use them for proportions, so putting 2 as a number will count that person as owing double.
UPDATE: Awesome, Lifehacker likes it! I’ve gotten request for two- and three-person versions of this beast. Anyone want to help out and build one in exchange for a link?
I’m one for efficiency and productivity tools, and I prefer the lifehacker mindset: spend a long time on solutions that will save time. Even if I end up using more time than I saved, I’ve learned something and created a product as well.
I know that there are many web- and iPhone-based solutions that look pretty cool for this type of thing, but I wanted to make one using Google Docs. The version I came up with has four people in it and allows you to enter up to 100 transactions per person. Under a transaction, enter the amount spent and a 1 under the column of every person who the amount should be shared with. You can use it to share something four ways or for 2- or 3-person splits or loans. Now that we have it, we will regularly pick up dinner for each other and “bill” each other on the tab system. This version also calculates how to settle the tab.
Try it out here, and if you like it, open the read-only version here and go to File -> Make a Copy to copy it to your own Google Docs. And please, let me know if you end up using it or making any improvements!
How to customize it
To modify the app to use your names, expand the AH column, which stores the names that are used elsewhere.
A quick run-down on how I created the app: The thick black bars to the right of each person are actually two columns of formulas. The first one calculates how much that person owes to others; to do this, it looks at each other person’s spending in that row, divides it by how many people are sharing the item, and adds it to the tally if that person is marked as sharing it. The second column calculates how much is owed to that person based on how many people they are sharing with.
The thick black bar on the right edge of the spreadsheet does some Google Docs magic, which you should be able to figure out pretty easily, to calculate how to settle the tab.
Enjoy!
3 responses to “A tab system on Google Docs”
Hi, I like that you’ve decided to share your work. I made something similar to this on Google Docs too, last year, and while it didn’t have many of the features yours did, it had a few i think you might wanna add:
+ Ability for custom price splitting (ie people paying different proportions)
+ A form (which google handles very well) to embed on your site somewhere easier to remember. – I implemented this, but could not figure a way to have the data automatically imported to the right tab, etc.
+ Using Google Docs means (AFAIK) that any collaborator can change things without the others knowing, which leads to potential deceit, or accidental data damage by the not-so-technical users.
Having come up against these problems, I decided to create my own version. It’s written in PHP and stores the data in a MySQL database, and has the following advantages:
+ Ability to decide whether to split the purchase price equally between all users, or manually enter a price for each.
+ Checks that the correct data type is entered (avoids people typing letters in the price field, etc.
+ Data is backed up nightly, and available for exporting as a CSV file (so you don’t need to feel locked in to the site)
+ Logs are kept of all changes made by users, so they can be undone if someone tries to mess things up.
+ All logging in/out is handled through Facebook, so it’s just as secure, and I can’t learn your password.
+ SMS and email notifications can be sent when anyone adds a purchase for which you’re expected to contribute to the cost.
I’d love to see if you can implement some of these features into your Google Doc too. I intended to suggest my own version of the spreadsheet as an alternative the TheStudentSpreadsheet, but if yours gets any better, i think a link here would be much more handy (if that’s okay with you..?)!
Hey Ollie,
Thanks for the comment! This system is still ultimately a hack in that it is not a built-from-scratch app that allows these kinds of things to be implemented easily. I took a look at yours, which is certainly more flexible.
I don’t intend to make anything very much more complex (though anybody is free to do so) because our needs are quite simple. If we each owe different amounts for a meal, it is simple enough to put two line items and vastly more complicated to make one line item handle different proportions. In terms of data types, an error will screw up the whole spreadsheet which should be visible pretty quickly. Google Docs keeps a record of all changes made and by who, so tampering can be detected with a bit of effort.
My personal wish list for SMS is the ability to add items through text, which would be very helpful. Right now I just email myself on my iPhone.
Thanks for visiting and commenting!
Mike,
Thanks for filling me in about the extra things Google Docs can do – Apologies for claiming that it didn’t store history of user actions! I appreciate what you said about putting two lines if the prices are different, and i agree it’s perhaps more hassle than it’s worth, however there’s a house of 7 here, and adding 6 new entries is a hassle too. It turned out to be pretty simple in PHP.
Interesting you should say that you want the SMS ability; it’s one of the key reasons i’m building off the Facebook Connect API:
Receiving User Generated Messages.
Having looked at the pricing of services such as aql.co.uk, i think the Facebook route seems most cost-effective!
I’ll let you know if/when i get it working. At the moment it’s seeming like it shouldn’t be too hard at all…
Talking of iPhones, an app would be handy (and is planned) too, that syncs with the website whenever the phone is connected to the internet. I’m aware that similar apps exist, but for some reason, none seem to be available for free!
Cheers, Ollie
TheStudentSpreadsheet.com.