Tableaux croisés dynamiques

L'API Google Sheets vous permet de créer et de mettre à jour des tableaux croisés dynamiques dans des feuilles de calcul. Les exemples de cette page illustrent comment vous pouvez obtenir certains tableaux croisés dynamiques courants avec l'API Sheets.

Ces exemples sont présentés sous la forme de requêtes HTTP et neutres. Pour découvrir comment implémenter une mise à jour par lot dans différents langages à l'aide de les bibliothèques clientes de l'API Google, consultez la section Mettre à jour feuilles de calcul.

Dans ces exemples, les espaces réservés SPREADSHEET_ID et SHEET_ID indique où vous devez fournir ces identifiants. Vous pouvez accéder à la feuille de calcul ID dans l'URL de la feuille de calcul. Vous pouvez obtenir l'ID de la feuille de calcul à l'aide de l'élément spreadsheets.get. La sont spécifiées au format A1. Une exemple de plage est Feuille1!A1:D5.

De plus, l'espace réservé SOURCE_SHEET_ID indique votre feuille avec les données sources. Dans ces exemples, il s'agit du tableau répertorié sous Tableau croisé dynamique données sources de la table.

Données sources du tableau croisé dynamique

Pour ces exemples, supposons que la feuille de calcul utilisée dispose de la source suivante "ventes" dans sa première feuille ("Feuille1"). Les chaînes de la première ligne sont des étiquettes pour les colonnes individuelles. Pour voir des exemples de lecture sur d'autres feuilles de votre feuille de calcul, consultez la section Notation A1.

A B C D E F G
1 Catégorie de l'élément Numéro de modèle Coût Quantité Région Commercial Date d'expédition
2 Roue W-24 20,50 $ 4 Ouest Beth 01/03/2016
3 Porte J-01X 15 $ 2 Sud Amir 15/03/2016
4 Moteur ENG-0134 100 $ 1 Nord Carmen 20/03/2016
5 Cadre FR-0B1 34,00 $ 8 Est Anna 12/03/2016
6 Panneau P-034 6 $ 4 Nord Devyn 02/04/2016
7 Panneau P-052 11,50 $ 7 Est Erik 16/05/2016
8 Roue W-24 20,50 $ 11 Sud Sheldon 30/04/2016
9 Moteur ENG-0161 330,00 $ 2 Nord Jessica 02/07/2016
10 Porte J-01Y 29,00 $ 6 Ouest Armando 13/03/2016
11 Cadre FR-0B1 34,00 $ 9 Sud Yuliana 27/02/2016
12 Panneau P-102 3 $ 15 Ouest Carmen 18/04/2016
13 Panneau P-105 8,25 $ 13 Ouest Jessica 20/06/2016
14 Moteur ENG-0211 283,00 $ 1 Nord Amir 21/06/2016
15 Porte J-01X 15 $ 2 Ouest Armando 03/07/2016
16 Cadre FR-0B1 34,00 $ 6 Sud Carmen 7/15/2016
17 Roue W-25 20 $ 8 Sud Anna 02/05/2016
18 Roue W-11 29,00 $ 13 Est Erik 19/05/2016
19 Porte D-05 17,70 $ 7 Ouest Beth 28/06/2016
20 Cadre FR-0B1 34,00 $ 8 Nord Sheldon 30/03/2016

Ajouter un tableau croisé dynamique

Les éléments suivants : spreadsheets.batchUpdate exemple de code montre comment utiliser UpdateCellsRequest pour créer un tableau croisé dynamique à partir des données sources, en l'ancréant sur la cellule A50 du feuille spécifiée par SHEET_ID.

