kinlane
11/12/2013 - 8:16 PM

Pulls a spreadsheet content using PHP and Google API Client.

Pulls a spreadsheet content using PHP and Google API Client.

<?php
$fieldname = '$t';
$ShowWorksheet = 'acronyms';

$clientid = '[your client id]';
$clientsecret = '[your client secret]';
$redirecturl = '[your redirect url]';

require_once 'google-api-php-client/src/Google_Client.php';
require_once 'google-api-php-client/src/contrib/Google_DriveService.php';

$client = new Google_Client();
// Get your credentials from the console
$client->setClientId($clientid);
$client->setClientSecret($clientsecret);
$client->setRedirectUri($redirecturl);
$client->setScopes(array('https://www.googleapis.com/auth/drive','https://spreadsheets.google.com/feeds'));

$service = new Google_DriveService($client);

$authUrl = $client->createAuthUrl();

$STDIN = "";

$authCode = "";

$tokenObject = $client->authenticate($authCode);
$client->setAccessToken($tokenObject);

$tokens = json_decode($tokenObject);
//print_r($tokens);
$refresh_token = $tokens->refresh_token;
$access_token = $tokens->access_token;

//echo "Access Token: " . $access_token . "<br />";
//echo "Refresh Token: " . $refresh_token . "<br />";

$pageToken = NULL;

$parameters = array();
if ($pageToken) {
    $parameters['pageToken'] = $pageToken;
}
$files = $service->files->listFiles($parameters);

//print_r($files);

$NumberOfSheets = 3;
$SheetCount = 1;

foreach($files['items'] as $file)
	{
		
	$mimeType = $file['mimeType'];
	$displayName = $file['title'];
	$fileId = $file['id'];
	//	print_r($file);

	if($mimeType=='application/vnd.google-apps.spreadsheet' && $displayName == $ShowWorksheet)
		{
			
		//echo "(" . $mimeType . ") " . $displayName . " - " . $fileId . "<br />";

		$SpreadsheetURL = 'https://spreadsheets.google.com/feeds/worksheets/' . $fileId . '/private/full/?v=3.0&access_token=' . $access_token . "&alt=json";		
		//echo '<a href="' . $SpreadsheetURL . '">' . $SpreadsheetURL . '</a><br />';		
		
		$SpreadsheetResults = file_get_contents($SpreadsheetURL);			
		$SpreadsheetResults = json_decode($SpreadsheetResults);
		//var_dump($SpreadsheetResults);
		
		$Sheet_Count = 1;
		
		// Each Sheet
		foreach($SpreadsheetResults as $Sheet){
				
			$SpreadsheetContentURL = 'https://spreadsheets.google.com/feeds/list/' . $fileId . '/' . $Sheet_Count . '/private/full?v=3.0&access_token=' . $access_token . "&alt=json";		
			//echo '<a href="' . $SpreadsheetContentURL . '">' . $SpreadsheetContentURL . '</a><br />';		
			
			$SpreadsheetContent = file_get_contents($SpreadsheetContentURL);			
			$SpreadsheetContent = str_replace('gsx$','',$SpreadsheetContent);
			$SpreadsheetContent = json_decode($SpreadsheetContent);
			$SpreadsheetRows = $SpreadsheetContent->{'feed'}->{'entry'};				
					
			foreach($SpreadsheetRows as $SpreadsheetRow) 
				{
							
				//var_dump($SpreadsheetRow);
				
				echo "<hr />";
				
				// For Each Spreadsheet Row in Service Worksheet
				$FieldCount = 1;
				foreach($SpreadsheetRow as $key => $value) 
					{
					// The first eight are default sheet, everything after are columns
					if($FieldCount>8){		
			  			echo $key . " = " . $SpreadsheetRow->$key->$fieldname . "<br />";
						}
					$FieldCount++;
					}								
				}
					
			$Sheet_Count++;
			}
		
		echo "<hr />";
		$SheetCount++;			
		}
	}
?>