March 18th, 2009 by Rich Armstrong

An API Tutorial Plus Excel 2007 Export… Plus Zombies!

NOTE: FogBugz 7 has been released, and it includes a CSV export plug-in. This article is still a good starting place for people interested in exploring the API, or in extracting other types of data, like hours worked, into a spreadsheet. 

View a quick screencast (4:47) to see how to use the FogBugz XML API with Excel:

Sometimes, you just need an Excel spreadsheet.  Sometimes, you just need to be able to slice and dice your cases in the way that’s right for you.  Sometimes, you need to drop the case report into a Powerpoint slide to show to the higher-ups.

Sometimes, you need to go out hunting radioactive zombies and you need a hard copy… because neither WiFi nor 3G will penetrate the lead lining of your specially adapted Semi-Autonomous Radioactive Zombie Eradication Vehicle (SARZEV).

Well, if this sounds like you, then I have good news.  I’m happy to report that because Excel 2007 supports XML, it works well with the FogBugz API.  There’s a few steps here, but in the process you’ll learn a little about Excel and a lot about our super-easy API.

The first step is to come up with a view into the data that suits your needs.  If you’re going out to hunt the undead, information is your most powerful weapon. Well, maybe a rail gun is your most powerful weapon… but information is a close second.

First, get your data. Here’s an example of the data you might want:

AssignedCases1

To get this from FogBugz to Excel, you just need to get the same data from the API, which sounds scary, but come on! Aren’t you going off to destroy the undead?  Why are you scared of a little API action? Seriously, it’s easy.

First, you’ll need an API token.  This is a 30-character code that will allow you to act via the API as if you were signed in to FogBugz. I use Chrome almost exclusively, but when I am working with the API, I like to use FireFox. Its XML rendering is a bit friendlier.  You want to go to:

https://[your fogbugz]/api.asp?cmd=logon&email=[your email]&password=[your password]

GetToken2

This token is valid until you hit Log Off in the FogBugz interface or log off in the API. Now, you’re ready to build your query.

Remember that page with the original search on it?  If you go and look at the URL string, you’ll see a searchFor parameter.  This is the encoded search string that you will use to replicate the exact same search in the API.  Here’s what mine looked like:

SearchParam3

Build that into the URL below and you’ll start getting your first data back from the API

https://[your fogbugz]/api.asp?token=[your token]&cmd=search&q=[your search parameter]

So, mine is (broken out for clarity):

https://zombiewatch.fogbugz.com/api.asp?

token=k5ea5jecjsv1qjo7gg54mcngebb852
&cmd=search
&q=assignedto:%22lead-lined%22+status:active

This should give you the same list of cases, but with only the case number.  For our example, we need to add a cols parameter to tell FogBugz what case information to return.

&cols=ixBug,sTitle,sVersion,sComputer

Here, we are using the FogBugz built-in custom fields to denote the type of zombie and its decay level.  We call them “Type” and “Decay” in the interface, but the API needs to be consistent, so we use the original names.  The data is unaffected.

So here’s what the whole URL looks like.

Now, open Excel 2007 and open a new spreadsheet, then choose the Data tab and From Other Sources, then From XML Data Import.

Excel4

Paste your URL into the Open dialog that comes up, then hit Open.

Excel5

You might get one or more messages that look like this.  Just hit OK on each of them.

Excel36

Then you’ll get a message like this:

Excel7

Again, it’s fine to just click OK here. And here’s you’re reward.

Excel8

Okay, so it’s not the prettiest thing in the world, but click that “Summarize with Pivot Table” button and you can get something pretty good:

Pivot9

From here, you can get charts and graphs and all sorts of other neat reports that will help you make your case that the undead will soon rule the earth.

Be careful out there.

ps.

I’ve created an Auditor read-only user on zombiewatch.fogbugz.com, with the password of “password”.

If you want, you can get a new token for this user by going here. With that token, you should be able to play around with the API to your heart’s desire.  If you want to mess around with your own site, though, you can do so by going to try.fogbugz.com.

OH, and, duh, here’s the full documentation on the API.