API Google Таблиц позволяет создавать и обновлять сводные таблицы в электронных таблицах. Примеры на этой странице иллюстрируют, как можно выполнить некоторые распространенные операции со сводными таблицами с помощью Sheets API.
Эти примеры представлены в виде HTTP-запросов, которые не зависят от языка. Чтобы узнать, как реализовать пакетное обновление на разных языках с помощью клиентских библиотек API Google, см. раздел Обновление электронных таблиц .
В этих примерах заполнители SPREADSHEET_ID и SHEET_ID указывают, где вы будете предоставлять эти идентификаторы. Идентификатор таблицы можно найти в URL-адресе таблицы. Вы можете получить идентификатор листа , используя метод spreadsheets.get . Диапазоны указаны с использованием обозначения A1 . Пример диапазона: Лист1!A1:D5.
Кроме того, заполнитель SOURCE_SHEET_ID указывает на ваш лист с исходными данными. В этих примерах это таблица, указанная в разделе «Исходные данные сводной таблицы» .
Исходные данные сводной таблицы
В этих примерах предположим, что используемая электронная таблица содержит следующие исходные данные о продажах на первом листе («Лист1»). Строки в первой строке являются метками отдельных столбцов. Чтобы просмотреть примеры чтения с других листов в электронной таблице, см. обозначение A1 .
| А | Б | С | Д | Э | Ф | Г | |
| 1 | Категория товара | Номер модели | Расходы | Количество | Область | Продавец | Дата отправки |
| 2 | Колесо | W-24 | 20,50 долларов США | 4 | Запад | Бет | 01.03.2016 |
| 3 | Дверь | D-01X | 15,00 долларов США | 2 | Юг | Амир | 15.03.2016 |
| 4 | Двигатель | АНГ-0134 | 100,00 долларов США | 1 | Север | Кармен | 20.03.2016 |
| 5 | Рамка | ФР-0Б1 | $34,00 | 8 | Восток | Ханна | 12.03.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 | Дверь | D-01X | 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 .
Запрос настраивает сводную таблицу со следующими свойствами:
- Одна группа значений ( Количество ), указывающая количество продаж. Поскольку существует только одна группа значений, две возможные настройки
valueLayoutэквивалентны. - Две группы строк ( категория товара и номер модели ). Первый сортирует по возрастанию общего количества из региона «Запад». Таким образом, «Двигатель» (без продаж на Западе) появляется над «Дверью» (с 15 продажами на Западе). В группе «Номер модели» общий объем продаж во всех регионах сортируется по убыванию, поэтому «W-24» (15 продаж) отображается над «W-25» (8 продаж). Это делается путем установки поля
valueBucketв{}. - Одна группа столбцов ( Регион ), которая сортирует по возрастанию большинство продаж. Опять же, для
valueBucketустановлено значение{}. «Север» имеет наименьший общий объем продаж, поэтому он отображается в первом столбце «Регион» .
Протокол запроса показан ниже.
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. - Чтобы упростить внешний вид таблицы, запрос скрывает промежуточные итоги для всех, кроме основных групп строк и столбцов.
- Запрос устанавливает для
valueLayoutVERTICALдля улучшения внешнего вида таблицы.valueLayoutважен только при наличии двух или более групп значений.
Протокол запроса показан ниже.
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 . По сути, редактирование сводной таблицы требует замены ее новой.
Запрос вносит следующие изменения в исходную сводную таблицу:
- Удаляет вторую группу строк из исходной сводной таблицы ( номер модели ).
- Добавляет группу столбцов ( Продавец ). Столбцы отсортированы по убыванию общего количества продаж Panel . «Кармен» (продажа 15 панелей ) появляется слева от «Джесси» (продажа 13 панелей ).
- Сворачивает столбец для каждого региона , кроме «Запад», скрывая группу продавцов для этого региона. Это делается путем установки
collapsedtrueв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
}
}
],
}