Tabelle pivot

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 $ 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 $ 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 valueLayout impostazioni sono 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 valueBucket su {}.
  • Un gruppo di colonne (Regione) ordinato in ordine crescente per la maggior parte delle vendite. Anche in questo caso, valueBucket è impostato su {}. "Nord" registra le vendite totali più basse 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:

Aggiungi risultato formula tabella pivot

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 valueBucket nel campo PivotGroup.
  • 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 valueLayout a VERTICAL per 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:

Aggiungi il risultato della formula del gruppo di valori pivot

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 collapsed su true nel valueMetadata: 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 come la seguente:

Modifica risultato formula tabella pivot

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 risposta.

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
      }
    }
  ],
}