Tabelas dinâmicas

A API Google Sheets permite que você crie e atualize tabelas dinâmicas dentro de planilhas. Os exemplos nesta página ilustram como você pode obter algumas com a API Sheets.

Esses exemplos são apresentados na forma de solicitações HTTP para serem neutras. Para saber como implementar uma atualização em lote em diferentes idiomas usando bibliotecas de clientes da API do Google, consulte Atualizar planilhas.

Nestes exemplos, os marcadores de posição SPREADSHEET_ID e SHEET_ID indica onde você deve fornecer esses IDs. Você pode encontrar a planilha ID no URL da planilha. Você pode receber o ID da planilha usando o spreadsheets.get. A são especificados usando a notação A1. Um exemplo de intervalo é Sheet1!A1:D5.

Além disso, o marcador de posição SOURCE_SHEET_ID indica que sua página com os dados de origem. Nestes exemplos, esta é a tabela listada em Tabela dinâmica dados de origem da tabela.

Dados de origem da tabela dinâmica

Para esses exemplos, suponha que a planilha usada tenha a seguinte fonte "vendas" dados em sua primeira planilha ("Sheet1"). As strings na primeira linha são rótulos nas colunas individuais. Para ver exemplos de como ler dados de outros páginas na sua planilha, consulte a notação A1.

A B C D E F G
1 Categoria do item Número do modelo Custo Quantidade Região Vendedor Data de envio
2 Wheel W-24 US$ 20,50 4 Oeste Bete 01/03/2016
3 Porta D-01X US$ 15,00 2 Sul Amir 15/03/2016
4 Mecanismo ENG-0134 US$ 100 1 Norte Carmen 20/03/2016
5 Quadro FR-0B1 US$ 34,00 8 Leste Hannah 12/03/2016
6 Painel P-034 US$ 6,00 4 Norte Devyn 02/04/2016
7 Painel P-052 US$ 11,50 7 Leste Erik 16/05/2016
8 Wheel W-24 US$ 20,50 11 Sul Sheldon 30/04/2016
9 Mecanismo ENG-0161 US$ 330,00 2 Norte Jéssica 02/07/2016
10 Porta D-01Y US$ 29,00 6 Oeste Armando 13/03/2016
11 Quadro FR-0B1 US$ 34,00 9 Sul Yuliana 27/02/2016
12 Painel P-102 US$ 3,00 15 Oeste Carmen 18/04/2016
13 Painel P-105 US$ 8,25 13 Oeste Jéssica 20/06/2016
14 Mecanismo ENG-0211 US$ 283,00 1 Norte Amir 21/06/2016
15 Porta D-01X US$ 15,00 2 Oeste Armando 03/07/2016
16 Quadro FR-0B1 US$ 34,00 6 Sul Carmen 15/07/2016
17 Wheel W-25 US$ 20,00 8 Sul Hannah 02/05/2016
18 Wheel W-11 US$ 29,00 13 Leste Erik 19/05/2016
19 Porta D-05 US$ 17,70 7 Oeste Bete 28/06/2016
20 Quadro FR-0B1 US$ 34,00 8 Norte Sheldon 30/03/2016

Adicionar uma tabela dinâmica

O seguinte spreadsheets.batchUpdate um exemplo de código mostra como usar a UpdateCellsRequest para criar uma tabela dinâmica a partir dos dados de origem, ancorando-a na célula A50 da planilha especificada por SHEET_ID.

A solicitação configura a tabela dinâmica com as seguintes propriedades:

  • um grupo de valores (Quantity) que indica o número de vendas. Como há apenas um grupo de valores, os dois possíveis valueLayout são equivalentes.
  • Dois grupos de linhas (Item Category e Model Number). A primeira classifica valor crescente do total de Quantidade em "Oeste" Região. Portanto, "Motor" (sem vendas na região oeste) aparece acima de "Porta" (com 15 vendas na região oeste). A O grupo Número do modelo classifica em ordem decrescente do total de vendas em todas ou seja, "W-24" (15 vendas) aparece acima de "W-25" (8 vendas). Pronto definindo o valueBucket como {}.
  • Um grupo de colunas (Região) que é classificado em ordem crescente com o maior número de vendas. Novamente, valueBucket é definido como {}. "Norte" tem o menor total de vendas e para que apareça como a primeira coluna Região.

Confira o protocolo da solicitação abaixo.

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"
      }
    }
  ]
}

A solicitação cria uma tabela dinâmica como esta:

Resultado do roteiro "Adicionar tabela dinâmica"

Adicionar uma tabela dinâmica com valores calculados

O seguinte spreadsheets.batchUpdate um exemplo de código mostra como usar a UpdateCellsRequest para criar uma tabela dinâmica com um grupo de valores calculados a partir dos dados de origem, ancorando-o na célula A50 da planilha especificada por SHEET_ID.

