sim.plified.com

Chris Pollock

Chris Pollock - web developer (PHP/mySQL & ASP.NET)
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: ,

2 Comments, Comment or Ping

  1. 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?

  2. @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.

Reply to “Accessing Google Spreadsheet with PHP”

Things I see

Amy and baby after storm cloud bike rideRochester storm cloudHuge storm cloud Rochester NYSad life preserver SimonHappy life preserver SimonFuture employee?Simon rides jasmineMicah with ear protectionMicah with ear protectionCIMG7660100_3672100_3666100_3661100_3650100_3643

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

Amy and baby after storm cloud bike rideRochester storm cloudHuge storm cloud Rochester NYSad life preserver SimonHappy life preserver SimonFuture employee?Simon rides jasmineMicah with ear protectionMicah with ear protectionCIMG7660100_3672100_3666100_3661100_3650100_3643