google spreadsheet api php
PHP Quickstart
Complete the steps described in the rest of this page to create a simple PHP command-line application that makes requests to the Google Sheets API.
Prerequisites
To run this quickstart, you need the following prerequisites:
Step 1: Install the Google Client Library
See the library’s installation page for the alternative installation options.
Step 2: Set up the sample
Create a file named quickstart.php in your working directory and copy in the following code:
Step 3: Run the sample
Run the sample using the following command:
The first time you run the sample, it prompts you to authorize access:
Browse to the provided URL in your web browser.
If you are not already signed in to your Google account, you are be prompted to sign in. If you are signed in to multiple Google accounts, you are asked to select one account to use for the authorization.
Notes
Troubleshooting
This section describes some common issues that you may encounter while attempting to run this quickstart and suggests possible solutions.
SSL certificate problem: unable to get local issuer certificate
This error indicates that the underlying HTTP libraries can’t find a certificate store, and are therefore unable to setup the SSL connection to Google’s servers. See the Guzzle library’s documentation for information on how to setup a certificate store on your machine.
Uncaught InvalidArgumentException: missing the required redirect URI
This error occurs when the credentials.json file used contains a client ID of the wrong type. This code requires an OAuth client ID of type Other, which will be created for you when using the button in Step 1. If creating your own client ID please ensure you select the correct type.
This app isn’t verified
If the OAuth consent screen displays the warning «This app isn’t verified,» your app is requesting scopes that provide access to sensitive user data. If your application uses sensitive scopes, your your app must go through the verification process to remove that warning and other limitations. During the development phase you can continue past this warning by clicking Advanced > Go to
Further reading
Except as otherwise noted, the content of this page is licensed under the Creative Commons Attribution 4.0 License, and code samples are licensed under the Apache 2.0 License. For details, see the Google Developers Site Policies. Java is a registered trademark of Oracle and/or its affiliates.
[Примеры] Google Sheets/Таблицы API PHP
Содержание:
codd.mail@gmail.com или в telegram alex_codd
1. Подготовительный этап
1. Создаём сервисный аккаунт и получаем ключ доступа к API.
2. Открываем доступ к таблице для email адреса сервисного аккаунта:
Email адрес сервисного аккаунта можно узнать на странице вашего проекта в Google Developers Console:
3. Устанавливаем библиотеку для работы с Google APIs: Google APIs Client Library for PHP.
Для установки с помощью Composer выполните в консоли следующие команды:
После установки подключаем файл AutoLoader:
4. Ключ сервисного аккаунта, созданный на первом шаге, кладём в папку с нашим проектом.
2. Приступаем к работе с API Google Sheets
spreadsheetId — ID таблицы (example blog)
sheetId — ID листа (Лист 1, Лист 2, …)
Google spreadsheet api php
This library is no longer maintained. Please use the official Google PHP Client
This library provides a simple interface to the Google Spreadsheet API v3.
There are a couple of important things to note.
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.
The first thing you will need to do is initialize the service request factory:
Note: For Windows users, you can disable the ssl verification by ‘$serviceRequest->setSslVerifyPeer(false)’
Retrieving a list of spreadsheets
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.
Note: The ‘getByTitle’ method will return the first spreadsheet found with that title if you have more than one spreadsheet with the same name.
Retrieving a public spreadsheet
A public spreadsheet is one that has been «published to the web». This does not require authentication. e.g.
The spreadsheet id can be copied from the url of the actual spreadsheet in Google Drive.
Retrieving a list of worksheets
You can retrieve a list of worksheets from a spreadsheet by calling the getWorksheets() method.
You can loop over each worksheet using ‘getEntries()’ or retrieve a single worksheet by it’s title.
Adding a worksheet
To create a new worksheet simply use the ‘addWorksheet()’ method. This takes 3 arguments:
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.
To add headers to a worksheet, use the following:
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.
Deleting a worksheet
It’s also possible to delete a worksheet.
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).
Retrieving a list feed
Once you have a list feed you can loop over each entry.
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.
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.
Updating a list row
Cell feed deals with individual cells. A cell feed is a collection of cells (of type CellEntry)
Retrieving a cell feed
You can retrieve a single cell from the cell feed if you know the row and column numbers for that specific cell.
You can then update the cell value using the ‘update’ method. The value can be a primitive value or a formula e.g.
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.
To use the batch request functionality you need access to a cell feed first. You can not use batch requests with list feeds.
About
A PHP library for accessing and manipulating Google Spreadsheets
How do I access the Google Spreadsheets API in PHP?
So, it seems like everyone’s passing the buck. I just need to write some PHP code that requests and processes data from a Google Spreadsheet. How do I do that? Please don’t link me to similar posts or pages without checking to be sure that they are 100% up to date, I’ve been sifting through a huge mess of posts for hours that are all full of outdated and deprecated dependencies.
3 Answers 3
You’ll need to use the Google APIs Client Library for PHP as well to authenticate via OAuth2. Developer documentation is linked on the github page.
Thanks for the hints and links on this page, I wanted to share what I ended up doing to read a google spreadsheet in php. You can access the spreadsheet in json format, and don’t need to use api or zend framework or gdata library. PhP can handle json very easily and it was the most neat solution I could reach to because it has no dependency to any third party libraries.
Here is a sample link for getting a spreadsheet in json format.
Please note if spreadsheet is private you still need to follow the authentication steps to get token to access to the page. In my case the spreadsheet was public.
Also you can access the spreadsheet via feed list or cell based (replace the list in the url with cell)
You can try the link in a browser and see the json result.
(Aug 2016)
As of this past May, you now have a better solution.
No more passing the buck:
Note the Sheets API allows you to create spreadsheets & sheets, upload & download data, as well as, in the general sense, programmatically access a Sheet as if you were using the user interface (create frozen rows, perform cell formatting, resizing rows/columns, adding pivot tables, creating charts, etc.), but to perform file-level access such as uploads & downloads, imports & exports (same as uploads & downloads but conversion to/from Google Apps formats), you would use the Google Drive API instead.
REST Resource: spreadsheets
Resource: Spreadsheet
Resource that represents a spreadsheet.
| JSON representation |
|---|