Работа с 4 версией API Google таблицы на php

В данной статье будет показана работа с API Google таблицы на php. Мы рассмотрим требования необходимые для использования API, расскажем как создать отдельный проект с доступом к API Google таблицы и сервисные учетные данные с ключом доступа. И конечно же приведем примеры работы с API Google таблицы на php.

Требования для работы с 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, полученным в предыдущем примере
$responsr = $Drive->permissions->create('you_spreadsheetId', $DrivePermisson);

var_dump(response);

Теперь мы можем перейти по url из предыдущего примера и редактировать таблицу.

Создание нового листа

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 = $response->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($respomse);

Мы получим следующий ответ:

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);

Очистка диапазона ячеек

Очистим диапазон ячеек, которые мы обновили в предыдущем примере:

// Объект - запрос очистки значений
$clear = new Google_Service_Sheets_ClearValuesRequest();
// Делаем запрос с указанием во втором параметре названия листа и диапазон ячеек для очистки
$response = $service->spreadsheets_values->clear($spreadsheetId, '2020-10!A5:E5', $clear);

Заключение

В этой статье рассмотрена лишь малая часть возможностей по работе с API Google страницы на php. При необходимости буду стараться добавлять новые примеры по взаимодействию с сервисом. Если у кого-то возникнет необходимость в конкретных – пишите, буду рад помочь.

Оставьте комментарий