L'API Fogli Google consente di creare e aggiornare tabelle pivot all'interno dei fogli di lavoro. Gli esempi in questa pagina mostrano come eseguire alcune operazioni comuni per le tabelle pivot con l'API Fogli.
Questi esempi sono presentati sotto forma di richieste HTTP per essere neutrali dal linguaggio. Per informazioni su come implementare un aggiornamento batch in diversi linguaggi 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 dovresti fornire questi ID. Puoi trovare l'ID del foglio di lavoro nell'URL del foglio di lavoro. Puoi ottenere
l'ID foglio utilizzando il
metodo
spreadsheets.get
. Gli intervalli vengono specificati utilizzando la notazione A1. Un
intervallo di esempio è Foglio1!A1:D5.
Inoltre, il segnaposto SOURCE_SHEET_ID
indica il foglio con i dati di origine. In questi esempi, questa è la 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 ("Foglio1"). Le stringhe nella prima riga sono etichette per le singole colonne. Per visualizzare esempi di lettura da altri fogli nel foglio di lavoro, vedi la 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 | Beth | 1/03/2016 |
3 | Porta | D-01X | $15,00 | 2 | Meridionale | Amir | 15/03/2016 |
4 | Motore | ENG-0134 | € 100 | 1 | Nord | Martina | 20/03/2016 |
5 | Frame | 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 | D-01Y | 29,00 $ | 6 | Occidentale | Armando | 13/03/2016 |
11 | Frame | FR-0B1 | 34,00 $ | 9 | Meridionale | Yuliana | 27/02/2016 |
12 | Riquadro | P-102 | 3 $ | 15 | Occidentale | Martina | 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 | Frame | FR-0B1 | 34,00 $ | 6 | Meridionale | Martina | 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 | Beth | 28/06/2016 |
20 | Frame | FR-0B1 | 34,00 $ | 8 | Nord | Sheldon | 30/03/2016 |
Aggiungi una tabella pivot
Il seguente esempio di codice di spreadsheets.batchUpdate
mostra come utilizzare UpdateCellsRequest
per creare una tabella pivot a partire 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 impostazioni di
valueLayout
sono equivalenti. - Due gruppi di righe (Item Category e Model Number). Il primo ordina
in valore crescente della Quantità totale nella regione "Ovest". Pertanto,
"Motore" (senza vendite Occidentali) compare sopra "Porta" (con 15 vendite Occidentali). Il gruppo Numero modello ordina in ordine decrescente le vendite totali in tutte le regioni, pertanto "W-24" (15 vendite) compare sopra "W-25" (8 vendite). Per farlo, imposta il campo
valueBucket
su{}
. - Un gruppo di colonne (Regione) che ordina la maggior parte delle vendite in ordine crescente.
Anche in questo caso, il valore di
valueBucket
è impostato su{}
. "Nord" presenta il numero minimo di vendite totali e viene quindi visualizzato come prima colonna 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" } ] } }, { "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
esempio di codice di spreadsheets.batchUpdate
mostra come utilizzare
UpdateCellsRequest
per creare una tabella pivot con un gruppo di valori calcolati a partire dai dati di origine,
ancorandoli 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). La prima indica il numero
di vendite. Il secondo è un valore calcolato basato sul prodotto del costo di un articolo e sul 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 vengono ordinati per nome (anziché per Quantità) in ogni gruppo, in ordine alfabetico. Per farlo, ometti il campo
valueBucket
daPivotGroup
. - Per semplificare l'aspetto della tabella, la richiesta nasconde i subtotali per tutti i gruppi di righe e colonne principali.
- La richiesta imposta
valueLayout
suVERTICAL
per migliorare l'aspetto della tabella.valueLayout
è importante solo se sono presenti due 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 simile alla seguente:
Eliminare una tabella pivot
Il seguente esempio di codice di spreadsheets.batchUpdate
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
, ma omettendo anche il campo pivotTable
nella cella
di ancoraggio.
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 della tabella pivot
Il seguente
esempio di codice di spreadsheets.batchUpdate
mostra come utilizzare
UpdateCellsRequest
per modificare la tabella pivot creata in Aggiungere una tabella pivot.
I sottoinsiemi del campo pivotTable
nella risorsa CellData
non possono essere modificati singolarmente con il parametro fields
. Per apportare modifiche, è necessario compilare
l'intero campo pivotTable
. In pratica, per modificare una tabella pivot
è necessario sostituirla con una nuova.
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 in base al numero totale di vendite del riquadro. "Carmen" (15 vendite di panel) appare a sinistra di "Jessie" (13 vendite di Panel).
- Comprime la colonna per ogni regione, ad eccezione di "Ovest", nascondendo il gruppo Commerciale per quella regione. Per farlo, imposta
collapsed
sutrue
nelvalueMetadata
per 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 simile alla seguente:
Leggi i dati della tabella pivot
Il seguente esempio di codice di spreadsheets.get
mostra come recuperare i dati di una 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 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 una risorsa Spreadsheet
, che contiene un oggetto Sheet
con elementi SheetProperties
. È presente anche un array di elementi GridData
contenenti informazioni su PivotTable
.
Le informazioni sulla tabella pivot sono contenute nella risorsa CellData
del foglio per la cella a cui è ancorata la tabella, ovvero l'angolo superiore sinistro 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 elaborata, mentre il secondo foglio (SHEET_ID
) contiene la tabella pivot, ancorata a B3. Le parentesi graffe vuote indicano i fogli o le celle che non contengono i dati della tabella pivot. Come riferimento, questa richiesta restituisce anche gli ID foglio.
{ "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
} } ], }