Tabelas dinâmicas

A API Google Sheets permite criar e atualizar tabelas dinâmicas em planilhas. Os exemplos nesta página ilustram como realizar algumas operações comuns de tabelas dinâmicas 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 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 dela. É possível receber o ID da planilha usando o spreadsheets.get método. Os intervalos são especificados usando A1 notação. Um exemplo de intervalo é Sheet1!A1:D5.

Além disso, o marcador SOURCE_SHEET_ID indica a planilha com os dados de origem. Nesses exemplos, essa é 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 "vendas" na primeira planilha ("Sheet1"). As strings na primeira linha são rótulos para as colunas individuais. Para conferir exemplos de como ler de outras planilhas, 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 Wheel W-24 US$ 20,50 4 Oeste Beth 01/03/2016
3 Porta D-01X US$ 15,00 2 Sul Amir 15/03/2016
4 Mecanismo ENG-0134 US$ 100,00 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 Jessie 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 Jessie 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 Beth 28/06/2016
20 Quadro FR-0B1 US$ 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 o UpdateCellsRequest para criar uma tabela dinâmica com os 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 (Quantidade) que indica o número de vendas. Como há apenas um grupo de valores, as duas configurações possíveis são equivalentes.valueLayout
  • Dois grupos de linhas (Categoria do item e Número do modelo). O primeiro é classificado em valor crescente da Quantidade total da Região "Oeste". Portanto, "Mecanismo" (sem vendas no Oeste) aparece acima de "Porta" (com 15 vendas no Oeste). O grupo Número do modelo é classificado em ordem decrescente de vendas totais em todas as regiões, então "W-24" (15 vendas) aparece acima de "W-25" (8 vendas). Isso é feito definindo o valueBucket campo como {}.
  • Um grupo de colunas (Região) que é classificado em ordem crescente de mais vendas. Novamente, valueBucket está definido como {}. "Norte" tem o menor número de vendas totais e, portanto, aparece como a primeira coluna Região.

O protocolo de solicitação é mostrado 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:

Adicionar o resultado da receita de tabela dinâmica

Adicionar uma tabela dinâmica com valores calculados

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

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

  • Dois grupos de valores (Quantidade e Preço total). O primeiro indica o número de vendas. O segundo é um valor calculado com base no produto do custo de uma peça e no número total de vendas, usando esta fórmula: =Cost*SUM(Quantity).
  • Três grupos de linhas (Categoria do item, Número do modelo e Custo).
  • Um grupo de colunas (Região).
  • Os grupos de linhas e colunas são classificados por nome (em vez de Quantidade) em cada grupo, alfabetizando a tabela. Isso é feito omitindo o valueBucket campo do PivotGroup.
    • Para simplificar a aparência da tabela, a solicitação oculta os subtotais de todos 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.

O protocolo de solicitação é mostrado 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 da receita do grupo de valores de pivô

Excluir uma tabela dinâmica

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

Um UpdateCellsRequest pode remover uma tabela dinâmica incluindo "pivotTable" no parâmetro fields, ao mesmo tempo em que omite o campo pivotTable na célula de ancoragem.

O protocolo de solicitação é mostrado 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 colunas e linhas da tabela dinâmica

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

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

A solicitação faz as seguintes mudanças na tabela dinâmica original:

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

O protocolo de solicitação é mostrado 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 da receita de tabela dinâmica

Ler dados da tabela dinâmica

O exemplo de código a seguir spreadsheets.get mostra como receber dados de tabelas dinâmicas de uma planilha. O parâmetro de consulta fields especifica que apenas os dados da tabela dinâmica precisam ser retornados (em vez dos dados de valor da célula).

O protocolo de solicitação é mostrado abaixo.

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

A resposta consiste em um Spreadsheet recurso, que contém um Sheet objeto com SheetProperties elementos. Há também uma matriz de GridData elementos que contém informações sobre o PivotTable. As informações da tabela dinâmica estão contidas no recurso CellData da planilha para a célula em que a tabela está ancorada (ou seja, o 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 planilha (SOURCE_SHEET_ID) tem os dados de origem da tabela bruta, enquanto a segunda planilha (SHEET_ID) tem a tabela dinâmica, ancorada em B3. As chaves vazias indicam planilhas ou células que não contêm dados de tabelas dinâmicas. Para referência, essa solicitação também retorna os IDs das planilhas.

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