[Примеры] Google Sheets/Таблицы API PHP

/ author

Содержание:

1. Подготовительный этап

1. Создаём сервисный аккаунт и получаем ключ доступа к API.
2. Открываем доступ к таблице для email адреса сервисного аккаунта:

Email адрес сервисного аккаунта можно узнать на странице вашего проекта в Google Developers Console:

3. Устанавливаем библиотеку для работы с Google APIs: Google APIs Client Library for PHP.
Для установки с помощью Composer выполните в консоли следующие команды:

cd /path/my/project/
composer require google/apiclient:^2.0

1 2	cd /path/my/project/ composer require google/apiclient:^2.0

После установки подключаем файл AutoLoader:

require_once '/path/to/your-project/vendor/autoload.php';

1	require_once '/path/to/your-project/vendor/autoload.php';

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

Таблица, используемая в примерах:

Получение содержимого таблиц и листов.

/**
 * Подключаемся к API
 *******************************************************/
require_once __DIR__ . '/vendor/autoload.php';

// Путь к файлу ключа сервисного аккаунта
$googleAccountKeyFilePath = __DIR__ . '/assets/my-project-test1-fdd689d70f55.json';
putenv( 'GOOGLE_APPLICATION_CREDENTIALS=' . $googleAccountKeyFilePath );

// Документация https://developers.google.com/sheets/api/
$client = new Google_Client();
$client->useApplicationDefaultCredentials();

// Области, к которым будет доступ
// https://developers.google.com/identity/protocols/googlescopes
$client->addScope( 'https://www.googleapis.com/auth/spreadsheets' );

$service = new Google_Service_Sheets( $client );

// ID таблицы
$spreadsheetId = '1KdTaK81c3BURTW4D7QMGgkEexd_ATYx3pUiUNbsmNlY';

/**

* Подключаемся к API

*******************************************************/

require_once __DIR__ . '/vendor/autoload.php';

// Путь к файлу ключа сервисного аккаунта

$googleAccountKeyFilePath = __DIR__ . '/assets/my-project-test1-fdd689d70f55.json';

putenv( 'GOOGLE_APPLICATION_CREDENTIALS=' . $googleAccountKeyFilePath );

// Документация https://developers.google.com/sheets/api/

$client = new Google_Client();

$client->useApplicationDefaultCredentials();

// Области, к которым будет доступ

// https://developers.google.com/identity/protocols/googlescopes

$client->addScope( 'https://www.googleapis.com/auth/spreadsheets' );

$service = new Google_Service_Sheets( $client );

// ID таблицы

$spreadsheetId = '1KdTaK81c3BURTW4D7QMGgkEexd_ATYx3pUiUNbsmNlY';

/**
 * Получение информации о таблице и листах
 *******************************************************/
// https://developers.google.com/sheets/api/reference/rest/v4/spreadsheets/get
$response = $service->spreadsheets->get($spreadsheetId);

/* Содержимое $response

Google_Service_Sheets_Spreadsheet::__set_state(array(
   'collection_key' => 'sheets',
   'developerMetadataType' => 'Google_Service_Sheets_DeveloperMetadata',
   'developerMetadataDataType' => 'array',
   'namedRangesType' => 'Google_Service_Sheets_NamedRange',
   'namedRangesDataType' => 'array',
   'propertiesType' => 'Google_Service_Sheets_SpreadsheetProperties',
   'propertiesDataType' => '',
   'sheetsType' => 'Google_Service_Sheets_Sheet',
   'sheetsDataType' => 'array',
   'spreadsheetId' => '1KdTaK81c3BURTW4D7QMGgkEexd_ATYx3pUiUNbsmNlY',
   'spreadsheetUrl' => 'https://docs.google.com/spreadsheets/d/1KdTaK81c3BURTW4D7QMGgkEexd_ATYx3pUiUNbsmNlY/edit',
   'internal_gapi_mappings' =>
  array (
  ),
   'modelData' =>
  array (
    'properties' =>
    array (
      'title' => 'example blog ',
      'locale' => 'ru_RU',
      'autoRecalc' => 'ON_CHANGE',
      'timeZone' => 'Asia/Baghdad',
      'defaultFormat' =>
      array (
        'backgroundColor' =>
        array (
          'red' => 1,
          'green' => 1,
          'blue' => 1,
        ),
        'padding' =>
        array (
          'top' => 2,
          'right' => 3,
          'bottom' => 2,
          'left' => 3,
        ),
        'verticalAlignment' => 'BOTTOM',
        'wrapStrategy' => 'OVERFLOW_CELL',
        'textFormat' =>
        array (
          'foregroundColor' =>
          array (
          ),
          'fontFamily' => 'arial,sans,sans-serif',
          'fontSize' => 10,
          'bold' => false,
          'italic' => false,
          'strikethrough' => false,
          'underline' => false,
        ),
      ),
    ),
    'sheets' =>
    array (
      0 =>
      array (
        'properties' =>
        array (
          'sheetId' => 0,
          'title' => 'Лист1',
          'index' => 0,
          'sheetType' => 'GRID',
          'gridProperties' =>
          array (
            'rowCount' => 1000,
            'columnCount' => 26,
          ),
        ),
      ),
      1 =>
      array (
        'properties' =>
        array (
          'sheetId' => 1871531335,
          'title' => 'Аналитика',
          'index' => 1,
          'sheetType' => 'GRID',
          'gridProperties' =>
          array (
            'rowCount' => 999,
            'columnCount' => 26,
          ),
        ),
      ),
      2 =>
      array (
        'properties' =>
        array (
          'sheetId' => 1789805341,
          'title' => 'Лист3',
          'index' => 2,
          'sheetType' => 'GRID',
          'gridProperties' =>
          array (
            'rowCount' => 1000,
            'columnCount' => 26,
          ),
        ),
      ),
    ),
  ),
   'processed' =>
  array (
  ),
))
 */

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

