Tag: spreadsheet

Categories
Development

Insert row into Google Spreadsheet using Zend Framework

I got a little stumped when attempting to insert a new row in a Google Docs Spreadsheet using the Zend Framework, so I figured I’d share this in case someone else runs in the same problems. The documentation provided is really great, but I was getting caught in how the row data was supposed to be getting passed. It just needs a basic array with key/value pairs.

First you need to have the Zend Framework installed on your server. Google Code has a great article on getting started. Then you need to have a spreadsheet in Google Docs.

Now we’re going to write a simple PHP script that will input a new row into a basic spreadsheet. First you need to call the following Zend Framework include files for the script to work:

require_once 'Zend/Loader.php';
Zend_Loader::loadClass('Zend_Http_Client');
Zend_Loader::loadClass('Zend_Gdata');
Zend_Loader::loadClass('Zend_Gdata_ClientLogin');
Zend_Loader::loadClass('Zend_Gdata_Spreadsheets');

Next we’re going to set two variables with your Google Docs login info to authenticate our row creation. Enter you Google Account login:

$email = '<your_google_account_username>';
$pass = '<your_google_account_password>';

Now we authenticate ourselves with Google Docs and create a Zend_Gdata_Spreadsheets object.

$authService = Zend_Gdata_Spreadsheets::AUTH_SERVICE_NAME;
$httpClient = Zend_Gdata_ClientLogin::getHttpClient($email, $pass, $authService);
$spreadsheetService = new Zend_Gdata_Spreadsheets($httpClient);

Next we have to let Google Docs know which of our spreadsheets we want to write to. To do this we need the spreadsheet key. Obtaining the key is fairly simple, just grab it from the spreadsheet’s URL.

For instance:

https://spreadsheets.google.com/a/atlantic.edu/ccc?key=pqtY4KMBjwqlinsu9-f1PEg&hl=en

the key is:

pqtY4KMBjwqlinsu9-f1PEg

Not only do we need the spreadsheet key, but you also have to know what the worksheet id is too. Obtaining the worksheet id can be a little tricky, but if you only have one worksheet then it usually always is od6.

$spreadsheetKey = 'pqtY4KMBjwqlinsu9-f1PEg';
$worksheetId = 'od6';

Now we have to create the row data that we want to add to the above mentioned spreadsheet. You have to pass it an array with key/value pairs. The key is the column label in all lower case.

So to add a new row containing the string smurf to a column named stuff you build the following array:

$rowData = array('stuff' => 'smurf');

That’s it. If you were adding to more columns you would just pass more key/value pairs to the variable.

Now you just call the insertRow method and you’re all set.

$insertedListEntry = $spreadsheetService->insertRow($rowData, $spreadsheetKey, $worksheetId);

Pretty easy and very cool stuff.

Categories
Development

Using DataSource URL with Google Visualization API and Spreadsheet

Spent a good chunk of the day figuring out how to work with the Google Visualization API DataSource URL from a Google Docs Spreadsheet.  It was a little tricky at first, but it’s not too bad once you figure it out.

First make a query of the URL using the built in Visualization API methods:

var query = new google.visualization.Query('http://spreadsheets.google.com/tq?range=&lt;your cell ranges go here&gt;&amp;headers=-1&amp;key=&lt;your URL info goes here&gt;&amp;gid=17&amp;pub=1'); 

ok, let’s break down the key variables in the URL above:

  1. tq?range=E2-G2Anything after the tq is a query written in the Google Visualization API Query Language.  It’s very similar to basic SQL.  In the case above it’s saying “Select the cells between the range of E2 to G2”.  A pretty basic selection, but you can make much more elaborate ones using the Query Language.
     
  2. &key<your URL info goes here> can be found in the URL of the spreadsheet that you’re pulling data from.
  3.  &pub=1Appending this variable with the value of 1 states that the data is public.  This is important if you want you data available to any web visitor.
You then make a call to the API using the send() function.
query_day.send(handleQueryResponse);
Then send method requires a callback function.  In this case we name it handleQueryResponse.
Below is a basic function that when called pulls a value from the spreadsheet and sets that value on a web page.
function handleQuery_dayResponse(response) {
  var data = response.getDataTable();
  var sampleText = document.getElementById('sample-text-div');
  sampleText.innerHTML = 'Enrollment  as <strong>Day ' + data.getValue(0,0) + '</strong>.';
}
This assigns the variable data with the value of the response formatted as a DataTable.  It then grabs the specific value from the DataTable, in this case there’s only one value returned so the column and row values are (0,0).  The getValue() function makes this really easy to do.

Check out a basic example.

Categories
Development

Using a Spreadsheet to populate a chart with the Google Visualization API

I’ve been messing around with adding charts/graphs to web pages that can pull their data from a Google Doc Spreadsheet.  That way the chart is updated when the data is updated, instead of placing a static image of a chart on the page.  Conveniently the Google Visualization API makes creating attractive visualization of data fairly easy.  All you have to do is set up a Google Docs Spreadsheet, then do the following steps:

To get the data source url for Google Spreadsheet data sources follow these steps:

  • Create a spreadsheet in the format expected by your gadget.
  • Select the range of cells you want to display in the gadget.
  • From the toolbar, click ‘Insert’ and choose ‘Gadget’.
  • Choose an existing gadget or select ‘Custom’ as the gadget type, enter the URL of your gadget XML specification and click ‘Add’.
  • In the gadget title, click the arrow icon on top right of the title bar.
  • Choose “Get query data source url…” from the popup menu.
Then you can just call that data source url through some javascript on a webpage. Â