Thursday, March 4, 2010

Google Spreadsheet as an admin panel for your web app

Today, I would like to share a story with you. A story which led us to discover a nice and simple technique for building admin panels.


The customer's request

Recently, in one of our projects (e-commerce), we've had the following requirement:

In order to edit prices
As an admin
I want to export products to Excel, edit them and upload back


At first it seemed strange to me. Why would we want to edit prices using Excel if it can easily be done using the web admin panel which we created. I asked the customer what's the reason and he came up with a fair argument, that it's much easier to mass-edit prices using formulae or using copy/paste in Excel.

I was convinced at this point, but then Yashke (my teammate) suggested that maybe it's better if we do the same, but with Google Spreadsheets (GS), hopefully saving the time for exporting/importing from Excel. The idea sounded pretty cool, so I asked the customer if it was acceptable and I was given a positive response.

The setup we are trying

As always with this kind of risky situations (new technology) we prepared the simplest solution that could possibly work and exposed it in the admin panel. After some discussions we decided for the following workflow.

  • Admin presses "Generate the spreadsheet" in the web admin panel
  • Then he goes to the google spreadsheet page
  • He edits the prices
  • He goes back to the admin panel
  • He clicks "import prices"

As you can see, we're not relying fully on Google Spreadsheets - it's just a tool for editing the data. We still have the traditional web-based admin panel. So the admin logs in to the admin panel and then he can generate a spreadsheet with some data.

This project is not finished yet, but the first feedback is very positive.

The advantages

Our journey with GS started with a single requirement, but over time we discovered many exciting opportunities, like:

  • mass-editing records
  • sorting
  • search
  • search & replace
  • using formulae
  • using colors
  • charts!
I think that the most important argument is that people are used to spreadsheet applications, so giving them a Google Spreadsheet make them feel at home.

In the project I mentioned above, we use it for the following features:

  • mass-editing prices for product variants (we have about 2000 products, each of them can have more than 300 variants, that's a story for another blog post - how assigning prices can be challenging)
  • selecting which products to show on the main page, in what order etc.
  • managing other kind of collections, like bestseller products
  • displaying orders, so that it can be easily sorted, grouped. It's also useful for any kind of reports.

All of these things would be more difficult if we did it in a traditional web admin panel.

Another thing where Google Spreadsheet could be useful is for rapid prototyping. We already have some code that let us easily take a collection of objects and display them in a worksheet, so reusing it will be very easy.

The drawbacks

There are some drawbacks of course:

1. Speed

Exporting 2000 records to GS takes about 1 minute. The good thing is that they start appearing as soon as you press "Export", so you can see the first rows quickly.
The same problem is with importing. It's slow.

2. Relying on Google

We don't know if Google Spreadsheets will live forever, so relying so hard on it may be risky. We try to keep the GS layer simple, so that at any point we can change the implementation to handle Excel or Resolver One files.


Conclusion

So far, our experience with GS is very positive. As we build many web applications for which the admin panel plays an important role, we already consider using GS in other projects, like social network apps (statistics data) or a web surveys app (collecting and analyzing the survey results).

We use exclusively Rails for our web apps, but the idea of using GS can be applied with any other technology.

Let me know if you have any specific questions regarding the topic of using GS as the admin panel. I will be happy to help you.