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.


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.


Michał Szajbe said...

Nice idea.

Would be great if such a spreadsheet could be embedded on a page.

I imagine a javascript library (no backend) that supports simple formulas, sorting, etc. Don't think something like this exists though :/

My notes said...

So, I'm wondering, is there a way to embed the spreadsheet control within your control panel, or do you redirect the client to the appropriate google doc and then return?

Andrzej Krzywda said...

Currently we show a link to the Google Spreadsheet and the admin can open it in a new tab. Not the best user experience.

I don't know if there's any way of embedding the spreadsheet, but your comments made me think about simply using an iframe and show the spreadsheet at the bottom of the admin panel.


Doug Napoleone said...

If you have enough information to do the iframe (which just requires the link with some url arg options), then you also have all the information to do the export/import for the user as part of the admin page.

You can do it in pure javascript which gets the exported csv and pushes it to your server (it's just some extra args on that same URL) or you can make it a 'submit' style where you schedule the changes on the server. Here the user would click a 'submit' button, and the server would be the thing which gets the csv file and does the import. This gives you the ability to have a status (ala youtube upload/processing) where you can show the user that the server is processing the changes and have a history of the submits.