Ramani: Applications

Google Apps Integration Using PHP

Saving Form Data to Google Spreadsheets Using PHP and the Google Docs API

The general idea is to read a Google Spreadsheet through PHP and save user submitted form data via the Google Documents List Data API. By doing this, you can quickly view all the submissions at a glance and you are also able to export CSV files of the data. Using Google Docs gives you and your clients a quick and easy interface to interact with form data.

I've written a small PHP helper class to assist with the whole process (PHP5). You are going to need the following:

A working copy of the Zend GData Client Library (1.0.3+)

A copy of the PHP Google Spreadsheet Helper Class

A Google account, in order to be able to create a spreadsheet document using Google Docs.

First you should login to Google Docs using your existing Google account. Once logged in you will want to create a new spreadsheet document, you will be immediately taken to a spreadsheet interface. Lets start off by creating some column field names on row #1:

name

comments

Make sure to save your spreadsheet and give it a name, you will need to use the spreadsheet name in the code below.

The following is a basic example of using the Google_Spreadsheet PHP helper class. // Zend library include path

set_include_path(get_include_path() . PATH_SEPARATOR . "$_SERVER[DOCUMENT_ROOT]/ZendGdata-1.8.1/library");

include_once("Google_Spreadsheet.php");

$u = "username@gmail.com"; $p = "password";

$ss = new Google_Spreadsheet($u,$p);

$ss->useSpreadsheet("My Spreadsheet");

// if not setting worksheet, "Sheet1" is assumed // $ss->useWorksheet("worksheetName");

$row = array  (  "name" => "John Doe"  , "email" => "john@example.com"  , "comments" => "Hello world" );

if ($ss->addRow($row))

 echo "Form data successfully stored using Google Spreadsheet";

else

 echo "Error, unable to store spreadsheet data";

?>

Download
Google Spreadsheet PHP Helper Class, this project is on github.

Integrate your PHP application with Google Calendar
Understanding the Calendar Data API
Before you dive into the PHP code, a few words about the Google Calendar Data API are in order. As with all REST-based services, the API works by accepting HTTP requests containing one or more XML-encoded input arguments and returning XML-encoded responses that can be parsed in any XML-aware client. With the Google Calendar Data API, the response always consists of an Atom or RSS feed containing the requested information.
A typical Google Calendar feed includes more than enough information to build a useful and relevant application. To see an example, log into your Google Calendar account, navigate to your calendar settings, and find the link for your calendar's private address URL. This URL, which you should hold secret at all times, provides read-only access to your calendar's feed without first requiring authorization, and will look something like http://www.google.com/calendar/feeds/userid/private-magicCookie/basic. Pop this URL into your Web browser (or send a GET request for the feed through an HTTP client), and you should see something like Listing 1:
Listing 1: An example Google Calendar feed
<?xml version='1.0' encoding='UTF-8'?>
<feed xmlns='http://www.w3.org/2005/Atom' 
 xmlns:openSearch='http://a9.com/-/spec/opensearchrss/1.0/' 
 xmlns:batch='http://schemas.google.com/gdata/batch' 
 xmlns:gCal='http://schemas.google.com/gCal/2005' 
 xmlns:gd='http://schemas.google.com/g/2005'>
  <id>http://www.google.com/calendar/feeds/user@gmail.com/
   private-cookie/basic</id>
  <updated>2008-06-13T19:15:18.000Z</updated>
  <category scheme='http://schemas.google.com/g/2005#kind' 
  term='http://schemas.google.com/g/2005#event'/>
  <title type='text'>Joe User</title>
  <subtitle type='text'>Joe User</subtitle>
  <link rel='alternate' type='text/html' 
  href='http://www.google.com/calendar/embed?src=user@gmail.com'/>
  <link rel='http://schemas.google.com/g/2005#feed' type='application/atom+xml' 
   href='http://www.google.com/calendar/feeds/user@gmail.com/private-cookie/basic'/>
  <link rel='http://schemas.google.com/g/2005#batch' type='application/atom+xml' 
   href='http://www.google.com/calendar/feeds/user@gmail.com/private-cookie/basic/batch'
    />
  <link rel='self' type='application/atom+xml' 
  href='http://www.google.com/calendar/feeds/user@gmail.com/private-cookie/basic?
  max-results=25'/>
  <author>
    <name>Joe User</name>
    <email>user@gmail.com</email>
  </author>
  <generator version='1.0' uri='http://www.google.com/calendar'
  >Google Calendar</generator>
  <openSearch:totalResults>4</openSearch:totalResults>
  <openSearch:startIndex>1</openSearch:startIndex>
  <openSearch:itemsPerPage>25</openSearch:itemsPerPage>
  <gCal:timezone value='Asia/Calcutta'/>
  <entry>
    <id>http://www.google.com/calendar/feeds/user@gmail.com/
    private-cookie/basic/xxxxxxxx</id>
    <published>2008-06-12T08:49:38.000Z</published>
    <updated>2008-06-13T19:06:21.000Z</updated>
    <category scheme='http://schemas.google.com/g/2005#kind' 
    term='http://schemas.google.com/g/2005#event'/>
    <title type='html'>Swim party</title>
    <summary type='html'>When: Sat Jun 21, 2008 12pm to 3:30pm&nbsp;
IST<br>
<br>Event Status: confirmed</summary>
    <content type='html'>When: Sat Jun 21, 2008 12pm to 3:30pm 
IST<br />
<br />Event Status: confirmed</content>
    <link rel='alternate' type='text/html' 
    href='http://www.google.com/calendar/event?eid=cGxwbHExOYHHDlOHQ4ZjA
    yMGMgdmlrcmFtLm1lbG9uZmlyZUBnb29nxmNvbQ' title='alternate'/>
    <link rel='self' type='application/atom+xml' 
    href='http://www.google.com/calendar/feeds/user@gmail.com/
    private-cookie/basic/ddddddddddddddd/>
    <author>
      <name>Joe User</name>
      <email>user@gmail.com</email>
    </author>
  </entry>
  <entry>
    <id>http://www.google.com/calendar/feeds/user@gmail.com/
    private-cookie/basic/yyyyyyyyyyyyyyyy</id>
    <published>2008-06-12T08:48:30.000Z</published>
    <updated>2008-06-12T08:48:46.000Z</updated>
    <category scheme='http://schemas.google.com/g/2005#kind' 
    term='http://schemas.google.com/g/2005#event'/>
    <title type='html'>Dinner with the gang</title>
    <summary type='html'>When: Wed Jun 11, 2008 7pm to 9:30pm&nbsp;
IST<br>
<br>Event Status: confirmed</summary>
    <content type='html'>When: Wed Jun 11, 2008 7pm to 9:30pm 
IST<br />
<br />Event Status: confirmed</content>
    <link rel='alternate' type='text/html' 
    href='http://www.google.com/calendar/event?eid=
    MmhpYmV2cmowMM2kam9lZDQgdcmFtLm1lbG9uZmlyZU4858Bnb29nbGVtYWlsLmNvbQ' 
    title='alternate'/>
    <link rel='self' type='application/atom+xml' 
    href='http://www.google.com/calendar/feeds/user@gmail.com/private-cookie/
    basic/hhhhhhhhhhhhhhhhh'/>
    <author>
      <name>Joe User</name>
      <email>user@gmail.com</email>
    </author>
  </entry>
  <entry>
  ...
  </entry>
</feed>