L'API Fogli Google ti consente di creare e aggiornare tabelle pivot all'interno dei fogli di lavoro. Gli esempi in questa pagina illustrano come eseguire alcune operazioni comuni con le tabelle pivot con l'API Fogli.
Questi esempi sono presentati sotto forma di richieste HTTP per essere indipendenti dalla lingua. Per scoprire come implementare un aggiornamento collettivo in lingue diverse 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 fornire questi ID. Puoi trovare l'ID del foglio di lavoro nell'URL del foglio di lavoro. Puoi recuperare
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, si tratta della tabella elencata in Dati di origine 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 le etichette per le singole colonne. Per visualizzare esempi di come leggere da altri fogli nel foglio di lavoro, consulta la 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 | 1/3/2016 |
| 3 | Porta | D-01X | 15 $ | 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 | Hannah | 3/12/2016 |
| 6 | Riquadro | P-034 | 6,00 $ | 4 | Nord | Devyn | 2/4/2016 |
| 7 | Riquadro | P-052 | 11,50 $ | 7 | Orientale | Erik | 16/05/2016 |
| 8 | Ruota | W-24 | 20,50 $ | 11 | Meridionale | Sheldon | 30/4/2016 |
| 9 | Motore | ENG-0161 | 330,00 $ | 2 | Nord | Jessie | 2/7/2016 |
| 10 | Porta | D-01Y | 29,00 $ | 6 | Occidentale | Armando | 13/03/2016 |
| 11 | Cornice | FR-0B1 | 34,00 $ | 9 | Meridionale | Yuliana | 27/2/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 $ | 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 | Hannah | 2/5/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 la funzione
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
valueLayoutsono equivalenti. - Due gruppi di righe (Categoria articolo e Numero modello). La prima riga ordina in ordine crescente il valore della Quantità totale della Regione "Ovest". Pertanto,
"Motore" (senza vendite in West) viene visualizzato sopra "Porta" (con 15 vendite in West). Il gruppo Numero modello viene ordinato in ordine decrescente delle vendite totali in tutte le regioni, pertanto "W-24" (15 vendite) viene visualizzato sopra "W-25" (8 vendite). Per farlo, imposta il campo
valueBucketsu{}. - Un gruppo di colonne (Regione) che ordina in ordine crescente la maggior parte delle vendite.
Anche in questo caso,
valueBucketè impostato su{}. "Nord" ha le vendite totali più basse, pertanto viene visualizzata 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 questa:
Aggiungere una tabella pivot con valori calcolati
Il seguente
spreadsheets.batchUpdate
esempio di codice mostra come utilizzare la funzione
UpdateCellsRequest
per creare una tabella pivot con un gruppo di valori calcolati dai dati di origine,
ancorandone la 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 del 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, alfabetizzando la tabella. Per farlo, ometti il campo
valueBucketdalPivotGroup. - 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 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 come questa:
Eliminare una tabella pivot
L'esempio di codice seguente
spreadsheets.batchUpdate
mostra come utilizzare la funzione
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 al contempo 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"
}
}
]
}
Modificare le colonne e le righe della tabella pivot
Il seguente
spreadsheets.batchUpdate
esempio di codice mostra come utilizzare la funzione
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 fornire l'intero campo pivotTable. In sostanza, per modificare una tabella pivot occorre sostituirla con una nuova.
La richiesta apporta le seguenti modifiche alla tabella pivot originale:
- Rimuove il secondo gruppo di righe dalla tabella pivot originale (Model Number).
- Aggiunge un gruppo di colonne (Venditore). Le colonne sono ordinate in ordine decrescente in base al numero totale di vendite di pannelli. "Carmen" (15 vendite del panel) viene visualizzato a sinistra di "Jessie" (13 vendite del panel).
- Comprimi la colonna per ogni Regione, ad eccezione di "Ovest", nascondendo il gruppo Venditore per quella regione. Per farlo, imposta
collapsedsutrueinvalueMetadataper la 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 questa:
Leggere i dati della tabella pivot
Il seguente
spreadsheets.get esempio di codice
mostra come recuperare i dati della tabella pivot da un foglio di lavoro. Il parametro query fields specifica che devono essere restituiti solo i dati della tabella pivot (diversamente dai dati dei valori delle celle).
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 è costituita da una risorsa
Spreadsheet, che contiene un oggetto
Sheet con elementi
SheetProperties. È presente anche un array di elementi
GridData
contenente informazioni sul
PivotTable.
Le informazioni della tabella pivot sono contenute nella risorsa
CellData del foglio per la cella a cui è ancorata la tabella (ovvero l'angolo superiore
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 non elaborati dell'origine tabella, 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. Come riferimento, questa richiesta restituisce anche gli ID
delle schede.
{
"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
}
}
],
}