Сводные таблицы

API Google Sheets позволяет создавать и обновлять сводные таблицы в электронных таблицах. Примеры на этой странице иллюстрируют, как можно выполнить некоторые распространенные операции со сводными таблицами с помощью API Sheets.

Эти примеры представлены в виде HTTP-запросов, чтобы обеспечить языковую нейтральность. Чтобы узнать, как реализовать пакетное обновление на разных языках с помощью клиентских библиотек Google API, см. раздел «Обновление электронных таблиц» .

В этих примерах заполнители SPREADSHEET_ID и SHEET_ID указывают, где следует указывать эти идентификаторы. Идентификатор электронной таблицы можно найти в URL-адресе электронной таблицы. Идентификатор листа можно получить, используя метод spreadsheets.get . Диапазоны указываются с использованием обозначения A1 . Пример диапазона: Sheet1!A1:D5.

Кроме того, в поле SOURCE_SHEET_ID указан ваш лист с исходными данными. В этих примерах это таблица, указанная в разделе «Исходные данные сводной таблицы» .

Исходные данные сводной таблицы

В этих примерах предположим, что используемая электронная таблица содержит следующие исходные данные о продажах на первом листе («Лист 1»). Строки в первой строке — это метки для отдельных столбцов. Примеры чтения данных с других листов электронной таблицы см. в обозначении A1 .

А Б С Д Е Ф Г
1 Категория товара Номер модели Расходы Количество Область Продавец Дата отгрузки
2 Колесо В-24 20,50 долларов 4 Запад Бет 01.03.2016
3 Дверь Д-01X 15,00 долларов 2 Юг Амир 15.03.2016
4 Двигатель ENG-0134 100,00 долларов США 1 Север Кармен 20.03.2016
5 Рамка FR-0B1 34,00 долларов 8 Восток Ханна 3/12/2016
6 Панель П-034 6,00 долларов 4 Север Девин 02.04.2016
7 Панель П-052 11,50 долларов 7 Восток Эрик 16.05.2016
8 Колесо В-24 20,50 долларов 11 Юг Шелдон 30.04.2016
9 Двигатель ENG-0161 330,00 долларов США 2 Север Джесси 07.02.2016
10 Дверь Д-01Y 29,00 долларов 6 Запад Армандо 13.03.2016
11 Рамка FR-0B1 34,00 долларов 9 Юг Юлиана 27.02.2016
12 Панель П-102 3,00 доллара 15 Запад Кармен 18.04.2016
13 Панель П-105 8,25 долларов 13 Запад Джесси 20.06.2016
14 Двигатель ENG-0211 283,00 долларов США 1 Север Амир 21.06.2016
15 Дверь Д-01X 15,00 долларов 2 Запад Армандо 07.03.2016
16 Рамка FR-0B1 34,00 долларов 6 Юг Кармен 15.07.2016
17 Колесо В-25 20,00 долларов 8 Юг Ханна 05.02.2016
18 Колесо В-11 29,00 долларов 13 Восток Эрик 19.05.2016
19 Дверь Д-05 17,70 долларов 7 Запад Бет 28.06.2016
20 Рамка FR-0B1 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 .
    • Для упрощения отображения таблицы запрос скрывает промежуточные итоги для всех групп строк и столбцов, кроме основных.
  • Запрос устанавливает для 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 может удалить сводную таблицу, включив параметр fields в поле "pivotTable", но при этом опустив поле 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
      }
    }
  ],
}