Com a API Google Sheets, você pode criar e atualizar tabelas dinâmicas em planilhas. Os exemplos nesta página ilustram como realizar algumas operações comuns de tabela dinâmica com a API Google Sheets.
Esses exemplos são apresentados na forma de solicitações HTTP para serem neutros em relação ao idioma. 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 de posição SPREADSHEET_ID e
SHEET_ID
indicam onde você forneceria esses IDs. Você pode encontrar o ID da planilha no URL dela.
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 é
Página1!A1:D5.
Além disso, o marcador de posição SOURCE_SHEET_ID indica
sua 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 estes exemplos, suponha que a planilha usada tenha os seguintes dados de origem "sales" na primeira página ("Sheet1"). As strings na primeira linha são rótulos para as colunas individuais. Para ver 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 | 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 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 (Quantidade) que indica o número de vendas. Como há apenas um grupo de valores, as duas configurações possíveis de
valueLayoutsão equivalentes. - Dois grupos de linhas (Categoria do item e Número do modelo). A primeira classificação é feita em valor crescente da quantidade total da região "Oeste". Portanto, "Engine" (sem vendas no Oeste) aparece acima de "Door" (com 15 vendas no Oeste). O grupo Número do modelo é classificado em ordem decrescente de vendas totais 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 classifica em ordem crescente de mais vendas.
Novamente,
valueBucketé definido como{}. "Norte" tem o menor total de vendas e, por isso, 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 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, 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 (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, 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 de todos os grupos, exceto os principais de linha e coluna.
- A solicitação define
valueLayoutcomoVERTICALpara melhorar a aparência da tabela.valueLayoutsó é 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:
Excluir uma tabela dinâmica
O exemplo de código
spreadsheets.batchUpdate
a seguir mostra como usar o
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 e omitindo 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.
Não é possível mudar individualmente os subconjuntos do campo pivotTable no recurso CellData com o parâmetro fields. Para fazer edições, todo o campo pivotTable precisa ser fornecido. Basicamente, para editar uma tabela dinâmica, é necessário substituí-la 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 do Painel. "Carmen" (15 vendas de painel) aparece à esquerda de "Jessie" (13 vendas de painel).
- Fecha a coluna de cada região, exceto "Oeste", ocultando o grupo Vendedor dessa região. Isso é feito ao definir
collapsedcomotruenovalueMetadatadessa coluna no grupo 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:
Ler dados de tabela dinâmica
O exemplo de código spreadsheets.get a seguir mostra como receber dados de tabela dinâmica de uma planilha. O parâmetro de consulta fields especifica que apenas os dados da tabela dinâmica devem ser retornados, e não os valores das células.
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
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 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 for definido como o valor padrão, ele será omitido da resposta.
Neste exemplo, a primeira planilha (SOURCE_SHEET_ID) tem os dados brutos da tabela de origem, enquanto a segunda (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 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} } ], }