Using Google Spreadsheets as a CMS with PHP

As seen here:

Setup a Published Google Spreadsheet

  1. Create a Google spreadsheet, then fill in the data in your spreadsheet with an identifier in one column and the content in the next column.
  2. Next, we need to publish the content. Note: This is more than just “Sharing” the spreadsheet. When you to this, your content will be available to anyone who finds the link to it (which is probably unlikely, but still–don’t use this for anything private or critical).
  3. Once it’s published you need to get the key from it. This can be found in the modal window that pops up when you publish your spreadsheet, it’s one of the query variables in the URL (see image below).

Fetch the Spreadsheet Data

Time to get the data from our spreadsheet using the Google Spreadsheet API. The API is large and complex and you should feel free to explore it and see if it would be useful to you.

We’ll use the PHP cURL functions to fetch the data.

(Be sure to add in your own API Key and the correct Sheet Tab ID for you needs.)

$key = "1KWc96W6LpBIAuNwL0KPUcSs4Xjcg3CvITPwPQ1imyL0";
$sheet_tab = "2";
$url = "$key/$sheet_tab/public/values";

$ch = curl_init();

curl_setopt($ch, CURLOPT_URL, $url);
curl_setopt($ch, CURLOPT_HEADER, 0);

$google_sheet = curl_exec($ch);


$xml = simplexml_load_string($google_sheet);
foreach($xml->entry as $entry) {
	//echo $entry->title.": ".$entry->content;

$title = $text = $content = array();
$count = 0;
foreach($xml->entry as $entry) {
	// Every other entry will be the identifier
	if($count % 2 == 0)
		$title[] = (string)$entry->content;
		$text[] = (string)$entry->content;
for($i = 0; $i < count($title); $i++) {
	$content[$title[$i]] = $text[$i];
	//echo '<p>'.$title[$i].': '.$text[$i].'</p>';
echo '<h1>'.$content['Page Title'].'</h1>';
echo '<p>Most Recent Donation: '.$content['Last Donation'];
echo '<br/>( Thank you '.$content['Donor First'].' '.$content['Donor Last'].'! )</p>';
echo '<p>Total Raised: '.$content['Total'].'</p>';



No comments yet.

Leave a Reply