Com a API Google Sheets, você pode criar e atualizar tabelas dinâmicas em planilhas. Os exemplos nesta página ilustram como é possível 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 a linguagem neutra. Para saber como implementar uma atualização em lote em linguagens diferentes usando as bibliotecas de cliente das APIs do Google, consulte Atualizar planilhas.
Nesses exemplos, os marcadores SPREADSHEET_ID e SHEET_ID
indicam onde você fornece esses IDs. Você pode encontrar o ID da planilha no URL da planilha. Consiga 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 | Beth | 01/03/2016 |
| 3 | Porta | Dado | US$ 15,00 | 2 | Sul | Amir | 15/03/2016 |
| 4 | Mecanismo | ENG-0134 | R$ 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 | Roda gigante | 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 | Dado | US$ 15,00 | 2 | Oeste | Armando | 03/07/2016 |
| 16 | Quadro | FR-0B1 | US$ 34,00 | 6 | Sul | Carmen | 15/07/2016 |
| 17 | Roda gigante | W-25 | US$ 20,00 | 8 | Sul | Hannah | 02/05/2016 |
| 18 | Roda gigante | 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
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 de
valueLayoutpossíveis são equivalentes. - Dois grupos de linhas (Item Category e Model Number). A primeira classifica em valor crescente do total de Quantity da Região "Oeste". Portanto, "Engine" (sem vendas na região West) aparece acima de "Door" (com 15 vendas na região West). O grupo Model Number é 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
valueBucketcomo{}. - Um grupo de colunas (Região) que classifica em ordem crescente de acordo com a maioria das vendas.
Novamente,
valueBucketestá definido como{}. "Norte" tem o menor total de vendas e, portanto, aparece como a primeira coluna Região.
O protocolo da 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 uma tabela dinâmica com valores calculados
O exemplo de código
spreadsheets.batchUpdate
a seguir mostra como usar o
UpdateCellsRequest
para criar uma tabela dinâmica com um grupo de valores calculados dos dados de origem,
fixando-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 (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 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. Para isso, omita o campo
valueBucketdePivotGroup. - Para simplificar a aparência da tabela, a solicitação oculta os subtotais para todos, exceto os grupos de linhas e colunas principais.
- A solicitação define
valueLayoutcomoVERTICALpara melhorar a aparência da tabela.valueLayoutsó será importante se houver dois ou mais grupos de valores.
O protocolo da 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:
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 houver) 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, além de omitir o campo pivotTable na célula
fixa.
O protocolo da 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 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, é necessário informar todo o campo pivotTable. Basicamente, editar 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 da segunda linha 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 de Panel. "Carmen" (15 vendas de Panel) aparece à esquerda de "Jessie" (13 vendas de Panel).
- Recolhe a coluna para cada Region, exceto "Oeste", ocultando o grupo Seller dessa região. Isso é feito ao definir
collapsedcomotrueemvalueMetadatadessa coluna no grupo de colunas Região.
O protocolo da 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:
Ler dados da tabela dinâmica
O exemplo de código spreadsheets.get a seguir mostra como receber os dados da tabela dinâmica 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 do valor da célula).
O protocolo da 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 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 planilha
para a célula em que a tabela está ancorada (ou seja, no canto superior esquerdo
da tabela). Se um campo de resposta for definido com o valor padrão, ele será omitido da resposta.
Neste exemplo, a primeira página (SOURCE_SHEET_ID) tem os dados de origem da tabela
brutos, enquanto a segunda página (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 de tabela dinâmica. Para referência, essa solicitação também retorna os IDs da
planilha.
{
"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
}
}
],
}