sim.plified.com

Chris Pollock

Chris Pollock - web developer & ecommerce entrepreneur
undivided… my thoughts on world, family, church, business, technology and Jesus Christ (all in all)

Accessing Google Spreadsheet with PHP

Google spreadsheet can be used as a pseudo-database when setting up a more formal mySQL table is more than you’re looking for.  I was looking to write a simple app that pulled in email addresses and sent a short email (it will be run on a monthly cron as a reminder).   I wanted to easily be able to manage those email addresses.  Google Spreadsheet was the ticket. 

See my code below where I access the sheet, pull out the second row, and sends an email to any string the qualifies as an email address.

Consult the Google Spreadsheet API for more access methods.  I am using the “cells” feed.

   1: <?php
   2:  
   3:     $key = "THE KEY FOR YOUR SPREADSHEET";
   4:     
   5:     $url = "http://spreadsheets.google.com/feeds/cells/$key/1/public/values";
   6:     
   7:     $ch = curl_init();
   8:  
   9:     // set URL and other appropriate options
  10:     curl_setopt($ch, CURLOPT_URL, $url);
  11:     curl_setopt($ch, CURLOPT_HEADER, 0);
  12:     curl_setopt($ch, CURLOPT_RETURNTRANSFER, TRUE);
  13:     
  14:     // grab URL and pass it to the browser
  15:     $google_sheet = curl_exec($ch);
  16:     
  17:     // close cURL resource, and free up system resources
  18:     curl_close($ch);
  19:  
  20:     $doc = new DOMDocument();
  21:     $doc->loadXML($google_sheet); 
  22:     
  23:     $nodes = $doc->getElementsByTagName("cell");
  24:         
  25:     if($nodes->length > 0)
  26:     {
  27:         foreach($nodes as $node)
  28:         {
  29:             // 2nd row is the email row.
  30:             if ($node->getAttribute("row") == 2)
  31:             {
  32:                 if (eregi("^[\.\+_a-z0-9-]+@[a-z0-9-]+(\.[a-z0-9-]+)*(\.[a-z]{2,6})$", $node->nodeValue) ) 
  33:                 {
  34:                       mail($node->nodeValue, "Mail Subject", "Mail Message.", "From: email@yourdomain.com");
  35:                 }
  36:                     
  37:             }
  38:         }
  39:     }
  40:  
  41: ?>

In order to access the sheet with PHP I had to set the sheet to “public”.  There is probably a way to create a secure connection with an Auth Token, but that was the not the purpose of this exercise.  The key for your spread sheet can be found in the URL of the sheet when you have it open in the browser.

