A API Google Sheets permite que você crie e atualize tabelas dinâmicas dentro de planilhas. Os exemplos nesta página demonstram 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 termos de linguagem. Para saber como implementar uma atualização em lote em diferentes idiomas usando as bibliotecas do cliente da Google API, consulte Atualizar planilhas.
Nesses exemplos, os marcadores de posição SPREADSHEET_ID e SHEET_ID
indicam onde você deve incluir esses IDs. Você pode encontrar o ID da planilha no URL dela. Você pode conferir 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 de posição SOURCE_SHEET_ID indica sua planilha
com os dados de origem. Nesses exemplos, esta é a tabela listada em Dados de origem da tabela dinâmica.
Dados de origem da tabela dinâmica
Para esses exemplos, pressuponha que a planilha usada tem os seguintes dados de origem "vendas" na primeira página ("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 | 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 | 2/7/2016 |
| 10 | Porta | D-01Y | US$ 29,00 | 6 | Oeste | Armando | 3/13/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 | 3/7/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 a função
UpdateCellsRequest
para criar uma tabela dinâmica com base nos dados de origem, ancorando-a na célula A50 da
página 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
valueLayoutpossíveis são equivalentes. - Dois grupos de linhas (Item Category e Model Number). O primeiro é classificado em ordem crescente com base no valor total de Quantity da Região "West". Portanto, "Engine" (sem vendas na região oeste) aparece acima de "Door" (com 15 vendas na região West). O grupo Model Number é classificado em ordem decrescente com base no total de vendas em todas as regiões. Portanto, "W-24" (15 vendas) aparece acima de "W-25" (8 vendas). Para isso,
defina o campo
valueBucketcomo{}. - Um grupo de colunas (Região) que é classificado em ordem crescente com o maior número de vendas.
Novamente,
valueBucketé definido como{}. "North" tem o menor total de vendas e, portanto, aparece como a primeira coluna Region.
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:
Adicionar uma tabela dinâmica com valores calculados
O exemplo de código
spreadsheets.batchUpdate
a seguir mostra como usar a função
UpdateCellsRequest
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.
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 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 de colunas são classificados por nome (não por Quantidade) em cada
grupo, colocando a tabela em ordem alfabética. Para isso, omita o campo
valueBucketdoPivotGroup. - Para simplificar a aparência da tabela, a solicitação oculta os subtotais para todos os grupos de linhas e colunas, exceto os principais.
- A solicitação define
valueLayoutcomoVERTICALpara melhorar a aparência da tabela.valueLayoutsó é 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:
Excluir uma tabela dinâmica
O exemplo de código
spreadsheets.batchUpdate
abaixo mostra como usar o método
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 e omitindo o campo pivotTable na célula
de âncora.
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 colunas e linhas de tabelas dinâmicas
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.
Subconjuntos do campo
pivotTable
no recurso
CellData
não podem ser alterados individualmente com o parâmetro fields. Para fazer edições, o
campo pivotTable inteiro precisa ser fornecido. Essencialmente, editar uma tabela dinâmica
exige que ela seja substituída por uma nova.
A solicitação faz as seguintes alterações na tabela dinâmica original:
- Remove o segundo grupo de linhas da tabela dinâmica original (Model Number).
- Adiciona um grupo de colunas (Salesperson). 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 de cada Região, exceto "West", ocultando o grupo Salesperson dessa região. Isso é feito ao definir
collapsedcomotruenovalueMetadatapara 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:
Ler dados da tabela dinâmica
O exemplo de código
spreadsheets.get abaixo
mostra como receber dados de uma tabela dinâmica de uma planilha. O parâmetro de consulta fields
especifica que apenas os dados da tabela dinâmica devem ser retornados (em
vez de dados de valor de célula).
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 recurso
Spreadsheet, que contém um objeto
Sheet com elementos
SheetProperties. Há também uma matriz de elementos
GridData
com informações sobre o
PivotTable.
As informações da tabela dinâmica estão contidas no recurso
CellData da página
para a célula em que a tabela está ancorada (ou seja, o 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 brutos 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 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} } ], }