A API Google Sheets permite que você crie e atualize tabelas dinâmicas dentro de planilhas. Os exemplos nesta página ilustram como você pode obter algumas com a API Sheets.
Esses exemplos são apresentados na forma de solicitações HTTP para serem neutras. Para saber como implementar uma atualização em lote em diferentes idiomas usando bibliotecas de clientes da API do Google, consulte Atualizar planilhas.
Nestes exemplos, os marcadores de posição SPREADSHEET_ID e SHEET_ID
indica onde você deve fornecer esses IDs. Você pode encontrar a planilha
ID no URL da planilha. Você pode receber
o ID da planilha usando o
spreadsheets.get. A
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 que sua página
com os dados de origem. Nestes exemplos, esta é a tabela listada em Tabela dinâmica
dados de origem da tabela.
Dados de origem da tabela dinâmica
Para esses exemplos, suponha que a planilha usada tenha a seguinte fonte "vendas" dados em sua primeira planilha ("Sheet1"). As strings na primeira linha são rótulos nas colunas individuais. Para ver exemplos de como ler dados de outros páginas na sua planilha, consulte a 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 | Bete | 01/03/2016 |
| 3 | Porta | D-01X | 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 | Wheel | 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 | 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 | Bete | 28/06/2016 |
| 20 | Quadro | FR-0B1 | US$ 34,00 | 8 | Norte | Sheldon | 30/03/2016 |
Adicionar uma tabela dinâmica
O seguinte
spreadsheets.batchUpdate
um exemplo de código mostra como usar a
UpdateCellsRequest
para criar uma tabela dinâmica a partir 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:
- um grupo de valores (Quantity) que indica o número de vendas. Como
há apenas um grupo de valores, os dois possíveis
valueLayoutsão equivalentes. - Dois grupos de linhas (Item Category e Model Number). A primeira classifica
valor crescente do total de Quantidade em "Oeste" Região. Portanto,
"Motor" (sem vendas na região oeste) aparece acima de "Porta" (com 15 vendas na região oeste). A
O grupo Número do modelo classifica em ordem decrescente do total de vendas em todas
ou seja, "W-24" (15 vendas) aparece acima de "W-25" (8 vendas). Pronto
definindo o
valueBucketcomo{}. - Um grupo de colunas (Região) que é classificado em ordem crescente com o maior número de vendas.
Novamente,
valueBucketé definido como{}. "Norte" tem o menor total de vendas e para que apareça como a primeira coluna 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"
}
]
}
},
{
"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 seguinte
spreadsheets.batchUpdate
um exemplo de código mostra como usar a
UpdateCellsRequest
para criar uma tabela dinâmica com um grupo de valores calculados a partir 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 (Quantity e Total Price). O primeiro indica
número de vendas. O segundo é um valor calculado com base no produto de um
o 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 (Região).
- Os grupos de linhas e colunas são classificados por nome (e não por Quantidade) em cada
grupo, colocando a tabela em ordem alfabética. Isso é feito com a omissão da
valueBucketdaPivotGroup - Para simplificar a aparência da tabela, a solicitação oculta subtotais para todos os grupos, exceto para as linhas e colunas principais.
- A solicitação define
valueLayout. paraVERTICALpara melhorar a aparência da tabela.valueLayouté apenas 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 seguinte
spreadsheets.batchUpdate
um exemplo de código mostra como usar a
UpdateCellsRequest
para excluir uma tabela dinâmica (se houver) ancorada na célula A50 da página
especificado por SHEET_ID.
Um UpdateCellsRequest pode remover uma tabela dinâmica incluindo "pivotTable" no
o parâmetro fields, além de omitir o campo pivotTable na âncora
célula
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 linhas e colunas da tabela dinâmica
O seguinte
spreadsheets.batchUpdate
um exemplo de código mostra como usar a
UpdateCellsRequest
para editar a tabela dinâmica criada em Adicionar uma tabela dinâmica.
Os subconjuntos do
pivotTable
na
Recurso CellData
não pode ser alterado individualmente com o parâmetro fields. Para fazer edições, o
É necessário informar todo o campo pivotTable. Basicamente, editar uma tabela dinâmica
requer a substituição por um novo.
A solicitação faz as seguintes alterações na tabela dinâmica original:
- Remove o grupo da segunda linha da tabela dinâmica original (Número do modelo).
- Adiciona um grupo de colunas (Vendedor). As colunas são classificadas em ordem decrescente por o número total de vendas do Painel. "Carmen" (15 vendas Painel) parece à esquerda de "Jessie" (13 vendas de Painel).
- Recolhe a coluna para cada Região, exceto "Oeste", ocultando o
Grupo de vendedores para essa região. Isso é feito ao definir
collapsedcomotruenavalueMetadatapara 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 seguinte
Exemplo de código do spreadsheets.get
mostra como obter dados da tabela dinâmica de uma planilha. A consulta fields
especifica que apenas os dados da tabela dinâmica devem ser retornados (como
em oposição aos dados dos valores das células).
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
Spreadsheet
que contém um
Objeto Sheet com
SheetProperties
os elementos. Há também uma matriz
GridData
que contêm informações sobre o
PivotTable
As informações da tabela dinâmica estão contidas nos arquivos
Recurso CellData
para a célula em que a tabela está ancorada (ou seja, o canto superior esquerdo
direito). Se um campo de resposta for definido com o valor padrão, ele será omitido do
resposta.
Neste exemplo, a primeira página (SOURCE_SHEET_ID) tem a tabela bruta
dados de origem, enquanto a segunda página (SHEET_ID) tem a tabela dinâmica,
com base em B3. As chaves vazias indicam páginas ou células que não
contêm dados de tabela dinâmica. Para referência, esta solicitação também retorna a planilha
do Google Ads.
{
"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
}
}
],
}