2017 © Pedro Peláez
 

library php-google-spreadsheet-client

Google Spreadsheet PHP Client

image

donhq/php-google-spreadsheet-client

Google Spreadsheet PHP Client

  • Wednesday, March 16, 2016
  • by donhq
  • Repository
  • 1 Watchers
  • 0 Stars
  • 95 Installations
  • PHP
  • 0 Dependents
  • 0 Suggesters
  • 151 Forks
  • 0 Open issues
  • 29 Versions
  • 2 % Grown

The README.md

Build Status, (*1)

Introduction

This library provides a simple interface to the Google Spreadsheet API., (*2)

There are a couple of important things to note., (*3)

  • This library requires a valid OAuth access token to work but does not provide any means of generating one. The Google APIs Client Library for PHP has all the functionality required for for generating and refreshing tokens so it would have been a waste of time duplicating the official google library. I have created a demo application which shows how to generate an OAuth token here.
  • You can not create spreadsheets using this (PHP Google Spreadsheet Client) library, as creating spreadsheets is not part of the Spreadsheet API and the functionality already exists in the official Google Client Library.

I strongly recommend you read through the official Google Spreadsheet API documentation to get a grasp of the concepts., (*4)

Usage

Installation

Using composer is the recommended way to install it., (*5)

1 - Add "asimlqt/php-google-spreadsheet-client" as a dependency in your project's composer.json file., (*6)

{
    "require": {
        "asimlqt/php-google-spreadsheet-client": "2.3.*"
    }
}

2 - Download and install Composer., (*7)

curl -sS https://getcomposer.org/installer | php

3 - Install your dependencies., (*8)

php composer.phar install

4 - Require Composer's autoloader., (*9)

require 'vendor/autoload.php';

Bootstrapping

The first thing you will need to do is initialize the service request factory:, (*10)

use Google\Spreadsheet\DefaultServiceRequest;
use Google\Spreadsheet\ServiceRequestFactory;

$serviceRequest = new DefaultServiceRequest($accessToken);
ServiceRequestFactory::setInstance($serviceRequest);

Retrieving a list of spreadsheets

$spreadsheetService = new Google\Spreadsheet\SpreadsheetService();
$spreadsheetFeed = $spreadsheetService->getSpreadsheets();

SpreadsheetFeed implements ArrayIterator so you can iterate over it using a foreach loop or you can retrieve a single spreadsheet by name., (*11)

$spreadsheet = $spreadsheetFeed->getByTitle('MySpreadsheet');

Retrieving a list of worksheets

You can retrieve a list of worksheets from a spreadsheet by calling the getWorksheets() method., (*12)

$spreadsheetService = new Google\Spreadsheet\SpreadsheetService();
$spreadsheetFeed = $spreadsheetService->getSpreadsheets();
$spreadsheet = $spreadsheetFeed->getByTitle('MySpreadsheet');
$worksheetFeed = $spreadsheet->getWorksheets();

You can loop over each worksheet or get a single worksheet by title., (*13)

$worksheet = $worksheetFeed->getByTitle('Sheet 1');

Adding a worksheet

$spreadsheetService = new Google\Spreadsheet\SpreadsheetService();
$spreadsheetFeed = $spreadsheetService->getSpreadsheets();
$spreadsheet = $spreadsheetFeed->getByTitle('MySpreadsheet');
$spreadsheet->addWorksheet('New Worksheet', 50, 20);

The only required parameter is the worksheet name, The row and column count are optional. The default value for rows is 100 and columns is 10., (*14)

Deleting a worksheet

$spreadsheetService = new Google\Spreadsheet\SpreadsheetService();
$spreadsheetFeed = $spreadsheetService->getSpreadsheets();
$spreadsheet = $spreadsheetFeed->getByTitle('MySpreadsheet');
$worksheetFeed = $spreadsheet->getWorksheets();
$worksheet = $worksheetFeed->getByTitle('New Worksheet');
$worksheet->delete();

Working with list-based feeds

Retrieving a list-based feed

$spreadsheetService = new Google\Spreadsheet\SpreadsheetService();
$spreadsheetFeed = $spreadsheetService->getSpreadsheets();
$spreadsheet = $spreadsheetFeed->getByTitle('MySpreadsheet');
$worksheetFeed = $spreadsheet->getWorksheets();
$worksheet = $worksheetFeed->getByTitle('Sheet 1');
$listFeed = $worksheet->getListFeed();

Once you have a list feed you can loop over each entry., (*15)

foreach ($listFeed->getEntries() as $entry) {
    $values = $entry->getValues();
}

The getValues() method returns an associative array where the keys are the column names and the values are the cell content., (*16)

Note: The Google api converts the column headers to lower case so the column headings might not appear to be the same as what you see in Google Drive using your browser., (*17)

Note: If there is data for a particular row which does not have a column header then Google randomly generates a header and as far as i know it always begins with an underscore. Bear in mind that this is not generated by this library., (*18)

You can also sort and filter the data so you only retrieve what is required, this is expecially useful for large worksheets., (*19)

$listFeed = $worksheet->getListFeed(array("sq" => "age > 45", "reverse" => "true"));

To find out all the available options visit https://developers.google.com/google-apps/spreadsheets/#sorting_rows., (*20)

Adding a list row

$spreadsheetService = new Google\Spreadsheet\SpreadsheetService();
$spreadsheetFeed = $spreadsheetService->getSpreadsheets();
$spreadsheet = $spreadsheetFeed->getByTitle('MySpreadsheet');
$worksheetFeed = $spreadsheet->getWorksheets();
$worksheet = $worksheetFeed->getByTitle('Sheet 1');
$listFeed = $worksheet->getListFeed();

$row = array('name'=>'John', 'age'=>25);
$listFeed->insert($row);

When adding or updating a row the column headers need to match exactly what was returned by the Google API, not what you see in Google Drive., (*21)

Updating a list row

$spreadsheetService = new Google\Spreadsheet\SpreadsheetService();
$spreadsheetFeed = $spreadsheetService->getSpreadsheets();
$spreadsheet = $spreadsheetFeed->getByTitle('MySpreadsheet');
$worksheetFeed = $spreadsheet->getWorksheets();
$worksheet = $worksheetFeed->getByTitle('Sheet 1');
$listFeed = $worksheet->getListFeed();
$entries = $listFeed->getEntries();
$listEntry = $entries[0];

$values = $listEntry->getValues();
$values['name'] = 'Joe';
$listEntry->update($values);

Adding headers to a new worksheet

The Google Spreadsheet API does not allow you to update a list row if headers are not already assigned. So, when you create a new worksheet, before you can add data to a worksheet using the 'Adding/Updating a list row' methods above, you need to add headers., (*22)

To add headers to a worksheet, use the following:, (*23)


$spreadsheetService = new Google\Spreadsheet\SpreadsheetService(); $spreadsheetFeed = $spreadsheetService->getSpreadsheets(); $spreadsheet = $spreadsheetFeed->getByTitle('MySpreadsheet'); $worksheetFeed = $spreadsheet->getWorksheets(); $worksheet = $worksheetFeed->getByTitle('Sheet 1'); $cellFeed = $worksheet->getCellFeed(); $cellFeed->editCell(1,1, "Row1Col1Header"); $cellFeed->editCell(1,2, "Row1Col2Header"); $cellFeed->editCell(1,3, "Row1Col3Header"); $cellFeed->editCell(1,4, "Row1Col4Header");

Updating multiple cells with a batch request

When attempting to insert data into multiple cells then consider using the batch request functionality to improve performance., (*24)

$spreadsheetService = new Google\Spreadsheet\SpreadsheetService();

$spreadsheetFeed = $spreadsheetService->getSpreadsheets();
$spreadsheet = $spreadsheetFeed->getByTitle('MySpreadsheet');

$worksheet = $spreadsheet->getWorksheets()->getByTitle('Sheet1');
$cellFeed = $worksheet->getCellFeed();

$batchRequest = new Google\Spreadsheet\Batch\BatchRequest();
$batchRequest->addEntry($cellFeed->createInsertionCell(2, 1, "111"));
$batchRequest->addEntry($cellFeed->createInsertionCell(3, 1, "222"));
$batchRequest->addEntry($cellFeed->createInsertionCell(4, 1, "333"));

