Tabelas dinâmicas

Com a API Google Sheets, você pode criar e atualizar tabelas dinâmicas em planilhas. Os exemplos nesta página ilustram como você pode realizar algumas operações comuns de tabela dinâmica com a API Sheets.

Esses exemplos são apresentados na forma de solicitações HTTP para serem neutros em relação à linguagem. Para saber como implementar uma atualização em lote em diferentes linguagens usando as bibliotecas de cliente da API do Google, consulte Atualizar planilhas.

Nesses exemplos, os marcadores SPREADSHEET_ID e SHEET_ID indicam onde você forneceria esses IDs. Você pode encontrar o ID da planilha no URL da planilha. Você pode conseguir o ID da planilha usando o método spreadsheets.get. Os intervalos são especificados usando a notação A1. Um exemplo de intervalo é Sheet1!A1:D5.

Além disso, o marcador SOURCE_SHEET_ID indica sua planilha com os dados de origem. Nestes exemplos, esta é a tabela listada em Dados de origem da tabela dinâmica.

Dados de origem da tabela dinâmica

Para esses exemplos, suponha que a planilha usada tenha os seguintes dados de origem de "vendas" na primeira página ("Sheet1"). As strings na primeira linha são rótulos para as colunas individuais. Para ver exemplos de como ler outras páginas da planilha, consulte 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 Roda gigante W-24 US$ 20,50 4 Oeste Bete 01/03/2016
3 Porta Dado $15.00 2 Sul Amir 15/03/2016
4 Engine ENG-0134 $100.00 1 Norte Carmen 20/03/2016
5 Quadro FR-0B1 $34.00 8 Leste Hannah 12/03/2016
6 Painel P-034 $6.00 4 Norte Devyn 02/04/2016
7 Painel P-052 US$ 11,50 7 Leste Erik 16/05/2016
8 Roda gigante W-24 US$ 20,50 11 Sul Sheldon 30/04/2016
9 Engine ENG-0161 $330.00 2 Norte Jéssica 02/07/2016
10 Porta D-01Y $29.00 6 Oeste Armando 13/03/2016
11 Quadro FR-0B1 $34.00 9 Sul Yuliana 27/02/2016
12 Painel P-102 $3.00 15 Oeste Carmen 18/04/2016
13 Painel P-105 US$ 8,25 13 Oeste Jéssica 20/06/2016
14 Engine ENG-0211 $283.00 1 Norte Amir 21/06/2016
15 Porta Dado $15.00 2 Oeste Armando 03/07/2016
16 Quadro FR-0B1 $34.00 6 Sul Carmen 15/07/2016
17 Roda gigante W-25 $20.00 8 Sul Hannah 02/05/2016
18 Roda gigante W-11 $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 $34.00 8 Norte Sheldon 30/03/2016

Adicionar uma tabela dinâmica

O exemplo de código spreadsheets.batchUpdate a seguir mostra como usar UpdateCellsRequest para criar uma tabela dinâmica com base nos 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, as duas configurações valueLayout possíveis são equivalentes.
  • Dois grupos de linhas (Item Category e Model Number). A primeira classificação é feita no valor crescente do valor total de Quantity na Region "West". Portanto, "Motor" (sem vendas na região West) aparece acima de "Porta" (com 15 vendas na região West). O grupo Número do modelo é classificado em ordem decrescente do total de vendas em todas as regiões. Portanto, "W-24" (15 vendas) aparece acima de "W-25" (8 vendas). Isso é feito definindo o campo valueBucket como {}.
  • Um grupo de colunas (Region) que classifica em ordem crescente da maioria das vendas. Novamente, valueBucket é definido como {}. "North" tem o menor total de vendas e, portanto, aparece como a primeira coluna Region.

Confira abaixo o protocolo da solicitação.

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 da receita de adição de tabela dinâmica

Adicionar uma tabela dinâmica com valores calculados

O exemplo de código spreadsheets.batchUpdate a seguir mostra como usar a UpdateCellsRequest para criar uma tabela dinâmica com um grupo de valores calculados dos dados de origem, ancorando-o na célula A50 da página 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 o número de vendas. O segundo é um valor calculado com base no produto do 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 (Region).
  • Os grupos de linhas e colunas são classificados por nome (e não por Quantity) em cada grupo, colocando a tabela em ordem alfabética. Isso é feito omitindo o campo valueBucket de PivotGroup.
  • Para simplificar a aparência da tabela, a solicitação oculta os subtotais, exceto os grupos de linhas e colunas principais.
  • A solicitação define valueLayout como VERTICAL para melhorar a aparência da tabela. valueLayout só é importante se houver dois ou mais grupos de valores.

Confira abaixo o protocolo da solicitação.

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 da receita do grupo de valores dinâmicos

Excluir uma tabela dinâmica

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

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

Confira abaixo o protocolo da solicitação.

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 colunas e linhas da tabela dinâmica

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

Os subconjuntos do campo pivotTable no recurso CellData não podem ser alterados individualmente com o parâmetro fields. Para fazer edições, todo o campo pivotTable precisa ser fornecido. Basicamente, a edição de uma tabela dinâmica exige a substituição por uma nova.

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

  • Remove o grupo de segunda linhas da tabela dinâmica original (Model Number).
  • Adiciona um grupo de colunas (Vendedor). As colunas são classificadas em ordem decrescente pelo número total de vendas do Panel. "Carmen" (15 vendas de painel) aparece à esquerda de "Jessie" (13 vendas de Painel).
  • Recolhe a coluna para cada Region, exceto "Oeste", ocultando o grupo Seller dessa região. Isso é feito ao definir collapsed como true no valueMetadata dessa coluna no grupo de colunas Região.

Confira abaixo o protocolo da solicitação.

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 da receita de tabela dinâmica

Ler dados da tabela dinâmica

O exemplo de código spreadsheets.get a seguir mostra como receber dados da tabela dinâmica de uma planilha. O parâmetro de consulta fields especifica que somente os dados da tabela dinâmica devem ser retornados, e não os dados de valor da célula.

Confira abaixo o protocolo da solicitação.

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

A resposta consiste em um recurso Spreadsheet, que contém um objeto Sheet com elementos SheetProperties. Há também uma matriz de elementos GridData que contêm informações sobre a PivotTable. As informações da tabela dinâmica estão contidas no recurso CellData da página na célula em que a tabela está ancorada (ou seja, no canto superior esquerdo da tabela). Se um campo de resposta estiver definido como o valor padrão, ele será omitido da resposta.

Neste exemplo, a primeira página (SOURCE_SHEET_ID) tem os dados brutos de origem da tabela, enquanto a segunda (SHEET_ID) tem a tabela dinâmica, ancorada em B3. As chaves vazias indicam páginas ou células que não contêm dados da tabela dinâmica. Para referência, essa solicitação também retorna os IDs das páginas.

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