Требования для работы с API Google таблицы на php
К работе с API с помощью PHP предъявляются следующие минимальные требования:
- Google аккаунт
- PHP версии 5.4 и выше
- Расширение php-json
- Инструмент управления зависимостями Composer (необязательно)
Чтобы проверить версию php:
$ php -v
PHP 7.1.30 (cli) (built: Jul 2 2019 17:22:20) ( NTS )
Copyright (c) 1997-2018 The PHP GroupЧтобы установить расширение php-json выполните на CentOS (RedHat подобных):
$ sudo yum install php-jsonНа Ubuntu подобных системах:
$ sudo apt-get install php-jsonЕсли у вас установлен composer, то установите расширение с помощью него:
$ composer require google/apiclient:^2.0Я же просто скачаю его. Для этого создам папку под проект и перейду в нее:
$ mkdir sheets
$ cd sheetsНа этой странице выбираем последнюю версию для версии нашего php и копируем ссылку. В моем случае это версия клиента 2.7.2 для php 7.0. После этого скачиваем архив:
$ wget https://github.com/googleapis/google-api-php-client/releases/download/v2.7.2/google-api-php-client-v2.7.2-PHP7.0.zipИ разархивируем его:
$ unzip google-api-php-client-v2.7.2-PHP7.0.zipНа этом подготовительный этап закончен, переходим к получению доступа к API.
Получение доступа к API Google таблицы
Для начала создадим отдельный проект, в котором в дальнейшем разрешим работу API Google таблиц. Переходим по ссылке и нажимаем CREATE PROJECT (создать проект):
В поле Project name* укажем название название нового проекта, например Sheets и нажимаем CREATE:
На создание нового проекта может потребоваться до нескольких минут. После этого он появится в панели менеджера:
Далее требуется разрешить работу с API Google таблицы для нашего проекта. Переходим через консоль (три черты в верхнем левом углу) в APIs & Services и далее в Dashboards:
Проверяем, что выбран наш проект Sheets и нажимаем ENABLE APIS AND SERVICES:
Откроется страница со всеми API сервисами. Воспользуемся поиском указав Google Sheets API. Кликаем на единственный результат:
И нажимаем кнопку ENABLE:
Далее требуется создать сервисный аккаунт для доступа к API Google страницы. Через консоль выбираем APIs & Services и Credentials:
В появившемся окне выбрать CREATE CREDNTIALS и выбираем Service Account:
На первом шаге указываем имя сервисного аккаунта и нажимаем CREATE, на втором – выбираем роль Project -> Owner и нажимаем CONTINUE. На последнем шаге нажимаем DONE:
После этого открываем только что созданный аккаунт и в раздели ключей создаем новый выбирая Create new key:
В открывшемся окне выбираем JSON и нажимаем CREATE и сохраняем новый сгенерированный ключ. Его необходимо сохранить в нашем рабочем каталоге sheets под именем service_key.json . Также из данных аккаунта копируем электронную почту из поля Email, она нам понадобится в дальнейшем.
Примеры работы с API Google таблицы на php
Создадим таблицу. Для этого перейдем в раздел Google таблицы по ссылке и выберем пустой документ. После того, как таблица была создана необходимо предоставить доступ нашему сервисному аккаунту на её редактирование. Для этого нажимаем кнопку Настройки Доступа в верхнем правом углу и указываем там почту сервисного аккаунта, которую мы скопировали на предыдущем этапе, указав при этом роль Редактор.
Получение идентификатора таблицы и идентификатора листа
Обратите внимание на адресную строку, из которой нам необходимо будет записать два параметра идентификатор таблицы ($spreadsheetId) и идентификатор листа ($sheetId):
Подключение клиента для работы с API Google таблицы
Приступаем к коду на php. Создадим файл sheet.php в нашей рабочей директории. Структура файлов должны иметь следующий вид:
$ ls -l
total 204
-rw-r--r--. 1 user user 1981 Sep 18 23:04 CODE_OF_CONDUCT.md
-rw-r--r--. 1 user user 1315 Sep 18 23:04 composer.json
-rw-r--r--. 1 user user 133719 Sep 18 23:04 composer.lock
-rw-rw-r--. 1 user user 431 Oct 21 10:39 credentials.json
-rw-r--r--. 1 user user 10256 Sep 18 23:04 LICENSE
-rw-rw-r--. 1 user user 2986 Oct 21 11:11 quickstart.php
-rw-r--r--. 1 user user 17309 Sep 18 23:04 README.md
-rw-rw-r--. 1 user user 2292 Oct 22 12:09 service_key.json
-rw-rw-r--. 1 user user 1056 Oct 22 14:32 sheet.php
drwxr-xr-x. 3 user user 20 Sep 18 23:04 src
-rw-rw-r--. 1 user user 442 Oct 21 11:01 token.json
-rw-r--r--. 1 user user 11563 Sep 18 23:04 UPGRADING.md
drwxr-xr-x. 13 user user 193 Sep 18 23:04 vendor
где sheet.php – наш рабочий файл, service_key.json – ключ сервисного аккаунта, а остальные файлы и каталоги – клиент Google таблиц, который мы получили из архива.
Откроем файл sheet.php и запишем следующий код (замените $spreadsheetId на свой):
<?php
// Подключаем клиент Google таблиц
require_once __DIR__ . '/vendor/autoload.php';
// Наш ключ доступа к сервисному аккаунту
$googleAccountKeyFilePath = __DIR__ . '/service_key.json';
putenv('GOOGLE_APPLICATION_CREDENTIALS=' . $googleAccountKeyFilePath);
// Создаем новый клиент
$client = new Google_Client();
// Устанавливаем полномочия
$client->useApplicationDefaultCredentials();
// Добавляем область доступа к чтению, редактированию, созданию и удалению таблиц
$client->addScope('https://www.googleapis.com/auth/spreadsheets');
$service = new Google_Service_Sheets($client);
// ID таблицы
$spreadsheetId = 'ваш идентификатор таблицы';
Данный код по подключению клиента, разрешению доступа и указания идентификатора таблицы будет необходим для запуска всех примеров. Но чтобы его не дублировать, в дальнейшем он будет опускаться.
API Google таблиц имеет ограничение в 500 запросов на проект в течение 100 секунд и 100 запросов за 100 секунд на один сервисный аккаунт! Имейте это в виду при обращении к сервису, при необходимости делайте паузы в коде!
Проверка доступа к API Google таблицы
Добавим к нашему коду следующие строки:
$response = $service->spreadsheets->get($spreadsheetId);
var_dump($response);И выполним его:
$ php sheet.phpЕсли все сделано правильно, то после запуска скрипта на экран будет выведена информация о таблице. Но в первый раз может быть следующая ошибка:
PHP Fatal error: Uncaught DomainException: Could not load the default credentials. Browse to https://developers.google.com/accounts/docs/application-default-credentials for more information in /home/user/sheets/vendor/google/auth/src/ApplicationDefaultCredentials.php:180
Stack trace:
#0 /home/user/sheets/src/Google/Client.php(1197): Google\Auth\ApplicationDefaultCredentials::getCredentials('https://www.goo...', Object(Google\Auth\HttpHandler\Guzzle6HttpHandler), NULL, NULL, NULL)
#1 /home/user/sheets/src/Google/Client.php(402): Google_Client->createApplicationDefaultCredentials()
#2 /home/user/sheets/src/Google/Client.php(872): Google_Client->authorize()
#3 /home/user/sheets/src/Google/Service/Resource.php(232): Google_Client->execute(Object(GuzzleHttp\Psr7\Request), 'Google_Service_...')
#4 /home/user/sheets/vendor/google/apiclient-services/src/Google/Service/Sheets/Resource/Spreadsheets.php(92): Google_Service_Resource->call('get', Array, 'Google_Service_...')
#5 /home/user/sheets/sheet.php(25): Google_Service_Sheets_Resour in /home/user/sheets/vendor/google/auth/src/ApplicationDefaultCredentials.php on line 180
Она связана с некорректной структурой файла service_key.json: проверьте, чтобы в нем было переносов строк, а в параметре private_key в описании начала и конца ключа (BEGIN PRIVATE KEY и END PRIVATE KEY) между словами было строго по одному пробелу.
Получение данных таблицы, листов и их содержимого
Для примера будет использована таблицы с именем My project с одним листом 2020-10 и данными:
Посмотрим эти данные через API:
$response = $service->spreadsheets->get($spreadsheetId);
// Идентификатор таблицы
var_dump($response->spreadsheetId);
// URL страницы
var_dump($response->spreadsheetUrl);
// Получение свойств таблицы
$spreadsheetProperties = $response->getProperties();
// Имя таблицы
var_dump($spreadsheetProperties->title);
// Обход всех листов
foreach($response->getSheets() as $sheet) {
// Получаем свойства листа
$sheetProperties = $sheet->getProperties();
// Идентификатор листа
var_dump($sheetProperties->index);
// Имя листа
var_dump($sheetProperties->title);
}
Результатом работы будут следующие данные:
$ php sheet.php
string(44) "1ftopfUv4ymp78IdNa1268Cc8iC4Kd7LHgCzCNPAOavq"
string(88) "https://docs.google.com/spreadsheets/d/1ftopfUv4ymp78IdNa1268Cc8iC4Kd7LHgCzCNPAOavq/edit"
string(10) "My project"
int(0)
string(7) "2020-10"
Создание новой таблицы
Для примера приведет метод создания новой таблицы:
// Объект - свойства таблицы
$SpreadsheetProperties = new Google_Service_Sheets_SpreadsheetProperties();
// Название таблицы
$SpreadsheetProperties->setTitle('NewSpreadsheet');
// Объект - таблица
$Spreadsheet = new Google_Service_Sheets_Spreadsheet();
$Spreadsheet->setProperties($SpreadsheetProperties);
// Делаем запрос
$response = $service->spreadsheets->create($Spreadsheet);
// Выводим идентификатор и url новой таблицы
var_dump($response->spreadsheetId);
var_dump($response->spreadsheetUrl);
Однако, если вы перейдете url вам будет отказано в доступе. Необходимо разрешить чтение/ редактирование с вашего Google аккаунта.
Разрешения на доступ к таблице
Для этого необходимо разрешить в нашем проекте использование Google Drive API. Делается это аналогично включению Google Sheets API (см. выше): найти его в поиске и нажать ENABLE. А в первоначальном примере изменить область доступа $client->addScope(‘https://www.googleapis.com/auth/spreadsheets’) на:
$client->addScope(['https://www.googleapis.com/auth/drive', 'https://www.googleapis.com/auth/spreadsheets']);Теперь мы можем добавить доступ:
// Объект - диск
$Drive = new Google_Service_Drive($client);
// Объект - разрешения диска
$DrivePermisson = new Google_Service_Drive_Permission();
// Тип разрешения
$DrivePermisson->setType('user');
// Указываем свою почту
$DrivePermisson->setEmailAddress('you_email@gmail.com');
// Права на редактирование
$DrivePermisson->setRole('writer');
// Выполняем запрос с нашим spreadsheetId, полученным в предыдущем примере
$response = $Drive->permissions->create('you_spreadsheetId', $DrivePermisson);
var_dump($response);
Теперь мы можем перейти по url из предыдущего примера и редактировать таблицу.
Проверка прав доступа к таблице/файлу
Для проверки, какие права (роли) у нас есть на таблицу (файл) можно воспользоваться следующим примером:
$Drive = new Google_Service_Drive($client);
$DrivePermissions = $Drive->permissions->listPermissions($spreadsheetId);
foreach ($DrivePermissions as $key => $value) {
$role = $value->role;
var_dump($role);
}
Код вернет роли у таблицы $spreadsheetId для нашего аккаунта:
string(6) "writer"
string(5) "owner"Создание нового листа
Cоздадим в нашей таблице новый лист с именем NEW:
//Создаем новый объект с типом свойство листа
$SheetProperties = new Google_Service_Sheets_SheetProperties();
// Указываем имя листа
$SheetProperties->setTitle('NEW');
// Объект - запрос на добавление листа
$AddSheetRequests = new Google_Service_Sheets_AddSheetRequest();
$AddSheetRequests->setProperties($SheetProperties);
// Объект - запрос
$SheetRequests = new Google_Service_Sheets_Request();
$SheetRequests->setAddSheet($AddSheetRequests);
// Объект - запрос на обновление электронной таблицы
$requests = new Google_Service_Sheets_BatchUpdateSpreadsheetRequest();
$requests->setRequests($SheetRequests);
// Выполняем запрос на обновление таблицы
$response = $service->spreadsheets->BatchUpdate($spreadsheetId, $requests);
// Смотрим результат
var_dump($response);
Изменение свойств листа
Изменим свойства нового листа, например, переименуем его:
// Получаем нашу таблицу
$spreadsheets = $service->spreadsheets->get($spreadsheetId);
// Получаем текущие свойства созданного ранее листа, указывая его идентификатор - 1
$SheetProperties = $spreadsheets->getSheets()[1]->getProperties();
// Устанавливаем новое имя
$SheetProperties->setTitle('RENAME');
// Объект - запрос на обновление свойств листа
$UpdateSheetRequests = new Google_Service_Sheets_UpdateSheetPropertiesRequest();
$UpdateSheetRequests->setProperties($SheetProperties);
// Указываем, какое свойство мы хотим обновить
$UpdateSheetRequests->setFields('title');
// Объект - запрос листа
$SheetRequests = new Google_Service_Sheets_Request();
$SheetRequests->setUpdateSheetProperties($UpdateSheetRequests);
// Объект - запрос на обновление таблицы
$requests = new Google_Service_Sheets_BatchUpdateSpreadsheetRequest();
$requests->setRequests($SheetRequests);
// Выполняем запрос
$response = $service->spreadsheets->BatchUpdate($spreadsheetId, $requests);
// Смотрим результат
var_dump($response);
Копирование листа
Метод позволяет скопировать лист таблицы в эту же электронную таблицу или другую:
// Идентификатор листа, который требуется скопировать
$sheetId = 0;
// Объект - запрос копирование листа
$CopySheetRequest = new Google_Service_Sheets_CopySheetToAnotherSpreadsheetRequest();
$CopySheetRequest->setDestinationSpreadsheetId($spreadsheetId);
// Копирование листа $sheetId таблицы $spreadsheetId
$response = $service->spreadsheets_sheets->copyTo($spreadsheetId, $sheetId, $CopySheetRequest);
// Выводим данные нового листа
var_dump($response->index);
var_dump($response->title);
В нашей таблице появится новый лист с идентификатором 1 и именем 2020-10 (копия).
Получение содержимого листа
Чтобы получить содержимое всего листа необходимо указать его имя:
// Получение содержимого всего листа по его имени
$range = '2020-10';
$response = $service->spreadsheets_values->get($spreadsheetId, $range);
var_dump($response);Мы получим следующий ответ:
object(Google_Service_Sheets_ValueRange)#75 (7) {
["collection_key":protected]=>
string(6) "values"
["majorDimension"]=>
string(4) "ROWS"
["range"]=>
string(18) "'2020-10'!A1:Z1000"
["values"]=>
array(4) {
[0]=>
array(5) {
[0]=>
string(4) "Name"
[1]=>
string(7) "Russian"
[2]=>
string(7) "English"
[3]=>
string(5) "Maths"
[4]=>
string(9) "Chemistry"
}
[1]=>
array(5) {
[0]=>
string(4) "Ivan"
[1]=>
string(1) "4"
[2]=>
string(1) "5"
[3]=>
string(1) "4"
[4]=>
string(1) "3"
}
[2]=>
array(5) {
[0]=>
string(4) "Alex"
[1]=>
string(1) "5"
[2]=>
string(1) "3"
[3]=>
string(1) "5"
[4]=>
string(1) "4"
}
[3]=>
array(5) {
[0]=>
string(5) "Peter"
[1]=>
string(1) "4"
[2]=>
string(1) "4"
[3]=>
string(1) "4"
[4]=>
string(1) "3"
}
}
["internal_gapi_mappings":protected]=>
array(0) {
}
["modelData":protected]=>
array(0) {
}
["processed":protected]=>
array(0) {
}
}
Получение содержимого диапазона ячеек
Чтобы получить содержимое диапазона ячеек необходимо в переменной $range вместе с именем листа указать восклицательный знак и после него диапазон ячеек:
// Получение содержимого диапазона ячеек
$range = '2020-10!A1:B4';
$response = $service->spreadsheets_values->get($spreadsheetId, $range);
var_dump($response);Обновление диапазона ячеек
Допустим, мы хотим добавить ещё одну строку с данными данными в диапазон A5:E5:
// Данные для обновления
$values = [
["Eric", "3", "3", "3", "3"],
];
// Объект - диапазон значений
$ValueRange = new Google_Service_Sheets_ValueRange();
// Устанавливаем наши данные
$ValueRange->setValues($values);
// Указываем в опциях обрабатывать пользовательские данные
$options = ['valueInputOption' => 'USER_ENTERED'];
// Делаем запрос с указанием во втором параметре названия листа и начальную ячейку для заполнения
$service->spreadsheets_values->update($spreadsheetId, '2020-10!A5', $ValueRange, $options);
По умолчанию, при вставке диапазона используется majorDimension = ROWS, т.е. диапазон заполняется построчно. Если требуется вставка по столбцам, необходимо изменить его значение на COLUMNS:
// Объект - диапазон значений
$ValueRange = new Google_Service_Sheets_ValueRange();
// Указываем направление вставки - по столбцам
$ValueRange->setMajorDimension('COLUMNS');
// Устанавливаем наши данные
$ValueRange->setValues($values);
// Указываем в опциях обрабатывать пользовательские данные
$options = ['valueInputOption' => 'USER_ENTERED'];
// Делаем запрос с указанием во втором параметре названия листа и начальную ячейку для заполнения
$service->spreadsheets_values->update($spreadsheetId, '2020-10!A5', $ValueRange, $options);
Если необходимо пропустить (не заполнять) строку / столбец, то в массиве значений указывается пустой массив:
$values = [
["Eric", "3", "3", "3", "3"],
[],
["Stan", "4", "3", "4", "3"]
];В этом случае значения строки / столбца останутся первоначальными.
Добавление значений (строки) в конец листа
Допустим у нас уже есть лист, заполненный значениями и нам требуется добавить новую строку в конец этого листа.
// Диапазон, в котором мы определяем заполненные данные. Например, если указать диапазон A1:A10
// и если ячейка A2 ячейка будет пустая, то новое значение запишется в строку, начиная с A2.
// Поэтому лучше перестраховаться и указать диапазон побольше:
$range = '2020-10!A1:Z';
// Данные для добавления
$values = [
["Eric", "3", "3", "3", "3"],
];
// Объект - диапазон значений
$ValueRange = new Google_Service_Sheets_ValueRange();
// Устанавливаем наши данные
$ValueRange->setValues($values);
// Указываем в опциях обрабатывать пользовательские данные
$options = ['valueInputOption' => 'USER_ENTERED'];
// Добавляем наши значения в последнюю строку (где в диапазоне A1:Z все ячейки пустые)
$service->spreadsheets_values->append($spreadsheetId, $range, $ValueRange, $options);
Очистка диапазона ячеек
Очистим диапазон ячеек, которые мы обновили в предыдущем примере:
// Объект - запрос очистки значений
$clear = new Google_Service_Sheets_ClearValuesRequest();
// Делаем запрос с указанием во втором параметре названия листа и диапазон ячеек для очистки
$response = $service->spreadsheets_values->clear($spreadsheetId, '2020-10!A5:E5', $clear);Заключение
В этой статье рассмотрена лишь малая часть возможностей по работе с API Google страницы на php. При необходимости буду стараться добавлять новые примеры по взаимодействию с сервисом. Если у кого-то возникнет необходимость в конкретных – пишите, буду рад помочь.
Полезные ссылки
- Документация по классам Google Sheets API v4.
- Документация по методам Rest Google Sheets API v4.
Больше полезных материалов в нашем Telegram канале. Вступайте и будем на связи! https://t.me/bf_conversion