$batchResponse = $cellFeed->insertBatch($batchRequest);

The Versions

16/03 2016

dev-master

9999999-dev https://github.com/donhq/php-google-spreadsheet-client

Google Spreadsheet PHP Client

  Sources   Download

Apache-2.0

The Requires

  • php >=5.3.0

 

The Development Requires

by Asim Liaquat

google spreadsheet

16/03 2016

2.3.7.1

2.3.7.1 https://github.com/donhq/php-google-spreadsheet-client

Google Spreadsheet PHP Client

  Sources   Download

Apache-2.0

The Requires

  • php >=5.3.0

 

The Development Requires

by Asim Liaquat

google spreadsheet

13/12 2015

2.3.7

2.3.7.0 https://github.com/asimlqt/php-google-spreadsheet-client

Google Spreadsheet PHP Client

  Sources   Download

Apache-2.0

The Requires

  • php >=5.3.0

 

The Development Requires

by Asim Liaquat

google spreadsheet

24/11 2015

2.3.6

2.3.6.0 https://github.com/asimlqt/php-google-spreadsheet-client

Google Spreadsheet PHP Client

  Sources   Download

Apache-2.0

The Requires

  • php >=5.3.0

 

The Development Requires

by Asim Liaquat

google spreadsheet

21/06 2015

2.3.5

2.3.5.0 https://github.com/asimlqt/php-google-spreadsheet-client

Google Spreadsheet PHP Client

  Sources   Download

Apache-2.0

The Requires

  • php >=5.3.0

 

The Development Requires

by Asim Liaquat

google spreadsheet

12/04 2015

2.3.4

2.3.4.0 https://github.com/asimlqt/php-google-spreadsheet-client

Google Spreadsheet PHP Client

  Sources   Download

Apache-2.0

The Requires

  • php >=5.3.0

 

The Development Requires

by Asim Liaquat

google spreadsheet

12/04 2015

dev-development

dev-development https://github.com/asimlqt/php-google-spreadsheet-client

Google Spreadsheet PHP Client

  Sources   Download

Apache-2.0

The Requires

  • php >=5.3.0

 

The Development Requires

by Asim Liaquat

google spreadsheet

22/02 2015

2.3.3

2.3.3.0 https://github.com/asimlqt/php-google-spreadsheet-client

Google Spreadsheet PHP Client

  Sources   Download

Apache-2.0

The Requires

  • php >=5.3.0

 

The Development Requires

by Asim Liaquat

google spreadsheet

22/02 2015

2.3.2

2.3.2.0 https://github.com/asimlqt/php-google-spreadsheet-client

Google Spreadsheet PHP Client

  Sources   Download

Apache-2.0

The Requires

  • php >=5.3.0

 

The Development Requires

by Asim Liaquat

google spreadsheet

22/02 2015

2.3.1

2.3.1.0 https://github.com/asimlqt/php-google-spreadsheet-client

Google Spreadsheet PHP Client

  Sources   Download

Apache-2.0

The Requires

  • php >=5.3.0

 

The Development Requires

by Asim Liaquat

google spreadsheet

02/12 2014

2.3.0

2.3.0.0 https://github.com/asimlqt/php-google-spreadsheet-client

Google Spreadsheet PHP Client

  Sources   Download

Apache-2.0

The Requires

  • php >=5.3.0

 

by Asim Liaquat

google spreadsheet

02/12 2014

2.2.4

2.2.4.0 https://github.com/asimlqt/php-google-spreadsheet-client

Google Spreadsheet PHP Client

  Sources   Download

Apache-2.0

The Requires

  • php >=5.3.0

 

by Asim Liaquat

google spreadsheet

18/11 2014

2.2.3

2.2.3.0 https://github.com/asimlqt/php-google-spreadsheet-client

Google Spreadsheet PHP Client

  Sources   Download

Apache-2.0

The Requires

  • php >=5.3.0

 

by Asim Liaquat

google spreadsheet

18/11 2014

2.2.2

2.2.2.0 https://github.com/asimlqt/php-google-spreadsheet-client

Google Spreadsheet PHP Client

  Sources   Download

Apache-2.0

The Requires

  • php >=5.3.0

 

by Asim Liaquat

google spreadsheet

17/11 2014