/**

* Получение информации о таблице и листах

*******************************************************/

// https://developers.google.com/sheets/api/reference/rest/v4/spreadsheets/get

$response = $service->spreadsheets->get($spreadsheetId);

/* Содержимое $response

Google_Service_Sheets_Spreadsheet::__set_state(array(

'collection_key' => 'sheets',

'developerMetadataType' => 'Google_Service_Sheets_DeveloperMetadata',

'developerMetadataDataType' => 'array',

'namedRangesType' => 'Google_Service_Sheets_NamedRange',

'namedRangesDataType' => 'array',

'propertiesType' => 'Google_Service_Sheets_SpreadsheetProperties',

'propertiesDataType' => '',

'sheetsType' => 'Google_Service_Sheets_Sheet',

'sheetsDataType' => 'array',

'spreadsheetId' => '1KdTaK81c3BURTW4D7QMGgkEexd_ATYx3pUiUNbsmNlY',

'spreadsheetUrl' => 'https://docs.google.com/spreadsheets/d/1KdTaK81c3BURTW4D7QMGgkEexd_ATYx3pUiUNbsmNlY/edit',

'internal_gapi_mappings' =>

array (

'modelData' =>

array (

'properties' =>

array (

'title' => 'example blog ',

'locale' => 'ru_RU',

'autoRecalc' => 'ON_CHANGE',

'timeZone' => 'Asia/Baghdad',

'defaultFormat' =>

array (

'backgroundColor' =>

array (

'red' => 1,

'green' => 1,

'blue' => 1,

'padding' =>

array (

'top' => 2,

'right' => 3,

'bottom' => 2,

'left' => 3,

'verticalAlignment' => 'BOTTOM',

'wrapStrategy' => 'OVERFLOW_CELL',

'textFormat' =>

array (

'foregroundColor' =>

array (

'fontFamily' => 'arial,sans,sans-serif',

'fontSize' => 10,

'bold' => false,

'italic' => false,

'strikethrough' => false,

'underline' => false,

'sheets' =>

array (

0 =>

array (

'properties' =>

array (

'sheetId' => 0,

'title' => 'Лист1',

'index' => 0,

'sheetType' => 'GRID',

'gridProperties' =>

array (

'rowCount' => 1000,

'columnCount' => 26,

1 =>

array (

'properties' =>

array (

'sheetId' => 1871531335,

'title' => 'Аналитика',

'index' => 1,

'sheetType' => 'GRID',

'gridProperties' =>

array (

'rowCount' => 999,

'columnCount' => 26,

2 =>

array (

'properties' =>

array (

'sheetId' => 1789805341,

'title' => 'Лист3',

'index' => 2,

'sheetType' => 'GRID',

'gridProperties' =>

array (

'rowCount' => 1000,

'columnCount' => 26,

'processed' =>

array (

))

$response = $service->spreadsheets->get($spreadsheetId);

// Свойства таблицы
$spreadsheetProperties = $response->getProperties();
$spreadsheetProperties->title; // Название таблицы

foreach ($response->getSheets() as $sheet) {

	// Свойства листа
	$sheetProperties = $sheet->getProperties();
	$sheetProperties->title; // Название листа

	$gridProperties = $sheetProperties->getGridProperties();
	$gridProperties->columnCount; // Количество колонок
	$gridProperties->rowCount; // Количество строк

}

$response = $service->spreadsheets->get($spreadsheetId);

// Свойства таблицы

$spreadsheetProperties = $response->getProperties();

$spreadsheetProperties->title; // Название таблицы

foreach ($response->getSheets() as $sheet) {

// Свойства листа

$sheetProperties = $sheet->getProperties();

$sheetProperties->title; // Название листа

$gridProperties = $sheetProperties->getGridProperties();

$gridProperties->columnCount; // Количество колонок

$gridProperties->rowCount; // Количество строк

}

/**
 * Получение содержимого указанного листа
 *******************************************************/
