Работа с 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, полученным в предыдущем примере
$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. При необходимости буду стараться добавлять новые примеры по взаимодействию с сервисом. Если у кого-то возникнет необходимость в конкретных – пишите, буду рад помочь.

Полезные ссылки

30 комментариев к “Работа с 4 версией API Google таблицы на php”

  1. Небольшая опечатка – “responsr” вместо “response”, а за материал спасибо, более качественно чем в других источниках – только по этому материалу смог во всём разобраться

    Ответить
  2. Как проверить наличие доступа к конкретной таблице, если заранее это неизвестно?

    Ответить
    • Права проверяются через Google_Service_Drive, т.е. через Google Drive.
      Добавил в статью пример, как посмотреть роли. Но Вы можете вывести все подробности с помощью var_dump($DrivePermissions);

      Ответить
  3. Спасибо, отлично, коннект налажен, подскажите пожалуйста, где посмотреть справочник по функциям

    Ответить
    • Да, что-то я забыл поделиться источниками)
      Добавил раздел с полезными ссылками.

      Ответить
  4. День добрый, а можете подсказать из-за чего может быть такая ошибка?
    “PHP Fatal error: Uncaught Error: Call to undefined method Google\Service\Drive\Permission::getSheets()”?

    Ловится вот на этой строчке кода:
    $SheetProperties = $response->getSheets()[1]->getProperties();

    Как понимаю не распознается метод getSheets()[1]->getProperties();
    Айдишник разумеется подставляю свой.

    Ответить
    • Добрый день.
      Была ошибка в $SheetProperties = $response->getSheets()[1]->getProperties();
      $response – результат работы предыдущего метода, а должна быть переменная $spreadsheets: $SheetProperties = $spreadsheets->getSheets()[1]->getProperties();
      Спасибо за внимательность, исправил.

      Ответить
  5. Добрый день, а не могу понять, возможно ли как то делать вставку по заголовку таблицы?
    Например есть три столбца, название, цена и колличество
    И надо сделать вставку например цены и колличество.
    Следовательно обычным методом получается что цена уйдет в столбец А (наименование), а кооличество в столбец B(цена)
    Или же как по диапозну ячеек образно B($i) C($i)

    Ответить
    • И Вам доброго дня!
      Можно вставлять диапазон не построчно, а по столбцам. Для этого требуется изменить направление вставки: $ValueRange->setMajorDimension('COLUMNS');
      Добавил описание в статье.

      Ответить
      • А если например нужно вставить только строку, но в столбец ФбСбВб минуя столбец B? Или просто в массиве пустоту передавать?

        Ответить
          • Заполнять по столбцам, а на месте столбца B указать пустой массив:
            $values = [
            ["Eric", "3", "3", "3", "3"],
            [],
            ["Stan", "4", "3", "4", "3"]
            ["Ivan", "5", "4", "5", "5"]
            ];

  6. Получение содержимого листа
    var_dump($respomse);
    Опечатка .
    Вместо $respoMse Нужно $respoNse.

    А так за статью огромное спасибо. Касательно прав доступа почерпнул для себя немного.

    Ответить
  7. Отличная статья. Спасибо автору.
    Остался только один вопрос, как сделать так чтобы при каждом срабатывании, значение заносилось в новую строку.
    Например если А1 заполнено загаситься в А2, затем в А3 и так далее

    Ответить
    • 1.Получаете содержимое листа (см соответствующий пункт)
      2.Из массива values получаем последний индекс, прибавляем 1 и получаем номер последней заполненной строки.
      3.Используем построчное заполнение (см пример в статье), указывая в параметрах название листа и номер полученной строки + 1

      Ответить
    • Посмотрите новый раздел Добавление значений (строки) в конец листа, там описан более элегантный способ.

      Ответить
  8. Как сделать так чтобы можно было указывать лист, а строку нет
    Чтобы запись велась в каждой строке поочередно, типо А1,А2,А3….

    Ответить
    • Посмотрите новый раздел Добавление значений (строки) в конец листа, возможно это то, что Вы имели ввиду.

      Ответить

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