You are viewing an old revision of this post, from June 26, 2015 @ 01:06:20. See below for differences between this version and the current revision.

Using Google Spreadsheets as a CMS with PHP

As seen here: http://elikirk.com/using-google-spreadsheets-as-a-cms-with-php/

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.) [php] $key = "1KWc96W6LpBIAuNwL0KPUcSs4Xjcg3CvITPwPQ1imyL0"; $sheet_tab = "2"; $url = "http://spreadsheets.google.com/feeds/cells/$key/$sheet_tab/public/values"; $ch = curl_init(); curl_setopt($ch, CURLOPT_URL, $url); curl_setopt($ch, CURLOPT_HEADER, 0); curl_setopt($ch, CURLOPT_RETURNTRANSFER, TRUE); $google_sheet = curl_exec($ch); curl_close($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; else $text[] = (string)$entry->content; $count++; } for($i = 0; $i < count($title); $i++) { $content[$title[$i]] = $text[$i]; //echo '<p>'.$title[$i].': '.$text[$i].'</p>'; } //var_dump($content); 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>'; [/php]

Revisions

Revision Differences

June 26, 2015 @ 01:06:20Current Revision
Content
Deleted: As seen here:Added: As seen here: <a href="http:// elikirk.com/using-google- spreadsheets- as-a-cms-with- php/">http:// elikirk.com/ using-google- spreadsheets- as-a-cms-with-php/</a>
Unchanged: <h3>Setup a Published Google Spreadsheet</h3>Unchanged: <h3>Setup a Published Google Spreadsheet</h3>
Unchanged: <ol>Unchanged: <ol>
Unchanged: <li>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.</li>Unchanged: <li>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.</li>
Unchanged: <li>Next, we need to <em>publish</em> 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).</li>Unchanged: <li>Next, we need to <em>publish</em> 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).</li>
Unchanged: <li>Once it’s published you need to get the <em>key</em> 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).</li>Unchanged: <li>Once it’s published you need to get the <em>key</em> 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).</li>
Unchanged: </ol>Unchanged: </ol>
Unchanged: <h3>Fetch the Spreadsheet Data</h3>Unchanged: <h3>Fetch the Spreadsheet Data</h3>
Unchanged: 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.Unchanged: 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.
Unchanged: We’ll use the PHP cURL functions to fetch the data.Unchanged: We’ll use the PHP cURL functions to fetch the data.
Unchanged: (Be sure to add in your own API Key and the correct Sheet Tab ID for you needs.)Unchanged: (Be sure to add in your own API Key and the correct Sheet Tab ID for you needs.)
 Added: [php]
Deleted: [php]$key = &quot;1KWc96W6LpBIAuNwL0KPUcSs4Xjcg3CvITPwPQ1imyL0&quot;; Added: $key = &quot;1KWc96W6LpBIAuNwL0KPUcSs4Xjcg3CvITPwPQ1imyL0&quot;;
Unchanged: $sheet_tab = &quot;2&quot;;Unchanged: $sheet_tab = &quot;2&quot;;
Unchanged: $url = &quot;http:// spreadsheets.google.com/feeds/ cells/$key/$sheet_tab/public/ values&quot;;Unchanged: $url = &quot;http:// spreadsheets.google.com/feeds/ cells/$key/$sheet_tab/public/ values&quot;;
Unchanged: $ch = curl_init();Unchanged: $ch = curl_init();
Unchanged: curl_setopt($ch, CURLOPT_URL, $url);Unchanged: curl_setopt($ch, CURLOPT_URL, $url);
Unchanged: curl_setopt($ch, CURLOPT_HEADER, 0);Unchanged: curl_setopt($ch, CURLOPT_HEADER, 0);
Unchanged: curl_setopt($ch, CURLOPT_RETURNTRANSFER, TRUE);Unchanged: curl_setopt($ch, CURLOPT_RETURNTRANSFER, TRUE);
Unchanged: $google_sheet = curl_exec($ch);Unchanged: $google_sheet = curl_exec($ch);
Unchanged: curl_close($ch);Unchanged: curl_close($ch);
Deleted: //$xml = simplexml_load_ string($google_sheet); Added: $xml = simplexml_load_ string($google_sheet);
Deleted: //foreach($xml-&gt;entry as $entry) { Added: foreach($xml-&gt;entry as $entry) {
Deleted: // echo $entry-&gt;title.&quot;: &quot;.$entry- &gt;content; Added: //echo $entry-&gt;title.&quot;: &quot;.$entry- &gt;content;
Deleted: //} Added: }
Unchanged: $title = $text = $content = array();Unchanged: $title = $text = $content = array();
Unchanged: $count = 0;Unchanged: $count = 0;
Unchanged: foreach($xml-&gt;entry as $entry) {Unchanged: foreach($xml-&gt;entry as $entry) {
Unchanged: // Every other entry will be the identifierUnchanged: // Every other entry will be the identifier
Unchanged: if($count % 2 == 0)Unchanged: if($count % 2 == 0)
Unchanged: $title[] = (string)$entry- &gt;content;Unchanged: $title[] = (string)$entry- &gt;content;
Unchanged: else Unchanged: else
Unchanged: $text[] = (string)$entry- &gt;content;Unchanged: $text[] = (string)$entry- &gt;content;
Unchanged: $count++;Unchanged: $count++;
Unchanged: }Unchanged: }
Unchanged: for($i = 0; $i &lt; count($title); $i++) {Unchanged: for($i = 0; $i &lt; count($title); $i++) {
Unchanged: $content[$title[$i]] = $text[$i];Unchanged: $content[$title[$i]] = $text[$i];
Deleted: //echo ''.$title[$i].': '.$text[$i].' Added: //echo '&lt;p&gt;'.$title[$i].': '.$text[$i].'&lt;/p&gt;';
Deleted: '; 
Unchanged: }Unchanged: }
Unchanged: //var_dump($content);Unchanged: //var_dump($content);
Deleted: echo ' 
Deleted: &lt;h1&gt;'.$content['Page Title'].'&lt;/h1&gt; Added: echo '&lt;h1&gt;'.$content['Page Title'].'&lt;/h1&gt;';
Deleted: '; 
Deleted: echo 'Most Recent Donation: '.$content['Last Donation']; Added: echo '&lt;p&gt;Most Recent Donation: '.$content['Last Donation'];
Deleted: echo ' 
Deleted: ( Thank you '.$content['Donor First'].' '.$content['Donor Last'].'! ) Added: echo '&lt;br/&gt;( Thank you '.$content['Donor First'].' '.$content['Donor Last'].'! )&lt;/p&gt;';
Deleted: '; 
Deleted: echo 'Total Raised: '.$content['Total'].' Added: echo '&lt;p&gt;Total Raised: '.$content['Total'].'&lt;/p&gt;';
Deleted: '; 
Unchanged: [/php]Unchanged: [/php]

Note: Spaces may be added to comparison text to allow better line wrapping.

Tags:

No comments yet.

Leave a Reply