- Saved searches
- Use saved searches to filter your results more quickly
- License
- grayciemoe/phpgooglespreadsheetapi
- Name already in use
- Sign In Required
- Launching GitHub Desktop
- Launching GitHub Desktop
- Launching Xcode
- Launching Visual Studio Code
- Latest commit
- Git stats
- Files
- README.md
- [Примеры] Google Sheets/Таблицы API PHP
- 2. Приступаем к работе с API Google Sheets
Saved searches
Use saved searches to filter your results more quickly
You signed in with another tab or window. Reload to refresh your session. You signed out in another tab or window. Reload to refresh your session. You switched accounts on another tab or window. Reload to refresh your session.
PHP library for read/write access to Google spreadsheets.
License
grayciemoe/phpgooglespreadsheetapi
This commit does not belong to any branch on this repository, and may belong to a fork outside of the repository.
Name already in use
A tag already exists with the provided branch name. Many Git commands accept both tag and branch names, so creating this branch may cause unexpected behavior. Are you sure you want to create this branch?
Sign In Required
Please sign in to use Codespaces.
Launching GitHub Desktop
If nothing happens, download GitHub Desktop and try again.
Launching GitHub Desktop
If nothing happens, download GitHub Desktop and try again.
Launching Xcode
If nothing happens, download Xcode and try again.
Launching Visual Studio Code
Your codespace will open once ready.
There was a problem preparing your codespace, please try again.
Latest commit
Git stats
Files
Failed to load latest commit information.
README.md
Google Spreadsheets PHP API
PHP library allowing read/write access to existing Google Spreadsheets and their data. Uses the version 3 API, which is the latest at time of writing.
Since this API uses OAuth2 for client authentication a very lite (and somewhat incomplete) set of classes for obtaining OAuth2 tokens is included.
Constructor accepts an instance of OAuth2\GoogleAPI() , which handles OAuth2 token fetching/refreshing and generation of HTTP authorization headers used with all Google spreadsheet API calls.
Returns a listing of available spreadsheets for the requesting client.
$OAuth2GoogleAPI = new OAuth2\GoogleAPI(/* URLs and client identifiers */); $OAuth2GoogleAPI->setTokenData(/* Token data */); $OAuth2GoogleAPI->setTokenRefreshHandler(/* Token refresh handler function */); $spreadsheetAPI = new GoogleSpreadsheet\API($OAuth2GoogleAPI); print_r( $spreadsheetAPI->getSpreadsheetList() ); /* [SPREADSHEET_KEY] => Array ( [ID] => 'https://spreadsheets.google.com/feeds/spreadsheets/private/full/. ' [updated] => UNIX_TIMESTAMP [name] => 'Spreadsheet name' ) */
Returns a listing of defined worksheets for a specified spreadsheet key.
$OAuth2GoogleAPI = new OAuth2\GoogleAPI(/* URLs and client identifiers */); $OAuth2GoogleAPI->setTokenData(/* Token data */); $OAuth2GoogleAPI->setTokenRefreshHandler(/* Token refresh handler function */); $spreadsheetAPI = new GoogleSpreadsheet\API($OAuth2GoogleAPI); print_r( $spreadsheetAPI->getWorksheetList('SPREADSHEET_KEY') ); /* [WORKSHEET_ID] => Array ( [ID] => 'https://spreadsheets.google.com/feeds/. ' [updated] => UNIX_TIMESTAMP [name] => 'Worksheet name' [columnCount] => TOTAL_COLUMNS [rowCount] => TOTAL_ROWS ) */
Returns a read only ‘list based feed’ of data for a given spreadsheet key and worksheet ID.
List based feeds have a specific format as defined by Google — see the API reference for details. Data is returned as an array with two keys — defined headers and the data body.
$OAuth2GoogleAPI = new OAuth2\GoogleAPI(/* URLs and client identifiers */); $OAuth2GoogleAPI->setTokenData(/* Token data */); $OAuth2GoogleAPI->setTokenRefreshHandler(/* Token refresh handler function */); $spreadsheetAPI = new GoogleSpreadsheet\API($OAuth2GoogleAPI); print_r( $spreadsheetAPI->getWorksheetDataList('SPREADSHEET_KEY','WORKSHEET_ID') ); /* Array ( [headerList] => Array ( [0] => 'Header name #1' [1] => 'Header name #2' [x] => 'Header name #x' ) [dataList] => Array ( [0] => Array ( ['Header name #1'] => VALUE ['Header name #2'] => VALUE ['Header name #x'] => VALUE ) [1]. ) ) */
Returns a listing of individual worksheet cells, for either the entire sheet or a specific row/column range — see example below for usage of row/column ranges.
Cells are returned as instances of GoogleSpreadsheet\CellItem() within an array list, indexed by their cell reference (e.g. «B1»). Cell instances can be modified and then passed into API()->updateWorksheetCellList() to update the source Google spreadsheet.
$OAuth2GoogleAPI = new OAuth2\GoogleAPI(/* URLs and client identifiers */); $OAuth2GoogleAPI->setTokenData(/* Token data */); $OAuth2GoogleAPI->setTokenRefreshHandler(/* Token refresh handler function */); $spreadsheetAPI = new GoogleSpreadsheet\API($OAuth2GoogleAPI); // fetch first 20 rows from third column (C) to the end of the sheet // if $cellRange is not passed then *all* cells for the spreadsheet will be returned $cellRange = [ 'columnStart' => 3 'rowStart' => 1 'rowEnd' => 20 ]; print_r( $spreadsheetAPI->getWorksheetCellList( 'SPREADSHEET_KEY','WORKSHEET_ID', $cellRange ) ); /* Array ( [CELL_REFERENCE] => GoogleSpreadsheet\CellItem Object ( getRow() getColumn() getReference() getValue() setValue() isDirty() ) [CELL_REFERENCE]. ) */
Accepts and array list of one or more GoogleSpreadsheet\CellItem() instances and updates the target spreadsheet where cell values have been modified from their source value using the GoogleSpreadsheet\CellItem()->setValue() method.
Passed cell instances that have not been modified will be skipped by this method (no work to do).
$OAuth2GoogleAPI = new OAuth2\GoogleAPI(/* URLs and client identifiers */); $OAuth2GoogleAPI->setTokenData(/* Token data */); $OAuth2GoogleAPI->setTokenRefreshHandler(/* Token refresh handler function */); $spreadsheetAPI = new GoogleSpreadsheet\API($OAuth2GoogleAPI); $cellList = $spreadsheetAPI->getWorksheetCellList('SPREADSHEET_KEY','WORKSHEET_ID'); $cellList['CELL_REFERENCE']->setValue('My updated value'); $spreadsheetAPI->updateWorksheetCellList( 'SPREADSHEET_KEY','WORKSHEET_ID', $cellList );
The provided example CLI script will perform the following tasks:
- Fetch all available spreadsheets for the requesting client and display.
- For the first spreadsheet found, fetch all worksheets and display.
- Fetch a data listing of the first worksheet.
- Fetch a range of cells for the first worksheet.
- Finally, modify the content of the first cell fetched (commented out in example).
- Create a new project API at https://console.developers.google.com/.
- Generate a new set of OAuth2 client tokens under the APIs & Auth -> Credentials section:
- Click Create new Client ID.
- Select Web application as the Application type (default).
- Enter an Authorized redirect URI — this does not need to be a real live URI for the example.
- Under the Client ID for web application section, note down generated client ID and client secret values.
- Note: In a production application this sensitive information should be saved in a secure form to datastore/database/etc.
Finally, run example.php to view the result.
Note: If OAuth2 token details stored in ./.tokendata require a refresh (due to expiry), the function handler set by OAuth2\GoogleAPI->setTokenRefreshHandler() will be called to allow the re-save of updated token data back to persistent storage.
The Google spreadsheet API documents suggest requests can specify the API version. Attempts to do this cause the cell based feed response to avoid providing the cell version slug in nodes — making it impossible to issue an update of cell values. So for now, I have left out sending the API version HTTP header.
[Примеры] Google Sheets/Таблицы API PHP
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, …)Документация по API Google Sheets: https://developers.google.com/sheets/api/.
Информация о лимитах: https://developers.google.com/sheets/api/limits.
Google Sheets API имеет ограничения в 100 запросов за 100 секунд, поэтому между запросами рекомендую ставить задержку sleep(1);Таблица, используемая в примерах:
- Generate a new set of OAuth2 client tokens under the APIs & Auth -> Credentials section: