If you’re a web designer then you’ve probably at some point had a client who wants to be able to update just a section of their static website, such as their prices page, themselves.
If the client doesn’t have the budget or requirement for a full CMS, but that prices page that will keep changing is a dealbreaker, than using Google Docs is a great way to enable them to update the content.
Google Docs is easy to use, secure, free and updateable from anywhere. They might already have a login for it and be familiar with the interface!
It’s not ideal for updating content which is heavily text based with lots of formatting and images spaced throughout it! It does work very well for multi column lists such as prices or updating individual items.
Here’s a step by step guide on how to use Google Docs as a CMS using a Google Docs Spreadsheet:
What You Need:
For this you will need:
- A Google user account
- A webserver with PHP and cURL enabled
- Understanding of PHP
How Does It Work?
For editing the content, you will set up a series of key -> value columns into the Google Spreadsheet.
The keys are pre-defined and are changed into html tags when they are loaded, wrapping the value in the second column, here is one we set up earlier (click to get a bigger version):
What To Do:
In Google Docs:
In this example we have limited the tag selection to Paragraph, H1-H6, a, ul and li, but you can easily add your own.
Once the content is created you will need to publish the sheet as a webpage. To do this go to File > Publish to the web
Inside the dialogue that appears, ensure “Automatically republish when changes are made” is checked if you wish to have the changes go “live” as soon as they are made.
Then click the “Start Publishing” button to begin publishing of the document.
Next Make a note of your key, this appears in the Get a link to the published data section of the pop-up, Your key is the $_GET query var “key”
Finally, you need to work out your sheet number (if you are using more than one sheet). To do this, count the number of sheets before the current sheet, and that is your sheet number.
That is all for the Google Docs Spreadsheet work so make sure your document is saved!
On your website:
Now create your PHP document. We wont show all the code for this, but we will give you the pseudo-code for you to generate your own where omitted.
Step one, we need to link to Google Docs from the script, we will do this though PHP cURL, so you will need to initialize cURL and send the request as follows:
The url to use for the cURL request is http://spreadsheets.google.com/feeds/cells/<<<YOUR KEY>>>/<<<YOUR SHEET>>>/public/values where <<<YOUR KEY>>> is the key from the spreadsheet, and <<<YOUR SHEET>>> is the sheet number you counted.
On to this url we will add 2 query vars, return-empty=true and min-row=2. The return-empty ensures cells are returned even if they are empty, and the min-row sets the first row number, so we can skip the freeze row titles (omit this if you don’t have column titles)
So your url should like roughly like : http://spreadsheets.google.com/feeds/cells/sample_key/1/public/values?return-empty=true&min-row=2
Next we set 2 cURL options, these are CURLOPT_HEADER and CURLOPT_RETURNTRANSFER. by setting the HEADER option to 0, we skip the header section of the document and by setting RETURNTRANSFER to true, we can store the returned data rather than writing it direct to the document.
In our example, we are fetching the the data, and adding it to a storage variable.
The next step is to process the storage variable. The data returned from the cURL function is in XML formatting with the elements in a “cell” tag.
You will need to create a new DOMDocument and load in the storage variable.
From there, you will need to read in Cells (X) number at a time where X is the number of columns in the Google Sheet.
In our example spreadsheet we have 2 columns, so we will read in the cells 2 at a time,
We will add these 2 cells to an array, this array will effectively be the row, so we will add it to another array of “rows” in a structure similar to this:
The next step is to work though each of the rows using a foreach statement, the variable in the basic foreach statement would be an array, treat the first value as column A, and the second value as Column B as per the image above.
First, apply a case statement to the first entry in the array, the column A, switching for each of the possible values you wish to use, e.g. p, /p, a, a_blank etc, and apply the appropriate actions.
In our example spreadsheet, we have an empty column A, to signify a standard P tag without any additional tags, if you use this, you will need to allow a case for this also.
When the case is found for column A, you will need to apply the appropriate HTML Tag to the content in B.
In our spreadsheet we have a P in column A and no content in column B, We will use this to open a P tag and add other tags inside it, and keep it open until a /p is found in column A, at which point we will close it.
Once you have created your tag for the row, you can either append it to an out tag (though use of an output buffer, or a $output.= method) or echo it straight to your page.
Now that the page content is coming straight from Google Docs, when ever it is updated in google docs, it will be updated directly on your page also.
Ways to extend this:
There are a number of ways to extend this basic feature, including, adding an extra column in the spreadsheet to add classes to your tags or other attributes and allowing other tags such as divs and tables.
Tips:
- If you delete a sheet (tab) from your spreadsheet the remaining sheets which followed after it will renumber themselves automatically so you will need to update any pages which reference them.
Give it a try and let us know what you did and how it went in the comments below!
