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

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

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

В этих примерах заполнители 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 .
  • Чтобы упростить внешний вид таблицы, запрос скрывает промежуточные итоги для всех, кроме основных групп строк и столбцов.
  • Запрос устанавливает для valueLayout VERTICAL для улучшения внешнего вида таблицы. 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 панелей ).
  • Сворачивает столбец для каждого региона , кроме «Запад», скрывая группу продавцов для этого региона. Это делается путем установки 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
      }
    }
  ],
}
 ,

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

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

В этих примерах заполнители 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 .
  • Чтобы упростить внешний вид таблицы, запрос скрывает промежуточные итоги для всех, кроме основных групп строк и столбцов.
  • Запрос устанавливает для valueLayout VERTICAL для улучшения внешнего вида таблицы. 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 панелей ).
  • Сворачивает столбец для каждого региона , кроме «Запад», скрывая группу продавцов для этого региона. Это делается путем установки 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
      }
    }
  ],
}