2.2.1

2.2.1.0 https://github.com/asimlqt/php-google-spreadsheet-client

Google Spreadsheet PHP Client

  Sources   Download

Apache-2.0

The Requires

  • php >=5.3.0

 

by Asim Liaquat

google spreadsheet

17/08 2014

2.2.0

2.2.0.0 https://github.com/asimlqt/php-google-spreadsheet-client

Google Spreadsheet PHP Client

  Sources   Download

Apache-2.0

The Requires

  • php >=5.3.0

 

by Asim Liaquat

google spreadsheet

10/08 2014

2.1.8

2.1.8.0 https://github.com/asimlqt/php-google-spreadsheet-client

Google Spreadsheet PHP Client

  Sources   Download

Apache-2.0

The Requires

  • php >=5.3.0

 

by Asim Liaquat

google spreadsheet

09/08 2014

2.1.7

2.1.7.0 https://github.com/asimlqt/php-google-spreadsheet-client

Google Spreadsheet PHP Client

  Sources   Download

Apache-2.0

The Requires

  • php >=5.3.0

 

by Asim Liaquat

google spreadsheet

05/08 2014

dev-dev-stream-request

dev-dev-stream-request https://github.com/asimlqt/php-google-spreadsheet-client

Google Spreadsheet PHP Client

  Sources   Download

Apache-2.0

The Requires

  • php >=5.3.0

 

by Asim Liaquat

google spreadsheet

05/08 2014

2.1.6

2.1.6.0 https://github.com/asimlqt/php-google-spreadsheet-client

Google Spreadsheet PHP Client

  Sources   Download

Apache-2.0

The Requires

  • php >=5.3.0

 

by Asim Liaquat

google spreadsheet

18/06 2014

2.1.5

2.1.5.0 https://github.com/asimlqt/php-google-spreadsheet-client

Google Spreadsheet PHP Client

  Sources   Download

Apache-2.0

The Requires

  • php >=5.3.0

 

by Asim Liaquat

google spreadsheet

07/06 2014

2.1.4

2.1.4.0 https://github.com/asimlqt/php-google-spreadsheet-client

Google Spreadsheet PHP Client

  Sources   Download

Apache-2.0

The Requires

  • php >=5.3.0

 

by Asim Liaquat

google spreadsheet

18/05 2014

2.1.3

2.1.3.0 https://github.com/asimlqt/php-google-spreadsheet-client

Google Spreadsheet PHP Client

  Sources   Download

Apache-2.0

The Requires

  • php >=5.3.0

 

by Asim Liaquat

google spreadsheet

18/05 2014

2.1.2

2.1.2.0 https://github.com/asimlqt/php-google-spreadsheet-client

Google Spreadsheet PHP Client

  Sources   Download

Apache-2.0

The Requires

  • php >=5.3.0

 

by Asim Liaquat

google spreadsheet

18/05 2014

2.1.1

2.1.1.0 https://github.com/asimlqt/php-google-spreadsheet-client

Google Spreadsheet PHP Client

  Sources   Download

Apache-2.0

The Requires

  • php >=5.3.0

 

by Asim Liaquat

google spreadsheet

18/05 2014

2.1.0

2.1.0.0 https://github.com/asimlqt/php-google-spreadsheet-client

Google Spreadsheet PHP Client

  Sources   Download

Apache-2.0

The Requires

  • php >=5.3.0

 

by Asim Liaquat

google spreadsheet

06/04 2014

dev-rework

dev-rework https://github.com/asimlqt/php-google-spreadsheet-client

Google Spreadsheet PHP Client

  Sources   Download

Apache-2.0

The Requires

  • php >=5.3.0

 

by Asim Liaquat

google spreadsheet

22/02 2014

2.0.0

2.0.0.0 https://github.com/asimlqt/php-google-spreadsheet-client

Google Spreadsheet PHP Client

  Sources   Download

Apache-2.0

The Requires

  • php >=5.3.0

 

by Asim Liaquat

google spreadsheet

28/07 2013

1.0

1.0.0.0 https://github.com/asimlqt/php-google-spreadsheet-client

Google Spreadsheet PHP Client

  Sources   Download

by Asim Liaquat

google spreadsheet