Note: This library has been updated to v3 and it is not backwards compatible with v2 so please test/update your code appropriately before upgrading.
This library provides a simple interface to the Google Spreadsheet API v3.
There are a couple of important things to note.
- 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 generating and refreshing tokens so it would have been a waste of time duplicating the official Google library.
- You can not create spreadsheets using this library, as creating spreadsheets is not part of the Google Spreadsheet API, rather it's part of the Google Drive API. See the official Google APIs Client Library for PHP.
I strongly recommend you read through the official Google Spreadsheet API documentation to get a grasp of the concepts.
Using Composer is the recommended way to install it.
1 - Add "asimlqt/php-google-spreadsheet-client" as a dependency in your project's composer.json file.
{
"require": {
"asimlqt/php-google-spreadsheet-client": "3.0.*"
}
}2 - Download and install Composer.
curl -sS https://getcomposer.org/installer | php
3 - Install your dependencies.
php composer.phar install
4 - Require Composer's autoloader.
require 'vendor/autoload.php';
The first thing you will need to do is initialize the service request factory:
use Google\Spreadsheet\DefaultServiceRequest;
use Google\Spreadsheet\ServiceRequestFactory;
$serviceRequest = new DefaultServiceRequest($accessToken);
ServiceRequestFactory::setInstance($serviceRequest);Note: For Windows users, you can disable the ssl verification by '$serviceRequest->setSslVerifyPeer(false)'
$spreadsheetService = new Google\Spreadsheet\SpreadsheetService();
$spreadsheetFeed = $spreadsheetService->getSpreadsheetFeed();Once you have a SpreadsheetFeed you can iterate over the spreadsheets using a foreach loop by calling the 'getEntries()' method or you can retrieve a single spreadsheet by it's title.
$spreadsheet = $spreadsheetFeed->getByTitle('MySpreadsheet');Note: The 'getByTitle' method will return the first spreadsheet found with that title if you have more than one spreadsheet with the same name.
A public spreadsheet is one that has been "published to the web". This does not require authentication. e.g.
$serviceRequest = new DefaultServiceRequest("");
ServiceRequestFactory::setInstance($serviceRequest);
$spreadsheetService = new Google\Spreadsheet\SpreadsheetService();
$worksheetFeed = $spreadsheetService->getPublicSpreadsheet("spreadsheet-id");The spreadsheet id can be copied from the url of the actual spreadsheet in Google Drive.
You can retrieve a list of worksheets from a spreadsheet by calling the getWorksheets() method.
$spreadsheet = $spreadsheetFeed->getByTitle('MySpreadsheet');
$worksheetFeed = $spreadsheet->getWorksheetFeed();You can loop over each worksheet using 'getEntries()' or retrieve a single worksheet by it's title.
$worksheet = $worksheetFeed->getByTitle('Sheet1');To create a new worksheet simply use the 'addWorksheet()' method. This takes 3 arguments:
- Worksheet name
- Number of rows
- Number of columns
$spreadsheet->addWorksheet('New Worksheet', 50, 20);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.
To add headers to a worksheet, use the following:
$cellFeed = $worksheet->getCellFeed();
$cellFeed->editCell(1,1, "Row1Col1Header");
$cellFeed->editCell(1,2, "Row1Col2Header");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.
It's also possible to delete a worksheet.
$worksheet->delete();List feeds work at the row level. Each entry will contain the data for a specific row.
Note: You can not use formulas with the list feed. If you want to use formulas then you must use the cell feed (described below).
$listFeed = $worksheet->getListFeed();Once you have a list feed you can loop over each entry.
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.
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.
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.
You can also sort and filter the data so you only retrieve what is required, this is expecially useful for large worksheets.
$listFeed = $worksheet->getListFeed(["sq" => "age > 45", "reverse" => "true"]);To find out all the available options visit https://developers.google.com/google-apps/spreadsheets/#sorting_rows.
$listFeed->insert(["name" => "Someone", "age" => 25]);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.
$entries = $listFeed->getEntries();
$listEntry = $entries[0];
$values = $listEntry->getValues();
$values["name"] = "Joe";
$listEntry->update($values);Cell feed deals with individual cells. A cell feed is a collection of cells (of type CellEntry)
$cellFeed = $worksheet->getCellFeed();You can retrieve a single cell from the cell feed if you know the row and column numbers for that specific cell.
$cell = $cellFeed->getCell(10, 2);You can then update the cell value using the 'update' method. The value can be a primitive value or a formula e.g.
$cell->update("=SUM(B2:B9)");When attempting to insert data into multiple cells then consider using the batch request functionality to improve performance.
To use the batch request functionality you need access to a cell feed first. You can not use batch requests with list feeds.
$cellFeed = $worksheet->getCellFeed();
$batchRequest = new Google\Spreadsheet\Batch\BatchRequest();
$batchRequest->addEntry($cellFeed->createCell(2, 1, "111"));
$batchRequest->addEntry($cellFeed->createCell(3, 1, "222"));
$batchRequest->addEntry($cellFeed->createCell(4, 1, "333"));
$batchRequest->addEntry($cellFeed->createCell(5, 1, "=SUM(A2:A4)"));
$batchResponse = $cellFeed->insertBatch($batchRequest);