La requête configure le tableau croisé dynamique avec les propriétés suivantes:

  • Un groupe de valeurs (Quantity) qui indique le nombre de ventes. Depuis il n'y a qu'un seul groupe de valeurs, les deux valueLayout sont équivalents.
  • Deux groupes de lignes (Catégorie de l'article et Numéro de modèle). Le premier tri valeur croissante de la quantité totale de "Ouest" Région : Par conséquent, "Moteur" (sans ventes à l'Ouest) apparaît au-dessus de "Porte". (avec 15 ventes à l'Ouest). La Numéro de modèle effectue un tri par ordre décroissant des ventes totales dans toutes par région, par exemple "W-24" (15 ventes) apparaît au-dessus de "W-25" (8 ventes). C'est fait en paramétrant le valueBucket sur {}.
  • Un groupe de colonnes (Region) qui trie le plus grand nombre de ventes par ordre croissant Là encore, valueBucket est défini sur {}. "Nord" génère le moins de ventes totales ; pour qu'elle apparaisse dans la première colonne Region.

Le protocole de requête est présenté ci-dessous.

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 demande crée un tableau croisé dynamique comme celui-ci:

Ajouter un résultat de recette pour le tableau croisé dynamique

Ajouter un tableau croisé dynamique avec des valeurs calculées

Les éléments suivants : spreadsheets.batchUpdate exemple de code montre comment utiliser UpdateCellsRequest pour créer un tableau croisé dynamique avec un groupe de valeurs de calcul à partir des données sources, l'ancrer dans la cellule A50 de la feuille spécifiée par SHEET_ID.

La requête configure le tableau croisé dynamique avec les propriétés suivantes:

  • Deux groupes de valeurs (Quantity et Total Price) Le premier indique le nombre de ventes. La seconde est une valeur calculée en fonction du produit le coût d'une pièce et le nombre total de ses ventes, à l'aide de la formule suivante: =Cost*SUM(Quantity)
  • Trois groupes de lignes : Item Category (Catégorie de l'article), Model Number (Numéro de modèle) et Cost (Coût).
  • Un groupe de colonnes (Region)
  • Les groupes de lignes et de colonnes sont triés par nom (plutôt que par quantité) dans chaque en classant le tableau par ordre alphabétique. Pour ce faire, omettez le valueBucket dans le champ PivotGroup
  • Pour simplifier l'apparence du tableau, la requête masque les sous-totaux pour tous les groupes de lignes et de colonnes, sauf les groupes de lignes et de colonnes principaux.
  • La requête définit valueLayout à VERTICAL pour améliorer l'apparence du tableau. valueLayout est seulement important s'il y a deux groupes de valeurs ou plus.

Le protocole de requête est présenté ci-dessous.

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 demande crée un tableau croisé dynamique comme celui-ci:

Ajouter le résultat de la recette pour regrouper des valeurs de tableaux croisés dynamiques

Supprimer un tableau croisé dynamique

Les éléments suivants : spreadsheets.batchUpdate exemple de code montre comment utiliser UpdateCellsRequest pour supprimer un tableau croisé dynamique (le cas échéant) ancré dans la cellule A50 de la feuille spécifié par SHEET_ID.

Un UpdateCellsRequest peut supprimer un tableau croisé dynamique en incluant "pivotTable" dans le paramètre fields, tout en omettant le champ pivotTable sur l'ancre. cellule.

Le protocole de requête est présenté ci-dessous.

POST https://sheets.googleapis.com/v4/spreadsheets/SPREADSHEET_ID:batchUpdate
{
  "requests": [
    {
      "updateCells": {
          "rows": [ 
            {
            "values": [
              {}
            ]
          }
        ],
        "start": {
          "sheetId": SHEET_ID,
          "rowIndex": 49,
          "columnIndex": 0
        },
        "fields": "pivotTable"
      }
    }
  ]
}

Modifier les colonnes et les lignes d'un tableau croisé dynamique

Les éléments suivants : spreadsheets.batchUpdate exemple de code montre comment utiliser UpdateCellsRequest pour modifier le tableau croisé dynamique créé dans Ajouter un tableau croisé dynamique.

Sous-ensembles du pivotTable dans Ressource CellData ne peut pas être modifié individuellement avec le paramètre fields. Pour apporter des modifications, l'intégralité du champ pivotTable doit être renseignée. En bref, modifier un tableau croisé dynamique nécessite de le remplacer par un nouveau.

La demande apporte les modifications suivantes au tableau croisé dynamique d'origine:

  • Supprime le deuxième groupe de lignes du tableau croisé dynamique d'origine (Numéro de modèle).
  • Ajoute un groupe de colonnes (Salesperson). Les colonnes trient dans l’ordre décroissant par le nombre total de ventes de Panel. "Carmen" (15 ventes Panneau) semble à gauche de "Jessie" (13 ventes Panel).
  • Réduit la colonne pour chaque région, à l'exception de "Ouest", en masquant la Commercial pour cette région. Pour ce faire, définissez collapsed sur true dans valueMetadata pour cette colonne dans le groupe de colonnes Région.

Le protocole de requête est présenté ci-dessous.

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 demande crée un tableau croisé dynamique comme celui-ci:

Modifier le résultat de la recette du tableau croisé dynamique

Lire les données du tableau croisé dynamique

Les éléments suivants : Exemple de code spreadsheets.get montre comment obtenir les données d'un tableau croisé dynamique à partir d'une feuille de calcul. Requête fields spécifie que seules les données du tableau croisé dynamique doivent être renvoyées (comme par opposition aux données de la valeur des cellules).

Le protocole de requête est présenté ci-dessous.

GET https://sheets.googleapis.com/v4/spreadsheets/SPREADSHEET_ID?fields=sheets(properties.sheetId,data.rowData.values.pivotTable)

La réponse se compose d'un Spreadsheet ressource, qui contient Objet Sheet avec SheetProperties éléments. Il y a aussi un tableau GridData contenant des informations PivotTable Les informations du tableau croisé dynamique sont contenues dans le Ressource CellData pour la cellule sur laquelle le tableau est ancré (c'est-à-dire, dans la partie supérieure gauche . Si un champ de réponse est défini sur la valeur par défaut, il est omis du champ de réponse.

Dans cet exemple, la première feuille (SOURCE_SHEET_ID) contient le tableau brut les données sources, tandis que la deuxième feuille (SHEET_ID) contient le tableau croisé dynamique, ancrée sur B3. Les accolades vides indiquent les feuilles ou les cellules qui n'ont pas contiennent des données de tableau croisé dynamique. Pour référence, cette requête renvoie également la feuille 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
      }
    }
  ],
}