Требования для работы с 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