Setup a Published Google Spreadsheet
- 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.
- 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).
- 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
- June 26, 2015 @ 01:08:45 [Current Revision] by PeterLugg
- June 26, 2015 @ 01:08:45 by PeterLugg
- June 26, 2015 @ 01:07:59 by PeterLugg
- June 26, 2015 @ 01:06:20 by PeterLugg
- June 26, 2015 @ 01:05:20 by PeterLugg
Revision Differences
| June 26, 2015 @ 01:05:20 | Current 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: | Added: $key = "1KWc96W6LpBIAuNwL0KPUcSs4Xjcg3CvITPwPQ1imyL0"; | ||
| Unchanged: $sheet_tab = "2"; | Unchanged: $sheet_tab = "2"; | ||
| Unchanged: $url = "http:// spreadsheets.google.com/feeds/ cells/$key/$sheet_tab/public/ values"; | Unchanged: $url = "http:// spreadsheets.google.com/feeds/ cells/$key/$sheet_tab/public/ values"; | ||
| 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: | Added: $xml = simplexml_load_ string($google_sheet); | ||
| Deleted: | Added: foreach($xml->entry as $entry) { | ||
| Deleted: | Added: //echo $entry->title.": ".$entry- >content; | ||
| Deleted: | Added: } | ||
| Unchanged: $title = $text = $content = array(); | Unchanged: $title = $text = $content = array(); | ||
| Unchanged: $count = 0; | Unchanged: $count = 0; | ||
| Unchanged: foreach($xml->entry as $entry) { | Unchanged: foreach($xml->entry as $entry) { | ||
| Unchanged: // Every other entry will be the identifier | Unchanged: // Every other entry will be the identifier | ||
| Unchanged: if($count % 2 == 0) | Unchanged: if($count % 2 == 0) | ||
| Unchanged: $title[] = (string)$entry- >content; | Unchanged: $title[] = (string)$entry- >content; | ||
| Unchanged: else | Unchanged: else | ||
| Unchanged: $text[] = (string)$entry- >content; | Unchanged: $text[] = (string)$entry- >content; | ||
| Unchanged: $count++; | Unchanged: $count++; | ||
| Unchanged: } | Unchanged: } | ||
| Unchanged: for($i = 0; $i < count($title); $i++) { | Unchanged: for($i = 0; $i < count($title); $i++) { | ||
| Unchanged: $content[$title[$i]] = $text[$i]; | Unchanged: $content[$title[$i]] = $text[$i]; | ||
| Unchanged: //echo '<p>'.$title[$i].': '.$text[$i].'</p>'; | Unchanged: //echo '<p>'.$title[$i].': '.$text[$i].'</p>'; | ||
| Unchanged: } | Unchanged: } | ||
| Unchanged: //var_dump($content); | Unchanged: //var_dump($content); | ||
| Unchanged: echo '<h1>'.$content['Page Title'].'</h1>'; | Unchanged: echo '<h1>'.$content['Page Title'].'</h1>'; | ||
| Unchanged: echo '<p>Most Recent Donation: '.$content['Last Donation']; | Unchanged: echo '<p>Most Recent Donation: '.$content['Last Donation']; | ||
| Unchanged: echo '<br/>( Thank you '.$content['Donor First'].' '.$content['Donor Last'].'! )</p>'; | Unchanged: echo '<br/>( Thank you '.$content['Donor First'].' '.$content['Donor Last'].'! )</p>'; | ||
| Unchanged: echo '<p>Total Raised: '.$content['Total'].'</p>'; | Unchanged: echo '<p>Total Raised: '.$content['Total'].'</p>'; | ||
| Unchanged: [/php] | Unchanged: [/php] | ||
Note: Spaces may be added to comparison text to allow better line wrapping.
No comments yet.