API Google Таблиц позволяет создавать и обновлять сводные таблицы в электронных таблицах. Примеры на этой странице иллюстрируют, как выполнять некоторые распространённые операции со сводными таблицами с помощью API Таблиц.
Эти примеры представлены в виде HTTP-запросов, чтобы не зависеть от языка. Чтобы узнать, как реализовать пакетное обновление на разных языках с помощью клиентских библиотек API Google, см. раздел Обновление электронных таблиц .
В этих примерах плейсхолдеры SPREADSHEET_ID и SHEET_ID указывают, где следует указать эти идентификаторы. Идентификатор таблицы можно найти в URL-адресе таблицы. Получить идентификатор листа можно с помощью метода spreadsheets.get . Диапазоны указываются в формате A1 . Пример диапазона: Sheet1!A1:D5.
Кроме того, плейсхолдер SOURCE_SHEET_ID указывает на ваш лист с исходными данными. В данных примерах это таблица, указанная в разделе «Исходные данные сводной таблицы» .
Исходные данные сводной таблицы
В этих примерах предположим, что на первом листе используемой электронной таблицы («Лист1») находятся следующие исходные данные о продажах. Строки в первой строке — это заголовки отдельных столбцов. Примеры чтения данных с других листов электронной таблицы см. в разделе «Обозначение A1» .
| А | Б | С | Д | Э | Ф | Г | |
| 1 | Категория товара | Номер модели | Расходы | Количество | Область | Продавец | Дата отправки |
| 2 | Колесо | W-24 | 20,50 долларов | 4 | Запад | Бет | 01.03.2016 |
| 3 | Дверь | Д-01Х | 15,00 долларов США | 2 | Юг | Амир | 15.03.2016 |
| 4 | Двигатель | АНГ-0134 | 100,00 долларов США | 1 | Север | Кармен | 20.03.2016 |
| 5 | Рамка | ФР-0Б1 | 34,00 доллара США | 8 | Восток | Ханна | 3/12/2016 |
| 6 | Панель | П-034 | 6,00 долларов | 4 | Север | Девин | 02.04.2016 |
| 7 | Панель | П-052 | 11,50 долларов США | 7 | Восток | Эрик | 16.05.2016 |
| 8 | Колесо | W-24 | 20,50 долларов | 11 | Юг | Шелдон | 30.04.2016 |
| 9 | Двигатель | ЕНГ-0161 | 330,00 долларов США | 2 | Север | Джесси | 02.07.2016 |
| 10 | Дверь | Д-01Y | 29,00 долларов США | 6 | Запад | Армандо | 13.03.2016 |
| 11 | Рамка | ФР-0Б1 | 34,00 доллара США | 9 | Юг | Юлиана | 27.02.2016 |
| 12 | Панель | П-102 | 3,00 доллара | 15 | Запад | Кармен | 18.04.2016 |
| 13 | Панель | П-105 | 8,25 долларов | 13 | Запад | Джесси | 20.06.2016 |
| 14 | Двигатель | АНГ-0211 | 283,00 доллара США | 1 | Север | Амир | 21.06.2016 |
| 15 | Дверь | Д-01Х | 15,00 долларов США | 2 | Запад | Армандо | 03.07.2016 |
| 16 | Рамка | ФР-0Б1 | 34,00 доллара США | 6 | Юг | Кармен | 15.07.2016 |
| 17 | Колесо | W-25 | 20,00 долларов США | 8 | Юг | Ханна | 02.05.2016 |
| 18 | Колесо | W-11 | 29,00 долларов США | 13 | Восток | Эрик | 19.05.2016 |
| 19 | Дверь | Д-05 | 17,70 долларов США | 7 | Запад | Бет | 28.06.2016 |
| 20 | Рамка | ФР-0Б1 | 34,00 доллара США | 8 | Север | Шелдон | 30.03.2016 |
Добавить сводную таблицу
В следующем примере кода spreadsheets.batchUpdate показано, как использовать UpdateCellsRequest для создания сводной таблицы из исходных данных, прикрепляя ее к ячейке A50 листа, указанного в SHEET_ID .
Запрос настраивает сводную таблицу со следующими свойствами:
- Одна группа значений ( Quantity ), которая указывает количество продаж. Поскольку группа значений только одна, два возможных варианта настройки
valueLayoutэквивалентны. - Две группы строк ( Категория товара и Номер модели ). Первая группа сортируется по возрастанию общего количества товаров в регионе «Запад». Таким образом, «Двигатель» (без продаж в Западном регионе) отображается выше «Двери» (с продажами в Западном регионе 15). Группа «Номер модели» сортируется по убыванию общего количества товаров во всех регионах, поэтому «W-24» (15 продаж) отображается выше «W-25» (8 продаж). Это достигается установкой поля
valueBucketв значение{}. - Одна группа столбцов ( Region ), которая сортируется по возрастанию наибольшего количества продаж. Значение
valueBucketснова равно{}. У столбца «North» наименьший общий объём продаж, поэтому он отображается в качестве первого столбца «Region» .
Протокол запроса показан ниже.
POST https://sheets.googleapis.com/v4/spreadsheets/SPREADSHEET_ID:batchUpdate{ "requests": [ { "updateCells": { "rows": [ { "values": [ { "pivotTable": { "source": { "sheetId":SOURCE_SHEET_ID, "startRowIndex": 0, "startColumnIndex": 0, "endRowIndex": 20, "endColumnIndex": 7 }, "rows": [ { "sourceColumnOffset": 0, "showTotals": true, "sortOrder": "ASCENDING", "valueBucket": { "buckets": [ { "stringValue": "West" } ] } }, { "sourceColumnOffset": 1, "showTotals": true, "sortOrder": "DESCENDING", "valueBucket": {} } ], "columns": [ { "sourceColumnOffset": 4, "sortOrder": "ASCENDING", "showTotals": true, "valueBucket": {} } ], "values": [ { "summarizeFunction": "SUM", "sourceColumnOffset": 3 } ], "valueLayout": "HORIZONTAL" } } ] } ], "start": { "sheetId":SHEET_ID, "rowIndex": 49, "columnIndex": 0 }, "fields": "pivotTable" } } ] }
Запрос создает сводную таблицу следующего вида:
Добавить сводную таблицу с расчетными значениями
В следующем примере кода spreadsheets.batchUpdate показано, как использовать UpdateCellsRequest для создания сводной таблицы с группой вычисляемых значений из исходных данных, прикрепив ее к ячейке A50 листа, указанного в параметре SHEET_ID .
Запрос настраивает сводную таблицу со следующими свойствами:
- Две группы значений ( Количество и Общая цена ). Первая группа указывает количество продаж. Вторая — это значение, рассчитываемое как произведение стоимости детали и общего количества продаж по следующей формуле:
=Cost*SUM(Quantity). - Три группы строк ( Категория товара , Номер модели и Стоимость ).
- Одна группа столбцов ( Регион ).
- Группы строк и столбцов сортируются по имени (а не по количеству ) в каждой группе, располагая таблицу в алфавитном порядке. Это достигается путём исключения поля
valueBucketизPivotGroup.- Чтобы упростить внешний вид таблицы, запрос скрывает промежуточные итоги для всех групп строк и столбцов, кроме основных.
- Запрос устанавливает
valueLayoutвVERTICALдля улучшения внешнего вида таблицы.valueLayoutважен только в том случае, если имеется 2 или более групп значений.
Протокол запроса показан ниже.
POST https://sheets.googleapis.com/v4/spreadsheets/SPREADSHEET_ID:batchUpdate{ "requests": [ { "updateCells": { "rows": [ { "values": [ { "pivotTable": { "source": { "sheetId":SOURCE_SHEET_ID, "startRowIndex": 0, "startColumnIndex": 0, "endRowIndex": 20, "endColumnIndex": 7 }, "rows": [ { "sourceColumnOffset": 0, "showTotals": true, "sortOrder": "ASCENDING" }, { "sourceColumnOffset": 1, "showTotals": false, "sortOrder": "ASCENDING", }, { "sourceColumnOffset": 2, "showTotals": false, "sortOrder": "ASCENDING", } ], "columns": [ { "sourceColumnOffset": 4, "sortOrder": "ASCENDING", "showTotals": true } ], "values": [ { "summarizeFunction": "SUM", "sourceColumnOffset": 3 }, { "summarizeFunction": "CUSTOM", "name": "Total Price", "formula": "=Cost*SUM(Quantity)" } ], "valueLayout": "VERTICAL" } } ] } ], "start": { "sheetId":SHEET_ID, "rowIndex": 49, "columnIndex": 0 }, "fields": "pivotTable" } } ] }
Запрос создает сводную таблицу следующего вида:
Удалить сводную таблицу
В следующем примере кода spreadsheets.batchUpdate показано, как использовать UpdateCellsRequest для удаления сводной таблицы (если она есть), привязанной к ячейке A50 листа, указанного в SHEET_ID .
UpdateCellsRequest может удалить сводную таблицу, включив «pivotTable» в параметр fields , а также исключив поле pivotTable в ячейке привязки.
Протокол запроса показан ниже.
POST https://sheets.googleapis.com/v4/spreadsheets/SPREADSHEET_ID:batchUpdate{ "requests": [ { "updateCells": { "rows": [ { "values": [ {} ] } ], "start": { "sheetId": SHEET_ID, "rowIndex": 49, "columnIndex": 0 }, "fields": "pivotTable" } } ] }Редактировать столбцы и строки сводной таблицы
В следующем примере кода spreadsheets.batchUpdate показано, как использовать UpdateCellsRequest для редактирования сводной таблицы, созданной в разделе Добавление сводной таблицы .
Подмножества поля pivotTable в ресурсе CellData нельзя изменять по отдельности с помощью параметра fields . Для внесения изменений необходимо указать всё поле pivotTable . По сути, редактирование сводной таблицы заключается в её замене на новую.
Запрос вносит следующие изменения в исходную сводную таблицу:
- Удаляет вторую группу строк из исходной сводной таблицы ( Номер модели ).
- Добавляет группу столбцов ( Продавец ). Столбцы сортируются по убыванию общего количества продаж по панели . «Кармен» (15 продаж по панели ) отображается слева от «Джесси» (13 продаж по панели ).
- Сворачивает столбец для каждого региона , кроме «Запад», скрывая группу «Продавец» для этого региона. Для этого нужно установить
collapsed«trueдля параметраvalueMetadataэтого столбца в группе столбцов « Регион» .
Протокол запроса показан ниже.
POST https://sheets.googleapis.com/v4/spreadsheets/SPREADSHEET_ID:batchUpdate{ "requests": [ { "updateCells": { "rows": [ { "values": [ { "pivotTable": { "source": { "sheetId":SOURCE_SHEET_ID, "startRowIndex": 0, "startColumnIndex": 0, "endRowIndex": 20, "endColumnIndex": 7 }, "rows": [ { "sourceColumnOffset": 0, "showTotals": true, "sortOrder": "ASCENDING", "valueBucket": { "buckets": [ { "stringValue": "West" } ] } } ], "columns": [ { "sourceColumnOffset": 4, "sortOrder": "ASCENDING", "showTotals": true, "valueBucket": {}, "valueMetadata": [ { "value": { "stringValue": "North" }, "collapsed": true }, { "value": { "stringValue": "South" }, "collapsed": true }, { "value": { "stringValue": "East" }, "collapsed": true } ] }, { "sourceColumnOffset": 5, "sortOrder": "DESCENDING", "showTotals": false, "valueBucket": { "buckets": [ { "stringValue": "Panel" } ] }, } ], "values": [ { "summarizeFunction": "SUM", "sourceColumnOffset": 3 } ], "valueLayout": "HORIZONTAL" } } ] } ], "start": { "sheetId":SHEET_ID, "rowIndex": 49, "columnIndex": 0 }, "fields": "pivotTable" } } ] }
Запрос создает сводную таблицу следующего вида:
Чтение данных сводной таблицы
В следующем примере кода spreadsheets.get показано, как получить данные сводной таблицы из электронной таблицы. Параметр запроса fields указывает, что должны быть возвращены только данные сводной таблицы (а не значения ячеек).
Протокол запроса показан ниже.
GET https://sheets.googleapis.com/v4/spreadsheets/SPREADSHEET_ID?fields=sheets(properties.sheetId,data.rowData.values.pivotTable) Ответ состоит из ресурса Spreadsheet , содержащего объект Sheet с элементами SheetProperties . Также имеется массив элементов GridData , содержащий информацию о PivotTable . Информация о сводной таблице содержится в ресурсе CellData листа для ячейки, к которой привязана таблица (то есть в левом верхнем углу таблицы). Если для поля ответа задано значение по умолчанию, оно не включается в ответ.
В этом примере первый лист ( SOURCE_SHEET_ID ) содержит исходные данные исходной таблицы, а второй лист ( SHEET_ID ) — сводную таблицу, привязанную к ячейке B3. Пустые фигурные скобки обозначают листы или ячейки, не содержащие данных сводной таблицы. Для справки: этот запрос также возвращает идентификаторы листов.
{ "sheets": [ { "data": [{}], "properties": { "sheetId":SOURCE_SHEET_ID} }, { "data": [ { "rowData": [ {}, {}, { "values": [ {}, { "pivotTable": { "columns": [ { "showTotals": true, "sortOrder": "ASCENDING", "sourceColumnOffset": 4, "valueBucket": {} } ], "rows": [ { "showTotals": true, "sortOrder": "ASCENDING", "valueBucket": { "buckets": [ { "stringValue": "West" } ] } }, { "showTotals": true, "sortOrder": "DESCENDING", "valueBucket": {}, "sourceColumnOffset": 1 } ], "source": { "sheetId":SOURCE_SHEET_ID, "startColumnIndex": 0, "endColumnIndex": 7, "startRowIndex": 0, "endRowIndex": 20 }, "values": [ { "sourceColumnOffset": 3, "summarizeFunction": "SUM" } ] } } ] } ] } ], "properties": { "sheetId":SHEET_ID} } ], }