Technorati Tags: ,
  • Rob

    When I try this with my Google Spreadsheet, I get an error:

    "You do not have view access to the spreadsheet. Make sure you are properly authenticated."

    Did you have to do any special authentification before you were able to pull data out?

  • http://web.simplifiedbuilding.com cwpollock

    @Rob,

    Unfortuantely I had to mark my document "public" for it to work. I noted that below the coded section in the blog post. Otherwise I'm guessing you have to implement one of the authentication methods.

  • http://stephenakins.blogspot.com Stephen Akins

    Nice, simple introductory article!

    Readers may be interested in the following article also:

    http://stephenakins.blogspot.com/2009/04/google-docs-server-monitoring_8546.html

    It uses the same method to create a server monitor, using the Google Spreadsheet as it's front end.

    Again, nice work!

  • David Smyth

    Hi,

    I'm just researching how easy it would be to read a google spreadsheet into a mysql database. This looks to be exactly what I was after, except for one issue for me…

    I have recently tried using curl on my webhost but apparently the module is disabled. Is there a way of doing this without curl? If not, do you know how I can enable curl through .htaccess? I've not been able to find this anywhere.

    Kind Regards,

    David.

  • http://itlan.is-a-geek.com/ Itlan

    David Smyth, I have used this website with success:

    http://farinspace.com/2009/05/saving-form-data-to-google-spreadsheets/

    You need both the author's helper class, and a Zend Framework library. See website. Email me if you have questions.

  • http://farinspace.com/ Dimas

    David, if you don't have access to CURL, perhaps see if Snoopy will work for you … http://sourceforge.net/projects/snoopy/

    I think it uses CURL if enabled else it uses plain sockets…

  • http://ifixny.com/ Alex

    Great! This is very useful info. Love Google Docs, especially Spreadsheets. Thank you

  • http://willshouse.com/ Will

    There should be an option to copy the code without line numbers. This is pretty annoying, but otherwise this is helpful.

  • http://www.freebasic.nl Erik

    Nice script! I found out that for simple tables reading the public CSV-output of the spreadsheet with fgetcsv() is an easier option. I use that to display comments but this XML-solution is more solid and probably way better in terms of OO, portability etc.

  • tm

    Thanks a lot !
    I'm not familiar with ZendFramework (gData) or curl, so, again, nice work !

  • Pingback: Magento Import | Kitten Village

  • Pingback: Google Docs: What an easy way to embed a cell from google spreadsheet into value output? - Quora

  • Yvonne

    Worked nicely; one note though – I first set the Google Worksheet to "public" and was getting the same error as "Rob" above.

    Buried in the documentation at Google: find the hidden "publish worksheet to web" menu item and select it. Then suddenly everything worked. The KEY changed with the "publish to web" also…

  • Moon

    Hai,
    I read Excel file from my Google account using Zend. I call Motion chart using Publish gadget script. It automatically assign the default X-axis and Y-axis .I wish to assign the X- axis and Y-axis . I choose Advanced settings option to change the Axis and then call that script string in Php code. But Default axis only displayed ! How can i fix that ?

  • Brian

    I believe this version of the Spreadsheet integration with curl is now deprecated and will no longer work. You'll need to use the gData ZendFramework option to include the validation required to access the spreadsheet data.

    JUST FYI.

  • Pingback: How to use the Google SpreadSheets API to feed data to an application? - PHP Solutions - Developers Q & A

  • Mish

    still works! thanks! I just tried it on the latest wordpress version with a php plugin displaying some text from a google spreedsheet in a text widget based on the week number.

  • Raghavendra sunfra

    what is getelement bytagname(" "), i'm getting length of the node is 0

  • Cesar Morillas Barrio

    I think this article would clarify some concepts: http://edba.xyz/google-sheets-as-database

Things I see

There’s the verse that says “love covers a multitude of sins”, well... so does snow:grimacing::flushed::stuck_out_tongue_closed_eyes:. #mudseason #nograssagain #lifewithboysMicah’s been working on his beard. It’s filling in quite nicely!! #mountainman #lifewithboys #11yearoldswithbeardsSick day for Lucas and therefore the rest of us. It’s nice though to have the windows open!!What a difference a haircut makes! Spock to Simon. #beforeandafter #spockhair #haircut #shearingmysheepCelebrating Chinese New Year with a friend (a little early) with some treats straight from Beijing! Yummy!This greeted me this morning. :heart_eyes: #lifewithboys #ivalentinesdayPhotoApparently my read aloud book was riveting to this guy:yum:. The rest of us enjoyed it though. A historical fiction account of Tyndale’s life by Scott O’Dell. #thehawkthatcouldnothuntbyday #readaloud #historicalfictionPhotoThe current favorite face of the house! It’s a cute one!! #puppylifeYour future pilot :man:‍:airplane:Agamemnon, Menelaus and a reluctant general.History actors take a bowSnow Dog!Camo mom working on her next project.

Chris Pollock

Web Developer - proficient in both PHP and ASP.NET.
Rochester, New York

View my web developement site.

View Chris Pollock's LinkedIn profile

My Pictures

There’s the verse that says “love covers a multitude of sins”, well... so does snow:grimacing::flushed::stuck_out_tongue_closed_eyes:. #mudseason #nograssagain #lifewithboysMicah’s been working on his beard. It’s filling in quite nicely!! #mountainman #lifewithboys #11yearoldswithbeardsSick day for Lucas and therefore the rest of us. It’s nice though to have the windows open!!What a difference a haircut makes! Spock to Simon. #beforeandafter #spockhair #haircut #shearingmysheepCelebrating Chinese New Year with a friend (a little early) with some treats straight from Beijing! Yummy!This greeted me this morning. :heart_eyes: #lifewithboys #ivalentinesdayPhotoApparently my read aloud book was riveting to this guy:yum:. The rest of us enjoyed it though. A historical fiction account of Tyndale’s life by Scott O’Dell. #thehawkthatcouldnothuntbyday #readaloud #historicalfictionPhotoThe current favorite face of the house! It’s a cute one!! #puppylifeYour future pilot :man:‍:airplane:Agamemnon, Menelaus and a reluctant general.History actors take a bowSnow Dog!Camo mom working on her next project.