Google Spreadsheets as CMS
I had a client who, after a few years, desired to update his website himself. When I originally designed and developed his website, I knew that he would want to alter the information on his website occasionally, but we agreed that I would make those changes for a number of reasons, not least of which was his level of comfort with a computer. Therefore, when I developed the website, I placed all the data in XML, figuring this would be very easy for me to update and would not require actual website editing. I also have an inherent dislike for locking content to its display, i.e. embedding content into the web page itself (I’ve participated in too many website redevelopment projects). Additionally, should he, or someone else, want to learn how to update the website, I hoped that editing a simple XML file would be a smaller learning curve than having to open a PHP page and alter HTML code, and have my client risk damaging the PHP coding in the process.
In time, I relocated, removing any opportunity for a face-to-face tutorial session. Additionally, I realized that my client was less experienced with a computer than I originally thought. Something easier was in order.
I considered building some pages that would permit my client to update the information in a simple web form. Since I had left private web development work behind, I did not want to devote my precious personal time to what could very quickly become a complicated endeavor. After a week of contemplation, I remembered that one can “publish” a Google spreadsheet. Could I use that? After all, nearly everyone, even the most conservative computer user, has used a spreadsheet before.
Google provides a number of publication options. Atom, an XML format, was one of them. After a little work to import the data into a Google spreadsheet and a little work to transform the data in the Atom stream using XSL so that the data displays nicely, the work was done. No need to build a custom mini-CMS solution. Here are the code ingredients.
An example Atom stream from a Google spreadsheet:
Create an XSL file (I named it dinner.xsl):
<xsl:stylesheet
xmlns:xsl=“http://www.w3.org/1999/XSL/Transform”
xmlns:openSearch=‘http://a9.com/-/spec/opensearchrss/1.0/’
xmlns:gsx=‘http://schemas.google.com/spreadsheets/2006/extended’
xmlns:atom=‘http://www.w3.org/2005/Atom’
xmlns:str=“http://exslt.org/strings”
version=“1.0″ >
<xsl:output method=“html” indent=“yes”></xsl:output>
<xsl:strip-space elements=“*”></xsl:strip-space>
<xsl:template match=“/”>
<xsl:call-template name=“getHors” />
<br /><br />
<xsl:call-template name=“getPlats” />
<br /><br />
<xsl:call-template name=“getDesserts” />
</xsl:template>
<xsl:template name=“getHors”>
<p class=‘large’><b>Hors d’oeuvre</b></p>
<xsl:for-each select=“atom:feed/atom:entry”>
<xsl:for-each select=“atom:title”>
<xsl:if test=“normalize-space(.)=’hors’”>
<p>
<xsl:call-template name=“renderItem” />
</p>
</xsl:if>
</xsl:for-each>
</xsl:for-each>
</xsl:template>
<xsl:template name=“getPlats”>
<p class=‘large’><b>Les Plats</b></p>
<xsl:for-each select=“atom:feed/atom:entry”>
<xsl:for-each select=“atom:title”>
<xsl:if test=“normalize-space(.)=’plats’”>
<p>
<xsl:call-template name=“renderItem” />
</p>
</xsl:if>
</xsl:for-each>
</xsl:for-each>
</xsl:template>
<xsl:template name=“getDesserts”>
<p class=‘large’><b>Desserts</b></p>
<xsl:for-each select=“atom:feed/atom:entry”>
<xsl:for-each select=“atom:title”>
<xsl:if test=“normalize-space(.)=’dessert’”>
<p>
<xsl:call-template name=“renderItem” />
</p>
</xsl:if>
</xsl:for-each>
</xsl:for-each>
</xsl:template>
<xsl:template name=“renderItem”>
<xsl:for-each select=“str:tokenize(../atom:content,’:')”>
<xsl:choose>
<xsl:when test=“position()=2″>
<xsl:choose>
<xsl:when test=“contains(. , ‘, prix’)”>
<b><xsl:value-of select=“substring-before( . , ‘, prix’)”/></b><br />
</xsl:when>
<xsl:otherwise>
<b><xsl:value-of select=“substring-before( . , ‘, description’)”/></b><br />
</xsl:otherwise>
</xsl:choose>
</xsl:when>
<xsl:when test=“position()=3″>
<xsl:choose>
<xsl:when test=“contains(. , ‘, description’)”>
<xsl:value-of select=“following-sibling::*[1]“/> <xsl:value-of select=“substring-before( . , ‘, description’)”/>-
</xsl:when>
<xsl:otherwise>
<xsl:value-of select=“.”/>
</xsl:otherwise>
</xsl:choose>
</xsl:when>
</xsl:choose>
</xsl:for-each>
</xsl:template>
</xsl:stylesheet>
And then a little PHP:
$atomfeed = “http://spreadsheets.google.com/feeds/list/o13846618212923825784.2660624158407743517/od6/public/basic”;
$xml_data = file_get_contents($atomfeed);
$xml = new DOMDocument(“1.0″);
$xml->loadXML($xml_data);
$xsl_file = “dinner.xsl”;
$xsl = new DOMDocument(“1.0″);
$xsl->load($xsl_file);
// Configure the transformer
$proc = new XSLTProcessor;
$proc->importStyleSheet($xsl);
echo $proc->transformToXML($xml);
?>
Here are the files altogether:
January 2nd, 2009 at 6:42 pm
Sounds pretty cool. I’ll experiment with it when I get home, but it would be nice to see a sample website.
January 2nd, 2009 at 8:00 pm
Can you show us excel export (xls file, i.e. input spreadsheet file) and html result….?
January 3rd, 2009 at 10:48 am
GIYF:
http://www.ondinerestaurant.com/dinner.php
January 4th, 2009 at 10:07 am
any example sites being managed? demos?