L'API Fogli Google consente di creare e aggiornare tabelle pivot all'interno dei fogli di lavoro. Gli esempi in questa pagina illustrano come ottenere alcune tabelle pivot comuni operazioni con l'API Fogli.
Questi esempi sono presentati sotto forma di richieste HTTP per neutri. Per scoprire come implementare un aggiornamento batch in lingue diverse utilizzando le librerie client delle API di Google, consulta l'articolo Aggiornamento fogli di lavoro.
In questi esempi, i segnaposto SPREADSHEET_ID e SHEET_ID
indica dove fornire questi ID. Puoi trovare il foglio di lavoro
ID nell'URL del foglio di lavoro. Puoi ottenere
l'ID foglio utilizzando la proprietà
spreadsheets.get. La
vengono specificati utilizzando la notazione A1. Un
di esempio è Foglio1!A1:D5.
Inoltre, il segnaposto SOURCE_SHEET_ID indica il foglio
con i dati di origine. In questi esempi, si tratta della tabella elencata in Pivot
dati di origine della tabella.
Dati di origine della tabella pivot
Per questi esempi, supponiamo che il foglio di lavoro in uso abbia la seguente origine "vendite" nel primo foglio ("Foglio1"). Le stringhe nella prima riga sono le etichette per le singole colonne. Per visualizzare esempi di come leggere da altri fogli di lavoro, vedi notazione A1.
| A | B | C | D | E | F | G | |
| 1 | Categoria elemento | Numero modello | Costo | Quantità | Regione | Commerciale | Data di spedizione |
| 2 | Ruota | W-24 | 20,50 € | 4 | Occidentale | Bea | 1/03/2016 |
| 3 | Porta | D-01X | 15,00 € | 2 | Meridionale | Amir | 15/03/2016 |
| 4 | Motore | ENG-0134 | € 100 | 1 | Nord | Carmen | 20/03/2016 |
| 5 | Cornice | FR-0B1 | 34,00 $ | 8 | Orientale | Anna | 12/03/2016 |
| 6 | Riquadro | P-034 | 6,00 $ | 4 | Nord | Devyn | 2/04/2016 |
| 7 | Riquadro | P-052 | 11,50 $ | 7 | Orientale | Erik | 16/05/2016 |
| 8 | Ruota | W-24 | 20,50 € | 11 | Meridionale | Sheldon | 30/04/2016 |
| 9 | Motore | ENG-0161 | 330,00 $ | 2 | Nord | Jessica | 2/07/2016 |
| 10 | Porta | G-01A | 29,00 $ | 6 | Occidentale | Armando | 13/03/2016 |
| 11 | Cornice | FR-0B1 | 34,00 $ | 9 | Meridionale | Yuliana | 27/02/2016 |
| 12 | Riquadro | P-102 | $ 3,00 | 15 | Occidentale | Carmen | 18/04/2016 |
| 13 | Riquadro | P-105 | 8,25 $ | 13 | Occidentale | Jessica | 20/06/2016 |
| 14 | Motore | ENG-0211 | 283,00 $ | 1 | Nord | Amir | 21/06/2016 |
| 15 | Porta | D-01X | 15,00 € | 2 | Occidentale | Armando | 3/07/2016 |
| 16 | Cornice | FR-0B1 | 34,00 $ | 6 | Meridionale | Carmen | 15/07/2016 |
| 17 | Ruota | W-25 | 20,00 $ | 8 | Meridionale | Anna | 2/05/2016 |
| 18 | Ruota | W-11 | 29,00 $ | 13 | Orientale | Erik | 19/05/2016 |
| 19 | Porta | D-05 | 17,70 $ | 7 | Occidentale | Bea | 28/06/2016 |
| 20 | Cornice | FR-0B1 | 34,00 $ | 8 | Nord | Sheldon | 30/03/2016 |
Aggiungere una tabella pivot
Le seguenti
spreadsheets.batchUpdate
l'esempio di codice mostra come utilizzare
UpdateCellsRequest
per creare una tabella pivot dai dati di origine, ancorandola alla cella A50 del
specificato da SHEET_ID.
La richiesta configura la tabella pivot con le seguenti proprietà:
- Un gruppo di valori (Quantità) che indica il numero di vendite. Dal giorno
esiste un solo gruppo di valori, i 2 possibili
valueLayoutimpostazioni equivalenti. - Due gruppi di righe (Categoria articolo e Numero modello). Il primo ordina
valore crescente della Quantità totale da "Ovest" Regione. Pertanto,
"Motore" (senza vendite West) compare sopra "Porta" (con 15 vendite occidentali). La
Il gruppo Numero modello ordina in ordine decrescente le vendite totali di tutte le
regioni, quindi "W-24" (15 vendite) compare sopra "W-25" (8 vendite). Fatto
impostando il parametro
valueBucketsu{}. - Un gruppo di colonne (Regione) ordinato in ordine crescente per la maggior parte delle vendite.
Anche in questo caso,
valueBucketè impostato su{}. "Nord" ha meno vendite totali in modo che venga visualizzata come prima colonna Region.
Il protocollo di richiesta è mostrato di seguito.
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"
}
}
]
}
La richiesta crea una tabella pivot come la seguente:
Aggiungere una tabella pivot con valori calcolati
Le seguenti
spreadsheets.batchUpdate
l'esempio di codice mostra come utilizzare
UpdateCellsRequest
per creare una tabella pivot con un gruppo di valori calcolati dai dati di origine,
ancorandolo alla cella A50 del foglio specificato da SHEET_ID.
La richiesta configura la tabella pivot con le seguenti proprietà:
- Due gruppi di valori (Quantità e Prezzo totale). Il primo indica
di vendite. Il secondo è un valore calcolato in base al prodotto di
parte e il numero totale di vendite, utilizzando questa formula:
=Cost*SUM(Quantity). - Tre gruppi di righe (Categoria articolo, Numero modello e Costo).
- Un gruppo di colonne (regione).
- I gruppi di righe e colonne sono ordinati per nome (anziché per Quantità) in ogni
di mettere la tabella in ordine alfabetico. A questo scopo, ometti il parametro
valueBucketnel campoPivotGroup. - Per semplificare l'aspetto della tabella, la richiesta nasconde i subtotali per tutti i gruppi tranne le righe e le colonne principali.
- La richiesta imposta
valueLayoutaVERTICALper un aspetto migliore della tabella.valueLayoutè solo è importante se ci sono 2 o più gruppi di valori.
Il protocollo di richiesta è mostrato di seguito.
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"
}
}
]
}
La richiesta crea una tabella pivot come la seguente:
Eliminare una tabella pivot
Le seguenti
spreadsheets.batchUpdate
l'esempio di codice mostra come utilizzare
UpdateCellsRequest
per eliminare una tabella pivot (se presente) ancorata alla cella A50 del foglio
specificato da SHEET_ID.
Un UpdateCellsRequest può rimuovere una tabella pivot includendo "pivotTable" nel
Il parametro fields, omettendo anche il campo pivotTable nell'ancoraggio
cella.
Il protocollo di richiesta è mostrato di seguito.
POST https://sheets.googleapis.com/v4/spreadsheets/SPREADSHEET_ID:batchUpdate
{
"requests": [
{
"updateCells": {
"rows": [
{
"values": [
{}
]
}
],
"start": {
"sheetId": SHEET_ID,
"rowIndex": 49,
"columnIndex": 0
},
"fields": "pivotTable"
}
}
]
}
Modifica colonne e righe delle tabelle pivot
Le seguenti
spreadsheets.batchUpdate
l'esempio di codice mostra come utilizzare
UpdateCellsRequest
per modificare la tabella pivot creata in Aggiungi una tabella pivot.
Sottoinsiemi di
pivotTable
nel campo
CellData risorsa
non possono essere modificate singolarmente con il parametro fields. Per apportare modifiche,
è necessario specificare l'intero campo pivotTable. In pratica, la modifica di una tabella pivot
richiede la sua sostituzione con uno nuovo.
La richiesta apporta le seguenti modifiche alla tabella pivot originale:
- Rimuove il secondo gruppo di righe dalla tabella pivot originale (Numero modello).
- Aggiunge un gruppo di colonne (Commerciale). Le colonne vengono ordinate in ordine decrescente per il numero totale di vendite panel. "Carmen" (15 vendite Pannello) appare per a sinistra di "Jessie" (13 vendite del riquadro).
- Comprime la colonna per ogni Regione, ad eccezione di "Ovest", nascondendo
Gruppo Commerciale per quella regione. Per farlo, imposta
collapsedsutruenelvalueMetadataper quella colonna nel gruppo di colonne Regione.
Il protocollo di richiesta è mostrato di seguito.
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"
}
}
]
}
La richiesta crea una tabella pivot come la seguente:
Leggere i dati della tabella pivot
Le seguenti
Esempio di codice spreadsheets.get
mostra come recuperare i dati di una tabella pivot da un foglio di lavoro. La query fields
specifica che devono essere restituiti solo i dati della tabella pivot (come
al contrario dei dati del valore della cella).
Il protocollo di richiesta è mostrato di seguito.
GET https://sheets.googleapis.com/v4/spreadsheets/SPREADSHEET_ID?fields=sheets(properties.sheetId,data.rowData.values.pivotTable)
La risposta è composta da un
Spreadsheet
una risorsa che contiene un
Sheet con
SheetProperties
elementi. C'è anche un array
GridData
contenenti informazioni sugli
PivotTable.
Le informazioni della tabella pivot si trovano nella cartella
CellData risorsa
della cella su cui è ancorata la tabella (vale a dire, l'angolo in alto a sinistra
nell'angolo in alto a destra). Se un campo di risposta è impostato sul valore predefinito, viene omesso dalla
la risposta corretta.
In questo esempio, il primo foglio (SOURCE_SHEET_ID) contiene la tabella non elaborata
mentre il secondo foglio (SHEET_ID) contiene la tabella pivot,
ancorato su B3. Le parentesi graffe vuote indicano i fogli o le celle che non
che contengono dati di tabelle pivot. Come riferimento, questa richiesta restituisce anche il foglio
ID.
{
"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
}
}
],
}