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 delle tabelle pivot con l'API Fogli.
Questi esempi sono presentati sotto forma di richieste HTTP per essere indipendenti dal linguaggio. Per scoprire come implementare un aggiornamento batch in lingue diverse, utilizzando le librerie client delle API di Google, consulta la pagina relativa all'aggiornamento dei fogli di lavoro.
In questi esempi, i segnaposto SPREADSHEET_ID e SHEET_ID
indicano dove 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
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, 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 abbia i seguenti dati di origine "vendite" nel primo foglio ("Foglio1"). Le stringhe nella prima riga sono etichette delle singole colonne. Per visualizzare esempi di come leggere da altri fogli nel tuo foglio 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 | Engine | ENG-0134 | € 100 | 1 | Nord | Carmen | 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 | Engine | ENG-0161 | 330,00 $ | 2 | Nord | Jessica | 2/07/2016 |
| 10 | Porta | G-01A | 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 | Carmen | 18/04/2016 |
| 13 | Riquadro | P-105 | 8,25 $ | 13 | Occidentale | Jessica | 20/06/2016 |
| 14 | Engine | 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 | 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 | Frame | FR-0B1 | 34,00 $ | 8 | Nord | Sheldon | 30/03/2016 |
Aggiungere una tabella pivot
Il seguente esempio di codice spreadsheets.batchUpdate 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 impostazioni di
valueLayoutsono equivalenti. - Due gruppi di righe (Categoria articolo e Numero modello). Il primo ordina in valore crescente della Quantità totale della regione "Ovest". Di conseguenza,
"Motore" (senza vendite Occidentali) viene visualizzato sopra "Porta" (con 15 vendite Occidentali). Il gruppo Numero di modello ordina in ordine decrescente le vendite totali in tutte le regioni, quindi "W-24" (15 vendite) compare sopra "W-25" (8 vendite). Per farlo, imposta il campo
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 le vendite totali minime e appare 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 come la 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 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 in base al prodotto del costo di un componente e al relativo numero totale di vendite, utilizzando la 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, mettendo la tabella in ordine alfabetico. Per farlo, ometti il campo
valueBucketdaPivotGroup. - Per semplificare l'aspetto della tabella, la richiesta nasconde i subtotali per tutti i gruppi di righe e colonne tranne quelli principali.
- La richiesta imposta
valueLayoutsuVERTICALper un aspetto migliore della tabella.valueLayoutè importante solo se sono presenti 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
Il seguente esempio di codice 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, 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 delle tabelle pivot
Il seguente esempio di codice per 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 specificare
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 Riquadro. "Carmen" (15 vendite panel) appare a sinistra di "Jessie" (13 vendite panel).
- Comprime la colonna per ogni regione, ad eccezione di "Ovest", nascondendo il gruppo Commerciale per quella regione. Per farlo, imposta
collapsedsutruenellavalueMetadatadella colonna in questione 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
Il seguente esempio di codice 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 (al contrario dei dati con 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 che contengono informazioni su PivotTable.
Le informazioni della tabella pivot sono contenute all'interno della 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 i fogli o le celle che non
contengono dati di 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
}
}
],
}