A solicitação configura a tabela dinâmica com as seguintes propriedades:

  • Dois grupos de valores (Quantity e Total Price). O primeiro indica número de vendas. O segundo é um valor calculado com base no produto de um o custo de uma peça e seu número total de vendas, usando esta fórmula: =Cost*SUM(Quantity):
  • Três grupos de linhas (Item Category, Model Number e Cost).
  • Um grupo de colunas (Região).
  • Os grupos de linhas e colunas são classificados por nome (e não por Quantidade) em cada grupo, colocando a tabela em ordem alfabética. Isso é feito com a omissão da valueBucket da PivotGroup
  • Para simplificar a aparência da tabela, a solicitação oculta subtotais para todos os grupos, exceto para as linhas e colunas principais.
  • A solicitação define valueLayout. para VERTICAL para melhorar a aparência da tabela. valueLayout é apenas importante se houver dois ou mais grupos de valores.

Confira o protocolo da solicitação abaixo.

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"
      }
    }
  ]
}

A solicitação cria uma tabela dinâmica como esta:

Adicionar resultado do roteiro do grupo de valores dinâmicos

Excluir uma tabela dinâmica

O seguinte spreadsheets.batchUpdate um exemplo de código mostra como usar a UpdateCellsRequest para excluir uma tabela dinâmica (se houver) ancorada na célula A50 da página especificado por SHEET_ID.

Um UpdateCellsRequest pode remover uma tabela dinâmica incluindo "pivotTable" no o parâmetro fields, além de omitir o campo pivotTable na âncora célula

Confira o protocolo da solicitação abaixo.

POST https://sheets.googleapis.com/v4/spreadsheets/SPREADSHEET_ID:batchUpdate
{
  "requests": [
    {
      "updateCells": {
          "rows": [ 
            {
            "values": [
              {}
            ]
          }
        ],
        "start": {
          "sheetId": SHEET_ID,
          "rowIndex": 49,
          "columnIndex": 0
        },
        "fields": "pivotTable"
      }
    }
  ]
}

Editar linhas e colunas da tabela dinâmica

O seguinte spreadsheets.batchUpdate um exemplo de código mostra como usar a UpdateCellsRequest para editar a tabela dinâmica criada em Adicionar uma tabela dinâmica.

Os subconjuntos do pivotTable na Recurso CellData não pode ser alterado individualmente com o parâmetro fields. Para fazer edições, o É necessário informar todo o campo pivotTable. Basicamente, editar uma tabela dinâmica requer a substituição por um novo.

A solicitação faz as seguintes alterações na tabela dinâmica original:

  • Remove o grupo da segunda linha da tabela dinâmica original (Número do modelo).
  • Adiciona um grupo de colunas (Vendedor). As colunas são classificadas em ordem decrescente por o número total de vendas do Painel. "Carmen" (15 vendas Painel) parece à esquerda de "Jessie" (13 vendas de Painel).
  • Recolhe a coluna para cada Região, exceto "Oeste", ocultando o Grupo de vendedores para essa região. Isso é feito ao definir collapsed como true na valueMetadata para essa coluna no grupo de colunas Região.

Confira o protocolo da solicitação abaixo.

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"
      }
    }
  ]
}

A solicitação cria uma tabela dinâmica como esta:

Editar resultado do roteiro da tabela dinâmica

Ler dados da tabela dinâmica

O seguinte Exemplo de código do spreadsheets.get mostra como obter dados da tabela dinâmica de uma planilha. A consulta fields especifica que apenas os dados da tabela dinâmica devem ser retornados (como em oposição aos dados dos valores das células).

Confira o protocolo da solicitação abaixo.

GET https://sheets.googleapis.com/v4/spreadsheets/SPREADSHEET_ID?fields=sheets(properties.sheetId,data.rowData.values.pivotTable)

A resposta consiste em um Spreadsheet que contém um Objeto Sheet com SheetProperties os elementos. Há também uma matriz GridData que contêm informações sobre o PivotTable As informações da tabela dinâmica estão contidas nos arquivos Recurso CellData para a célula em que a tabela está ancorada (ou seja, o canto superior esquerdo direito). Se um campo de resposta for definido com o valor padrão, ele será omitido do resposta.

Neste exemplo, a primeira página (SOURCE_SHEET_ID) tem a tabela bruta dados de origem, enquanto a segunda página (SHEET_ID) tem a tabela dinâmica, com base em B3. As chaves vazias indicam páginas ou células que não contêm dados da tabela dinâmica. Para referência, esta solicitação também retorna a planilha do Google Ads.

{
  "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
      }
    }
  ],
}