Tuesday, April 19, 2011

The Spreadsheet Story 1: The General Idea

There is this spreadsheet project I have been working on. My wife works at a day shelter for homeless and formerly homeless women, as well as their children. At this shelter, they make use of an incentive point system. The ladies do some chore or favor, and they get points. There are preset ways to get points, with default values. Helping with the recycling gets this number of points, while putting chairs up gets that number of points. The points are just defaults, though. A client can get more or fewer than the default points depending on the situation. The staff can also create new chores or reasons for awarding points pretty much at their whim. A couple of times a week clients get the opportunity to spend those points in exchange for items.

It's a very popular program. Clients are constantly looking for ways to get more points, and asking what their point total is. Over the years, some clients have accumulated tens of thousands of points.

Tracking incentive points was a very tedious process involving punch cards, calculators, and the occasional mild profanity. I volunteered my geekiness to help come up with a better tracking system. I am mostly a Web programmer, so naturally my first impulse was a full-scale Rails, Django, or maybe even Catalyst Web application. Why not, right? It does sound like the perfect job for a CRUD framework:

  • Add and edit clients
  • Add and edit ways to get points
  • Log point changes for clients
  • Get the point total for any given client

There was just one tiny flaw in my proposal, which my wife was kind enough to point out: nobody would use it. The staff would prefer to keep things in a familiar framework, such as a spreadsheet. Spreadsheets are nice. They may not be the perfect choice for a database, but they do have a lot of built-in functionality that would take me forever to implement on my own.

Okay, I'm flexible. I made an Excel spreadsheet. I learned enough Excel to add some formulas and data validation rules. I even learned enough VBA to add some interactivity, reducing the tediousness a bit more. Well - reducing the tediousness for them. Not so much for me. Visual Basic is an interesting language, but I don't care for it.

What if I could use JavaScript? Google Apps Script uses JavaScript to add programming logic to spreadsheets and other documents. I don't know if it would be any easier than using VBA in Excel, but I know it would be more pleasant for me personally.

I have decided to go ahead and try it, now that the dust has settled on the Excel version. Hey - if it works well enough, they might actually use it. Regardless of whether it actually gets used, it'll provide a reasonable example of adding niftiness to a Google Spreadsheet. Somebody's bound to find that useful. Right?

The important thing is that I'll have some fun.

The Spreadsheet Itself

I can almost pretend this is a MVC application. The spreadsheet itself is the model layer, with each sheet representing a specific model. My knowledge of spreadsheets is incomplete at best, but the available formulas don't seem to provide the validation constraints that I'm looking for. It looks this will be what those in the know call a "fat controller" approach, with a disproportionate amount of the logic going into the scripting layer. That scripting layer, driven by Google Apps Script, will handle lookup and validation details. At least, it will until I figure out more about how Google Spreadsheets works. The scripting layer will also provide a view, insulating users from the worksheets by presenting dialogs for the most common tasks.

Yeah, I know. It's not really MVC. I have made a terrible analogy. But at least my terrible analogy has helped me divide the thing into logical components, rather than just looking at it as a spreadsheet with some scripts.

So. Let's look at the worksheets. I also made mock ups of the common task views, just for the fun of it.


Presents information about the clients that take part in the incentive program.

Name Used
The most common name used by this person. Must be unique. That's generally handled by including the initial of the last name or a nickname.
Full Name
The full name of the client, if available.
Other Names
Nicknames and aliases are common. Use this field to list any other known names for the client.
Starting Points
How many points the client had when the spreadsheet started being used. Nobody wants to lose their accumulated points, and this provides one way to differentiate it from points gained after. Could also be handy for importing, such as setting up different workbooks for different time periods.
Total Points
How many points this person has, after gaining and spending is taken into account.
Add Person Dialog


The different ways to gain and lose points. Pretty much a list of predefined chores and a couple of catchall buckets.

A unique name for this point category, like "Wash breakfast dishes".
Default Points
Unless the user specifies otherwise, this represents the gain or loss in points for the client.
Add Category Dialog

Points Log

This sheet contains records of the actual transactions which affect a client's point total. It depends on the other worksheets for some of its information.

Who gets the points? 'People'!'Name Used'
Points Category
What are they getting the points for? 'Categories':'Name'
How many points are they getting? Based on 'Categories':'Default Points'
When did they do whatever it was that got (or cost) them points?
Points Logger Dialog

What's Missing

There is no sheet to track inventory for items available in the incentive store. The items and their value vary too much for this to be a practical feature right now.

What Do I Have Now?

I have an incredibly dull spreadsheet.

What's Next?

I plan to spend the next few days - or weeks, depending on how much bloggy spreadsheet time I have - exploring Google Apps Script, particularly the Spreadsheet and Ui Services, in order to implement the dialog boxes I have so lovingly created mockups of. I will be taking it in small steps, depending on what I can manage in my copious free time. My next post will cover the simplest dialog: creating new Categories.


Saqib Ali said...


This should be relatively straight-forward to build using Google Apps Script, and if you are familiar with Google Web Toolkit UI Widgets, all the better. But even if you are not, Google has plenty of examples for UI Widget samples that you can harvest. Shouldn't take more than few hours to get this working. Ping me (@weaselese) if you get stuck.

Good luck. and no I don't work for Google :)


Brian said...

Thanks! I may end up asking for help in the details, but it's pretty much done already. It'll take me a little while to cover the details because it takes more time to talk about code than it does to write the code.

I hope to post some more on it tonight. That depends on what time I get home from work.

Saqib Ali said...

awesome! i just love the simplicity of using GWT and Google Apps Script in general :)