// Диапазон данных, которые необходимо получить
// Примеры:
// "Лист 1" - вернёт всё содержимое листа с указанным названием
// "Лист 1!B2:D4" - вернёт данные, находящиеся в диапазоне B2:D4 на листе с названием "Лист 1"
$range = 'Аналитика!B1:D3';
$response = $service->spreadsheets_values->get($spreadsheetId, $range);

/* Содержимое $response
Google_Service_Sheets_ValueRange::__set_state(array(
   'collection_key' => 'values',
   'majorDimension' => 'ROWS',
   'range' => '\'Аналитика\'!B1:D3',
   'values' =>
  array (
    0 =>
    array (
      0 => 'Конец периода',
      1 => 'Трафик Яндекс',
      2 => 'Трафик Google',
    ),
    1 =>
    array (
      0 => '08.05.2016',
      1 => '61 319 ',
      2 => '318 725 ',
    ),
    2 =>
    array (
      0 => '15.05.2016',
      1 => '63 521 ',
      2 => '329 762 ',
    ),
  ),
   'internal_gapi_mappings' =>
  array (
  ),
   'modelData' =>
  array (
  ),
   'processed' =>
  array (
  ),
))
 */

/**

* Получение содержимого указанного листа

*******************************************************/

// Диапазон данных, которые необходимо получить

// Примеры:

// "Лист 1" - вернёт всё содержимое листа с указанным названием

// "Лист 1!B2:D4" - вернёт данные, находящиеся в диапазоне B2:D4 на листе с названием "Лист 1"

$range = 'Аналитика!B1:D3';

$response = $service->spreadsheets_values->get($spreadsheetId, $range);

/* Содержимое $response

Google_Service_Sheets_ValueRange::__set_state(array(

'collection_key' => 'values',

'majorDimension' => 'ROWS',

'range' => '\'Аналитика\'!B1:D3',

'values' =>

array (

0 =>

array (

0 => 'Конец периода',

1 => 'Трафик Яндекс',

2 => 'Трафик Google',

1 =>

array (

0 => '08.05.2016',

1 => '61 319 ',

2 => '318 725 ',

2 =>

array (

0 => '15.05.2016',

1 => '63 521 ',

2 => '329 762 ',

'internal_gapi_mappings' =>

array (

'modelData' =>

array (

'processed' =>

array (

))

// Варианты отображения возвращаемых данных ValueRenderOption
// https://developers.google.com/sheets/api/reference/rest/v4/ValueRenderOption
// FORMATTED_VALUE | UNFORMATTED_VALUE | FORMULA

$response = $service->spreadsheets_values->get($spreadsheetId, $range, ['valueRenderOption' => 'FORMATTED_VALUE']);
/*
...
  array (
      0 => '15.05.2016',
      1 => '63 521 ',
      2 => '602 541 ',
    ),
...
 */

$response = $service->spreadsheets_values->get($spreadsheetId, $range, ['valueRenderOption' => 'UNFORMATTED_VALUE']);
/*
...
  array (
      0 => 42505,
      1 => 63521,
      2 => 602541,
    ),
...
 */

$response = $service->spreadsheets_values->get($spreadsheetId, $range, ['valueRenderOption' => 'FORMULA']);
/*
...
   array (
      0 => 42505,
      1 => 63521,
      2 => '=C2+E4',
    ),
...
 */

// Варианты отображения возвращаемых данных ValueRenderOption

// https://developers.google.com/sheets/api/reference/rest/v4/ValueRenderOption

// FORMATTED_VALUE | UNFORMATTED_VALUE | FORMULA

$response = $service->spreadsheets_values->get($spreadsheetId, $range, ['valueRenderOption' => 'FORMATTED_VALUE']);

...

