Mike Industries

I’ve used EditGrid for several projects now, including expense tracking for my new blog A House By The Park. In addition to expense tracking, EditGrid is useful for:

• Setting up company golf tournaments. Simply create a blank spreadsheet with your columns labeled, email everyone in your company the URL, and let people fill their teams themselves. EditGrid turns hours of coordination and emails in this case into about a minute of work.
• Figuring out when everyone is available for your live fantasy football draft. Again, simply label your rows, send out the URL and you’re done. No more emails flying back and forth and people forgetting what they committed to.
• Any other application where you need multiple people to enter data and there is no concern about access privileges or people screwing with the spreadsheet.

So, all of that stuff is really easy. You don’t need a tutorial to figure it out. What’s extra cool as well is that if you keep the spreadsheet open on your screen, you can see people editing it in real-time. There’s also automatic revision history in case someone messes up.

For A House By The Park though, I wanted to input my expense data into EditGrid, save it as a public read-only spreadsheet, and then suck each expense line item into the corresponding entry on my blog. In other words, if I’m writing about how a structural and sewer inspection cost $425 and $185 respectively, I want to pull that information straight from my expense spreadsheet and display it right within the blog entry.

Here’s how it’s done:

1. Create your spreadsheet in EditGrid.
2. In EditGrid, choose “Data > My Data Format” and then click the “Create New Data Format” button. You’re essentially going to create a new XSL template which will transform your generic spreadsheet output into semantically sound XML.
3. Give your new file format an extension. It doesn’t matter what you choose. I chose “.api”. Leave every other field the way it is, but in the “XML Stylesheet” field, paste in the following:

   <?xml version="1.0" encoding="UTF-8"?> <xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform" > <xsl:template match="sheet"> <sheet> <xsl:apply-templates/> </sheet> </xsl:template> <xsl:template match="row"> <entry> <item><xsl:value-of select="cell[@col=0]"/></item> <cost><xsl:value-of select="cell[@col=1]"/></cost> <totaloutflow><xsl:value-of select="cell[@col=2]"/></totaloutflow> <blog_post_id><xsl:value-of select="cell[@col=3]"/></blog_post_id> <date><xsl:value-of select="cell[@col=4]"/></date> </entry> </xsl:template> </xsl:stylesheet>

4. Your new XML file will be available at the same URL that your browser-readable spreadsheet is available at, plus the ".api" extension. In order to save having to hit editgrid.com whenever your pages get hit, set up a cronjob to pull this file every 5 minutes and save a copy to your server: <?php $FILEPATH = "http://www.editgrid.com/user/mikeindustries/House_Expenses.api"; $curl_handle = curl_init(); curl_setopt ($curl_handle, CURLOPT_URL, $FILEPATH); curl_setopt ($curl_handle, CURLOPT_RETURNTRANSFER, 1); curl_setopt ($curl_handle, CURLOPT_CONNECTTIMEOUT, 4); $CONTENTS = curl_execREMOVEME($curl_handle); curl_close($curl_handle); if (strpos($CONTENTS,"blog_post_id")) { $SLUG = "/home/yourlocalfilepath/ahousebythepark.com/House_Expenses.api"; $handle = fopenREMOVEME($SLUG, 'w'); fwriteREMOVEME($handle, $CONTENTS); fcloseREMOVEME($handle); print $CONTENTS; } ?>
   Note: Remove all instances of "REMOVEME". I had to put those in there to keep WordPress from trying to parse those functions. Weird.
5. Finally, grab your now local XML and parse it into an object in PHP: <?php if (file_exists('/home/yourlocalfilepath/ahousebythepark.com/House_Expenses.api')) { global $spreadsheetxml; $spreadsheetxml = simplexml_load_file('/home/yourlocalfilepath/ahousebythepark.com/House_Expenses.api'); } ?>

That’s it! Now you have a nice PHP XML object that you can do whatever you want with, and you have an editable spreadsheet with data that can automatically make its way on your blog. Gotta love EditGrid.

Side note: I’m sure people will inevitably ask why I don’t use Google Spreadsheets instead. The reason is simple, but probably not applicable for everyone — I’m not an engineer and Google’s API did not seem nearly as simple as EditGrid’s to me. I don’t want to worry about authentication methods, privileges, and all that stuff. I just want a spreadsheet that provides easy access to its data. For that, EditGrid is king.