L'API Google Sheets consente di creare e aggiornare le tabelle pivot all'interno dei fogli di lavoro. Gli esempi in questa pagina illustrano come eseguire alcune operazioni comuni sulle tabelle pivot con l'API Sheets.
Questi esempi vengono presentati sotto forma di richieste HTTP per essere indipendenti dalla lingua. Per scoprire come implementare un aggiornamento batch in diverse lingue utilizzando le librerie client delle API di Google, consulta Aggiornare i fogli di lavoro.
In questi esempi, i segnaposto SPREADSHEET_ID e
SHEET_ID
indicano dove devi fornire questi ID. Puoi trovare l'ID
del foglio di lavoro nell'URL del foglio di lavoro.
Puoi ottenere l'ID del foglio utilizzando
il
spreadsheets.get
metodo. Gli intervalli vengono specificati utilizzando la notazione A1. Un intervallo di esempio è Sheet1!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 Dati di origine della tabella pivot.
Dati di origine della tabella pivot
Per questi esempi, supponiamo che il foglio di lavoro utilizzato contenga i seguenti dati di origine "vendite" nel primo foglio ("Sheet1"). Le stringhe nella prima riga sono etichette per le singole colonne. Per visualizzare esempi di come leggere da altri fogli del foglio di lavoro, consulta Notazione A1.
| A | B | C | D | E | F | G | |
| 1 | Categoria elemento | Numero di modello | Costo | Quantità | Regione | Commerciale | Data di spedizione |
| 2 | Ruota | W-24 | 20,50 $ | 4 | Occidentale | Beth | 01/03/2016 |
| 3 | Porta | D-01X | 15,00 $ | 2 | Meridionale | Amir | 15/03/2016 |
| 4 | Motore | ENG-0134 | 100,00 $ | 1 | Nord | Carmen | 20/03/2016 |
| 5 | Cornice | FR-0B1 | 34,00 $ | 8 | Orientale | Hannah | 12/03/2016 |
| 6 | Riquadro | P-034 | 6,00 $ | 4 | Nord | Devyn | 02/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 | Jessie | 02/07/2016 |
| 10 | Porta | D-01Y | 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 | Jessie | 20/06/2016 |
| 14 | Motore | ENG-0211 | 283,00 $ | 1 | Nord | Amir | 21/06/2016 |
| 15 | Porta | D-01X | 15,00 $ | 2 | Occidentale | Armando | 03/07/2016 |
| 16 | Cornice | FR-0B1 | 34,00 $ | 6 | Meridionale | Carmen | 15/07/2016 |
| 17 | Ruota | W-25 | 20,00 $ | 8 | Meridionale | Hannah | 02/05/2016 |
| 18 | Ruota | W-11 | 29,00 $ | 13 | Orientale | Erik | 19/05/2016 |
| 19 | Porta | D-05 | 17,70 $ | 7 | Occidentale | Beth | 28/06/2016 |
| 20 | Cornice | FR-0B1 | 34,00 $ | 8 | Nord | Sheldon | 30/03/2016 |
Aggiungere una tabella pivot
Il seguente
spreadsheets.batchUpdate
esempio di codice mostra come utilizzare
UpdateCellsRequest
per creare una tabella pivot dai dati di origine, ancorandola alla cella A50 del
foglio 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. Poiché
esiste un solo gruppo di valori, le due possibili
valueLayoutimpostazioni sono equivalenti. - Due gruppi di righe (Categoria articolo e Numero di modello). Il primo ordina in base al valore crescente della Quantità totale della Regione "Occidentale". Di conseguenza, "Motore" (senza vendite occidentali) viene visualizzato sopra "Porta" (con 15 vendite occidentali). Il gruppo Numero di modello ordina in ordine decrescente in base alle vendite totali in tutte le regioni, quindi "W-24" (15 vendite) viene visualizzato sopra "W-25" (8 vendite). Questa operazione viene eseguita
impostando il
valueBucketcampo su{}. - Un gruppo di colonne (Regione) che ordina in ordine crescente in base al numero di vendite.
Anche in questo caso,
valueBucketè impostato su{}. "Nord" ha il minor numero di vendite totali, quindi viene visualizzata come prima colonna Regione.
Di seguito è riportato il protocollo di richiesta.
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 simile alla seguente:
Aggiungere una tabella pivot con valori calcolati
Il seguente
spreadsheets.batchUpdate
esempio di codice mostra come utilizzare
UpdateCellsRequest
per creare una tabella pivot con un gruppo di valori calcolati dai dati di origine,
ancorandola 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 il numero di vendite. Il secondo è un valore calcolato basato sul prodotto del costo di una
parte e del numero totale di vendite, utilizzando la seguente formula:
=Cost*SUM(Quantity). - Tre gruppi di righe (Categoria articolo, Numero di modello e Costo).
- Un gruppo di colonne (Regione).
- I gruppi di righe e colonne vengono ordinati per nome (anziché per Quantità) in ogni gruppo, alfabetizzando la tabella. Questa operazione viene eseguita omettendo il
valueBucketcampo dalPivotGroup.- Per semplificare l'aspetto della tabella, la richiesta nasconde i subtotali per tutti i gruppi di righe e colonne principali.
- La richiesta imposta
valueLayoutsuVERTICALper migliorare l'aspetto della tabella.valueLayoutè importante solo se sono presenti due o più gruppi di valori.
Di seguito è riportato il protocollo di richiesta.
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 simile alla seguente:
Eliminare una tabella pivot
Il seguente
spreadsheets.batchUpdate
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 parametro fields, omettendo anche il campo pivotTable nella cella di ancoraggio.
Di seguito è riportato il protocollo di richiesta.
POST https://sheets.googleapis.com/v4/spreadsheets/SPREADSHEET_ID:batchUpdate{
"requests": [
{
"updateCells": {
"rows": [
{
"values": [
{}
]
}
],
"start": {
"sheetId": SHEET_ID,
"rowIndex": 49,
"columnIndex": 0
},
"fields": "pivotTable"
}
}
]
}Modificare le colonne e le righe della tabella pivot
Il seguente
spreadsheets.batchUpdate
esempio di codice mostra come utilizzare
UpdateCellsRequest
per modificare la tabella pivot creata in Aggiungere una tabella pivot.
I sottoinsiemi del
pivotTable
campo nella risorsa
CellData
non possono essere modificati singolarmente con il parametro fields. Per apportare modifiche, è necessario fornire l'intero campo pivotTable. In sostanza, la modifica di una tabella pivot richiede la sostituzione con una nuova.
La richiesta apporta le seguenti modifiche alla tabella pivot originale:
- Rimuove il secondo gruppo di righe dalla tabella pivot originale (Numero di modello).
- Aggiunge un gruppo di colonne (Commerciale). Le colonne vengono ordinate in ordine decrescente in base al numero totale di vendite di Riquadro. "Carmen" (15 vendite di Riquadro) viene visualizzato a sinistra di "Jessica" (13 vendite di Riquadro).
- Comprime la colonna per ogni Regione, ad eccezione di "Occidentale", nascondendo il
Commerciale gruppo per quella regione. Questa operazione viene eseguita impostando
collapsedsutruein thevalueMetadataper la colonna nel gruppo di colonne Regione.
Di seguito è riportato il protocollo di richiesta.
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 simile alla seguente:
Leggere i dati della tabella pivot
Il seguente
spreadsheets.get esempio di codice
mostra come ottenere i dati della tabella pivot da un foglio di lavoro. Il parametro di query fields specifica che devono essere restituiti solo i dati della tabella pivot (anziché i dati dei valori delle celle).
Di seguito è riportato il protocollo di richiesta.
GET https://sheets.googleapis.com/v4/spreadsheets/SPREADSHEET_ID?fields=sheets(properties.sheetId,data.rowData.values.pivotTable)La risposta è costituita da una
Spreadsheet
risorsa, che contiene un
Sheet oggetto con
SheetProperties
elementi. È presente anche un array di
GridData
elementi contenenti informazioni su
PivotTable.
Le informazioni sulla tabella pivot sono contenute nella risorsa
CellData
del foglio per la cella su cui è ancorata la tabella (ovvero l'angolo in alto a sinistra
della tabella). Se un campo di risposta è impostato sul valore predefinito, viene omesso dalla risposta.
In questo esempio, il primo foglio (SOURCE_SHEET_ID) contiene i dati di origine della tabella non elaborati, mentre il secondo foglio (SHEET_ID) contiene la tabella pivot,
ancorata a B3. Le parentesi graffe vuote indicano fogli o celle che non contengono dati della tabella pivot. Per riferimento, questa richiesta restituisce anche gli ID dei fogli.
{ "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} } ], }