array (

0 => '15.05.2016',

1 => '63 521 ',

2 => '602 541 ',

...

$response = $service->spreadsheets_values->get($spreadsheetId, $range, ['valueRenderOption' => 'UNFORMATTED_VALUE']);

...

array (

0 => 42505,

1 => 63521,

2 => 602541,

...

$response = $service->spreadsheets_values->get($spreadsheetId, $range, ['valueRenderOption' => 'FORMULA']);

...

array (

0 => 42505,

1 => 63521,

2 => '=C2+E4',

...

Удаление содержимого ячеек и строк.

// Удаление содержимого ячеек
// https://developers.google.com/sheets/api/reference/rest/v4/spreadsheets.values/clear
$response = $service->spreadsheets_values->clear($spreadsheetId, 'Аналитика!A2:F2', new Google_Service_Sheets_ClearValuesRequest([]));

// Удаление содержимого ячеек

// https://developers.google.com/sheets/api/reference/rest/v4/spreadsheets.values/clear

$response = $service->spreadsheets_values->clear($spreadsheetId, 'Аналитика!A2:F2', new Google_Service_Sheets_ClearValuesRequest([]));

Результат:

// Полное удаление ячеек
// https://developers.google.com/sheets/api/reference/rest/v4/spreadsheets/batchUpdate
$requests = [ // Формируем запрос
	new Google_Service_Sheets_Request( [
		'deleteRange' => [
			'range'          => [
				'sheetId' => '1871531335', // ID листа
				'startRowIndex' => 1,
				'endRowIndex' => 3,
				'startColumnIndex' => 2,
				'endColumnIndex' => 5
			],

			// тип сдвига ячеек после удаления
			// https://developers.google.com/sheets/api/reference/rest/v4/spreadsheets/request#DeleteRangeRequest
			'shiftDimension' => 'ROWS'
		]
	] )
];

$batchUpdateRequest = new Google_Service_Sheets_BatchUpdateSpreadsheetRequest( [
	'requests' => $requests
] );

$service->spreadsheets->batchUpdate( $spreadsheetId, $batchUpdateRequest );

// Полное удаление ячеек

// https://developers.google.com/sheets/api/reference/rest/v4/spreadsheets/batchUpdate

$requests = [ // Формируем запрос

new Google_Service_Sheets_Request( [

'deleteRange' => [

'range' => [

'sheetId' => '1871531335', // ID листа

'startRowIndex' => 1,

'endRowIndex' => 3,

'startColumnIndex' => 2,

'endColumnIndex' => 5

// тип сдвига ячеек после удаления

// https://developers.google.com/sheets/api/reference/rest/v4/spreadsheets/request#DeleteRangeRequest

'shiftDimension' => 'ROWS'

]

] )

];

$batchUpdateRequest = new Google_Service_Sheets_BatchUpdateSpreadsheetRequest( [

'requests' => $requests

] );

$service->spreadsheets->batchUpdate( $spreadsheetId, $batchUpdateRequest );

Результат:

// Как удалить строки
$requests = [
	new Google_Service_Sheets_Request( [
		'deleteRange' => [
			'range'          => [
				'sheetId' => '1871531335',
				'startRowIndex' => 1,
				'endRowIndex' => 3,
			],
			'shiftDimension' => 'ROWS'
		]
	] )
];

// Как удалить колонки
$requests = [
	new Google_Service_Sheets_Request( [
		'deleteRange' => [
			'range'          => [
				'sheetId' => '1871531335',
				'startColumnIndex' => 2,
				'endColumnIndex' => 5
			],
			'shiftDimension' => 'ROWS'
		]
	] )
];

// Как удалить всё содержимое листа
$requests = [
	new Google_Service_Sheets_Request( [
		'deleteRange' => [
			'range'          => [
				'sheetId' => '1871531335',
			],
			'shiftDimension' => 'ROWS'
		]
	] )
];

// Как удалить строки

$requests = [

new Google_Service_Sheets_Request( [

'deleteRange' => [

'range' => [

'sheetId' => '1871531335',

'startRowIndex' => 1,

'endRowIndex' => 3,

'shiftDimension' => 'ROWS'

]

] )

];

// Как удалить колонки

$requests = [

new Google_Service_Sheets_Request( [

'deleteRange' => [

'range' => [

'sheetId' => '1871531335',

'startColumnIndex' => 2,

'endColumnIndex' => 5

'shiftDimension' => 'ROWS'

]

] )

];

// Как удалить всё содержимое листа

$requests = [

new Google_Service_Sheets_Request( [

'deleteRange' => [

'range' => [

'sheetId' => '1871531335',

'shiftDimension' => 'ROWS'

]

] )

];

// Полное удаление столбцов
$requests = [
	new Google_Service_Sheets_Request( [
		'deleteDimension' => [
			'range'          => [
				'sheetId' => 1789805341,
				'startIndex' => 5,
				'endIndex' => 50,
				'dimension' => 'COLUMNS'
			]
		]
	] )
];
$batchUpdateRequest = new Google_Service_Sheets_BatchUpdateSpreadsheetRequest( [
	'requests' => $requests
] );
$service->spreadsheets->batchUpdate( $spreadsheetId, $batchUpdateRequest );

// Полное удаление строк
$requests = [
	new Google_Service_Sheets_Request( [
		'deleteDimension' => [
			'range'          => [
				'sheetId' => 1789805341,
				'startIndex' => 5,
				'endIndex' => 5000,
				'dimension' => 'ROWS'
			]
		]
	] )
];
$batchUpdateRequest = new Google_Service_Sheets_BatchUpdateSpreadsheetRequest( [
	'requests' => $requests
] );
$service->spreadsheets->batchUpdate( $spreadsheetId, $batchUpdateRequest );

// Полное удаление столбцов

$requests = [

new Google_Service_Sheets_Request( [

'deleteDimension' => [

'range' => [

'sheetId' => 1789805341,

'startIndex' => 5,

'endIndex' => 50,

'dimension' => 'COLUMNS'

]

] )

];

$batchUpdateRequest = new Google_Service_Sheets_BatchUpdateSpreadsheetRequest( [

'requests' => $requests

] );

$service->spreadsheets->batchUpdate( $spreadsheetId, $batchUpdateRequest );

// Полное удаление строк

$requests = [

new Google_Service_Sheets_Request( [

'deleteDimension' => [

'range' => [

'sheetId' => 1789805341,

'startIndex' => 5,

'endIndex' => 5000,

'dimension' => 'ROWS'

]

] )

];

$batchUpdateRequest = new Google_Service_Sheets_BatchUpdateSpreadsheetRequest( [

'requests' => $requests

] );

$service->spreadsheets->batchUpdate( $spreadsheetId, $batchUpdateRequest );

Результат:

Обновление содержимого листа.

// https://developers.google.com/sheets/api/reference/rest/v4/spreadsheets.values/update
$values = [
	["2016-02-12", "5453 543543", "=C2+C3"],
	["2017-02-12", "5453 543543", "=C2+C3"],
	["2018-02-12", "5453 543543", "=C2+C3"],
];
$body    = new Google_Service_Sheets_ValueRange( [ 'values' => $values ] );

// valueInputOption - определяет способ интерпретации входных данных
// https://developers.google.com/sheets/api/reference/rest/v4/ValueInputOption
// RAW | USER_ENTERED
$options = array( 'valueInputOption' => 'RAW' );

$service->spreadsheets_values->update( $spreadsheetId, 'Аналитика!A13', $body, $options );

// https://developers.google.com/sheets/api/reference/rest/v4/spreadsheets.values/update

$values = [

["2016-02-12", "5453 543543", "=C2+C3"],

["2017-02-12", "5453 543543", "=C2+C3"],

["2018-02-12", "5453 543543", "=C2+C3"],

];

$body = new Google_Service_Sheets_ValueRange( [ 'values' => $values ] );

// valueInputOption - определяет способ интерпретации входных данных

// https://developers.google.com/sheets/api/reference/rest/v4/ValueInputOption

// RAW | USER_ENTERED

$options = array( 'valueInputOption' => 'RAW' );

$service->spreadsheets_values->update( $spreadsheetId, 'Аналитика!A13', $body, $options );

Результат:

$options = array( 'valueInputOption' => 'USER_ENTERED' );
$service->spreadsheets_values->update( $spreadsheetId, 'Аналитика!A13', $body, $options );

1 2	$options = array( 'valueInputOption' => 'USER_ENTERED' ); $service->spreadsheets_values->update( $spreadsheetId, 'Аналитика!A13', $body, $options );

Результат:

Изменение формата отображения данных (шрифты, выравнивание, цвет и т.д.).

$requests = [
	new Google_Service_Sheets_Request( [
		'repeatCell' => [

			// Диапазон, который будет затронут
			"range" => [
				"sheetId"          => 1871531335, // ID листа
				"startRowIndex"    => 3,
				"endRowIndex"      => 5,
				"startColumnIndex" => 3,
				"endColumnIndex"   => 5
			],

			// Формат отображения данных
			// https://developers.google.com/sheets/api/reference/rest/v4/spreadsheets#CellFormat
			"cell"  => [
				"userEnteredFormat" => [
					// Фон (RGBA)
					// https://developers.google.com/sheets/api/reference/rest/v4/spreadsheets#Color
					"backgroundColor"     => [
						"green" => 1,
						"red"   => 1
					],
					// https://developers.google.com/sheets/api/reference/rest/v4/spreadsheets#HorizontalAlign
					"horizontalAlignment" => "CENTER",
					// https://developers.google.com/sheets/api/reference/rest/v4/spreadsheets#padding
					"padding"             => [
						"left"   => 10,
						"bottom" => 50,
						"right"  => 30,
						"top"    => 11
					],
					// https://developers.google.com/sheets/api/reference/rest/v4/spreadsheets#textformat
					"textFormat"          => [
						"bold"      => true,
						"fontSize"  => 25,
						"italic"    => true,
						"underline" => true
					]
				]
			],

			"fields" => "UserEnteredFormat(backgroundColor,horizontalAlignment,padding,textFormat)"
		]
	] )
];

$batchUpdateRequest = new Google_Service_Sheets_BatchUpdateSpreadsheetRequest( [
	'requests' => $requests
] );

$service->spreadsheets->batchUpdate( $spreadsheetId, $batchUpdateRequest );

$requests = [

new Google_Service_Sheets_Request( [

'repeatCell' => [

// Диапазон, который будет затронут

"range" => [

"sheetId" => 1871531335, // ID листа

"startRowIndex" => 3,

"endRowIndex" => 5,

"startColumnIndex" => 3,

"endColumnIndex" => 5

// Формат отображения данных

// https://developers.google.com/sheets/api/reference/rest/v4/spreadsheets#CellFormat

"cell" => [

"userEnteredFormat" => [

// Фон (RGBA)

// https://developers.google.com/sheets/api/reference/rest/v4/spreadsheets#Color

"backgroundColor" => [

"green" => 1,

"red" => 1

// https://developers.google.com/sheets/api/reference/rest/v4/spreadsheets#HorizontalAlign

"horizontalAlignment" => "CENTER",

// https://developers.google.com/sheets/api/reference/rest/v4/spreadsheets#padding

"padding" => [

"left" => 10,

"bottom" => 50,

"right" => 30,

"top" => 11

// https://developers.google.com/sheets/api/reference/rest/v4/spreadsheets#textformat

"textFormat" => [

"bold" => true,

"fontSize" => 25,

"italic" => true,

"underline" => true

]

"fields" => "UserEnteredFormat(backgroundColor,horizontalAlignment,padding,textFormat)"

]

] )

];

$batchUpdateRequest = new Google_Service_Sheets_BatchUpdateSpreadsheetRequest( [

'requests' => $requests

] );

$service->spreadsheets->batchUpdate( $spreadsheetId, $batchUpdateRequest );

Результат:

3. Обработка ошибок

start:

try {

	// Для примера запускаем больше количество запросов к API без пауз, чтобы произошло первышение лимита "USER-100s" (лимит 100 запросов за 100 сек.)
	for ($i = 0; $i < 200; $i++) {
		$range = 'Аналитика!B1:D3';
		$response = $service->spreadsheets_values->get($spreadsheetId, $range);
	}


} catch (Google_Service_Exception $exception) {

	// Список всех возможных ошибок и исключений https://developers.google.com/drive/v3/web/handle-errors
	// Получаем информацию о причине возникновения исключения
	$reason = $exception->getErrors()[0]['reason'];

	// При превышении лимита на количество запросок к API ставим паузу и, используя оператор goto запускаем процесс по новой
	if ( $reason == 'rateLimitExceeded' ) {
		sleep( 10 );
		goto start;
	} else {
		// В случае иной причины возникновения исключения передаём обработку исключения заданной функции
		exceptionHandler( $exception );
	}

}

set_exception_handler('exceptionHandler');

function exceptionHandler(Exception $exception) {

	$exceptionClass = get_class($exception);

	if ($exceptionClass == 'Google_Service_Exception') {
		$message = $exception->getErrors()[0]['message'];
	} else {
		$message = $exception->getMessage();
	}

	echo $message, "<br/>\n";
	echo $exception->getFile(), ':', $exception->getLine(), "<br/>\n";
	echo $exception->getTraceAsString(), "<br/>\n";

	exit();
}

start:

try {

// Для примера запускаем больше количество запросов к API без пауз, чтобы произошло первышение лимита "USER-100s" (лимит 100 запросов за 100 сек.)

for ($i = 0; $i < 200; $i++) {

$range = 'Аналитика!B1:D3';

$response = $service->spreadsheets_values->get($spreadsheetId, $range);

}

} catch (Google_Service_Exception $exception) {

// Список всех возможных ошибок и исключений https://developers.google.com/drive/v3/web/handle-errors

// Получаем информацию о причине возникновения исключения

$reason = $exception->getErrors()[0]['reason'];

// При превышении лимита на количество запросок к API ставим паузу и, используя оператор goto запускаем процесс по новой

if ( $reason == 'rateLimitExceeded' ) {

sleep( 10 );

goto start;

} else {

// В случае иной причины возникновения исключения передаём обработку исключения заданной функции

exceptionHandler( $exception );

}

set_exception_handler('exceptionHandler');

function exceptionHandler(Exception $exception) {

$exceptionClass = get_class($exception);

if ($exceptionClass == 'Google_Service_Exception') {

$message = $exception->getErrors()[0]['message'];

} else {

$message = $exception->getMessage();

}

echo $message, "<br/>\n";

echo $exception->getFile(), ':', $exception->getLine(), "<br/>\n";

echo $exception->getTraceAsString(), "<br/>\n";

exit();

}

4. Скачивание таблицы в формате .xlsx с использованием ключа сервисного аккаунта

<?php

$api = array();
$accessToken = '';

$sheetId = '1KdTaK81c3BURTW4D7QMGgkEexd_ATYx3pUiUNbsmNlY'; // ID таблицы, которую необходимо скачать
$savePath = __DIR__ . '/google-file.xlsx'; // Путь до файла, в который будет сохранено содержимое таблицы

$api['keys']['private']['file'] = __DIR__ . '/testgooglekey.json'; // Путь до файла с ключом доступа к сервисному аккаунту
$api['jwt']['token']['file'] = __DIR__ . '/token.dat'; // Путь до файла, в котором будет храниться токен авторизации

$api['gdrive']['impersonator'] = "";

$api['gapis']['oauth']['grant_type'] = 'urn:ietf:params:oauth:grant-type:jwt-bearer';
$api['gapis']['oauth']['token'] = 'https://www.googleapis.com/oauth2/v4/token';
$api['gapis']['drive']['scope'] = 'https://www.googleapis.com/auth/drive';
$api['gapis']['drive']['files'] = 'https://www.googleapis.com/drive/v3/files';

function sendRequest($url, $header, $data = [], $method = "GET")
{
    $ch = curl_init();
    curl_setopt($ch, CURLOPT_SSL_VERIFYPEER, false);
    curl_setopt($ch, CURLOPT_SSL_VERIFYHOST, false);
    curl_setopt($ch, CURLOPT_URL, $url);
    curl_setopt($ch, CURLOPT_CUSTOMREQUEST, $method);
    curl_setopt($ch, CURLOPT_HTTPHEADER, $header);
    curl_setopt($ch, CURLOPT_POSTFIELDS, http_build_query($data));
    curl_setopt($ch, CURLOPT_RETURNTRANSFER, true);
    $response = curl_exec($ch);
    curl_close($ch);
    return $response;
}

/************************** Авторизуемся **************************************/

$api['keys']['private']['contents'] = json_decode(file_get_contents($api['keys']['private']['file']), true);

$api['jwt']['header']['alg'] = 'RS256';
$api['jwt']['header']['typ'] = 'JWT';

$api['jwt']['claim_set']['iss'] = $api['keys']['private']['contents']['client_email'];
if ($api['gdrive']['impersonator'] != "") {
    $api['jwt']['claim_set']['sub'] = $api['gdrive']['impersonator'];
}
$api['jwt']['claim_set']['scope'] = $api['gapis']['drive']['scope'];
$api['jwt']['claim_set']['aud'] = $api['gapis']['oauth']['token'];
$api['jwt']['claim_set']['exp'] = strtotime('+1 hour');
$api['jwt']['claim_set']['iat'] = strtotime('now');

$api['jwt']['assertion'] = rtrim(strtr(base64_encode(json_encode($api['jwt']['header'])), '+/', '-_'), '=');
$api['jwt']['assertion'] .= "." . rtrim(strtr(base64_encode(json_encode($api['jwt']['claim_set'])), '+/', '-_'), '=');
$result = openssl_sign(
    $api['jwt']['assertion'],
    $signature,
    openssl_pkey_get_private($api['keys']['private']['contents']['private_key']),
    'sha256');
if ($result === true) {
    $api['jwt']['assertion'] .= "." . rtrim(strtr(base64_encode($signature), '+/', '-_'), '=');
}

$url = $api['gapis']['oauth']['token'];
$header[] = 'Content-Type: application/x-www-form-urlencoded';
$data['grant_type'] = $api['gapis']['oauth']['grant_type'];
$data['assertion'] = $api['jwt']['assertion'];

$api['jwt']['token']['response'] = (array) json_decode(sendRequest($url, $header, $data, "POST"));

if (isset($api['jwt']['token']['response']['access_token'])) {
    $accessToken = $api['jwt']['token']['response']['access_token'];
    file_put_contents($api['jwt']['token']['file'], base64_encode($accessToken));
    $api['jwt']['token']['minutes'] = 59;
}

unset($api['keys']);
unset($api['jwt']);
unset($api['gapis']['oauth']);

/************************** Скачиваем и сохраняем файл **************************************/
$header = [];
$header[] = 'Content-Type: application/x-www-form-urlencoded';
$header[] = 'Authorization: Bearer ' . $accessToken;

$sheetExportUrl = 'https://docs.google.com/spreadsheets/d/' . $sheetId . '/export?format=xlsx&id=' . $sheetId;

$fileBody = sendRequest($sheetExportUrl, $header);
file_put_contents($savePath, $fileBody);

<?php

$api = array();

$accessToken = '';

$sheetId = '1KdTaK81c3BURTW4D7QMGgkEexd_ATYx3pUiUNbsmNlY'; // ID таблицы, которую необходимо скачать

$savePath = __DIR__ . '/google-file.xlsx'; // Путь до файла, в который будет сохранено содержимое таблицы

$api['keys']['private']['file'] = __DIR__ . '/testgooglekey.json'; // Путь до файла с ключом доступа к сервисному аккаунту

$api['jwt']['token']['file'] = __DIR__ . '/token.dat'; // Путь до файла, в котором будет храниться токен авторизации

$api['gdrive']['impersonator'] = "";

$api['gapis']['oauth']['grant_type'] = 'urn:ietf:params:oauth:grant-type:jwt-bearer';

$api['gapis']['oauth']['token'] = 'https://www.googleapis.com/oauth2/v4/token';

$api['gapis']['drive']['scope'] = 'https://www.googleapis.com/auth/drive';

$api['gapis']['drive']['files'] = 'https://www.googleapis.com/drive/v3/files';

function sendRequest($url, $header, $data = [], $method = "GET")

{

$ch = curl_init();

curl_setopt($ch, CURLOPT_SSL_VERIFYPEER, false);

curl_setopt($ch, CURLOPT_SSL_VERIFYHOST, false);

curl_setopt($ch, CURLOPT_URL, $url);

curl_setopt($ch, CURLOPT_CUSTOMREQUEST, $method);

curl_setopt($ch, CURLOPT_HTTPHEADER, $header);

curl_setopt($ch, CURLOPT_POSTFIELDS, http_build_query($data));

curl_setopt($ch, CURLOPT_RETURNTRANSFER, true);

$response = curl_exec($ch);

curl_close($ch);

return $response;

}

/************************** Авторизуемся **************************************/

$api['keys']['private']['contents'] = json_decode(file_get_contents($api['keys']['private']['file']), true);

$api['jwt']['header']['alg'] = 'RS256';

$api['jwt']['header']['typ'] = 'JWT';

$api['jwt']['claim_set']['iss'] = $api['keys']['private']['contents']['client_email'];

if ($api['gdrive']['impersonator'] != "") {

$api['jwt']['claim_set']['sub'] = $api['gdrive']['impersonator'];

}

$api['jwt']['claim_set']['scope'] = $api['gapis']['drive']['scope'];

$api['jwt']['claim_set']['aud'] = $api['gapis']['oauth']['token'];

$api['jwt']['claim_set']['exp'] = strtotime('+1 hour');

$api['jwt']['claim_set']['iat'] = strtotime('now');

$api['jwt']['assertion'] = rtrim(strtr(base64_encode(json_encode($api['jwt']['header'])), '+/', '-_'), '=');

$api['jwt']['assertion'] .= "." . rtrim(strtr(base64_encode(json_encode($api['jwt']['claim_set'])), '+/', '-_'), '=');

$result = openssl_sign(

$api['jwt']['assertion'],

$signature,

openssl_pkey_get_private($api['keys']['private']['contents']['private_key']),

'sha256');

if ($result === true) {

$api['jwt']['assertion'] .= "." . rtrim(strtr(base64_encode($signature), '+/', '-_'), '=');

}

$url = $api['gapis']['oauth']['token'];

$header[] = 'Content-Type: application/x-www-form-urlencoded';

$data['grant_type'] = $api['gapis']['oauth']['grant_type'];

$data['assertion'] = $api['jwt']['assertion'];

$api['jwt']['token']['response'] = (array) json_decode(sendRequest($url, $header, $data, "POST"));

if (isset($api['jwt']['token']['response']['access_token'])) {

$accessToken = $api['jwt']['token']['response']['access_token'];

file_put_contents($api['jwt']['token']['file'], base64_encode($accessToken));

$api['jwt']['token']['minutes'] = 59;

}

unset($api['keys']);

unset($api['jwt']);

unset($api['gapis']['oauth']);

/************************** Скачиваем и сохраняем файл **************************************/

$header = [];

$header[] = 'Content-Type: application/x-www-form-urlencoded';

$header[] = 'Authorization: Bearer ' . $accessToken;

$sheetExportUrl = 'https://docs.google.com/spreadsheets/d/' . $sheetId . '/export?format=xlsx&id=' . $sheetId;

$fileBody = sendRequest($sheetExportUrl, $header);

file_put_contents($savePath, $fileBody);

#google api#google spreadsheets