Tableaux croisés dynamiques

L'API Google Sheets vous permet de créer et de mettre à jour des tableaux croisés dynamiques au sein des feuilles de calcul. Les exemples de cette page montrent comment effectuer certaines opérations courantes de tableau croisé dynamique avec l'API Sheets.

Ces exemples sont présentés sous la forme de requêtes HTTP pour être neutres en ce qui concerne le langage. Pour découvrir comment implémenter une mise à jour groupée dans différents langages à l'aide des bibliothèques clientes de l'API Google, consultez Mettre à jour des feuilles de calcul.

Dans ces exemples, les espaces réservés SPREADSHEET_ID et SHEET_ID indiquent où vous devez fournir ces ID. L'ID de la feuille de calcul se trouve dans son URL. Vous pouvez obtenir l'ID de la feuille à l'aide de la méthode spreadsheets.get. Ces plages sont spécifiées au format A1. Exemple de plage : 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 de la table indiquée sous Données sources du tableau croisé dynamique.

Données sources du tableau croisé dynamique

Pour ces exemples, supposons que la feuille de calcul utilisée comporte les données sources "ventes" suivantes dans sa première feuille ("Sheet1"). Les chaînes de la première ligne sont des étiquettes pour les colonnes individuelles. Pour voir des exemples de lecture à partir d'autres feuilles de votre feuille de calcul, consultez 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 D-01X 15 $ 2 Sud Amir 15/03/2016
4 Moteur ENG-0134 100,00 € 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 D-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

L'exemple de code spreadsheets.batchUpdate suivant montre comment utiliser UpdateCellsRequest pour créer un tableau croisé dynamique à partir des données sources, en l'ancreant à la cellule A50 de la feuille spécifiée par SHEET_ID.

La demande configure le tableau croisé dynamique avec les propriétés suivantes:

  • Un groupe de valeurs (Quantity) indiquant le nombre de ventes. Étant donné qu'il n'y a qu'un seul groupe de valeurs, les deux paramètres valueLayout possibles sont équivalents.
  • Deux groupes de lignes (Item Category (Catégorie d'article) et Model Number (Numéro de modèle) La première effectue un tri par valeur croissante de la quantité totale de la région "Ouest". Par conséquent, "Moteur" (sans ventes Ouest) s'affiche au-dessus de "Porte" (avec 15 ventes Ouest). Le groupe Numéro de modèle effectue un tri par ordre décroissant des ventes totales dans toutes les régions. Par conséquent, "W-24" (15 ventes) apparaît au-dessus de "W-25" (8 ventes). Pour ce faire, définissez le champ valueBucket sur {}.
  • Un groupe de colonnes (Région) qui effectue le tri dans l'ordre croissant de la plupart des ventes. Là encore, valueBucket est défini sur {}. "Nord" présente le total des ventes le plus faible et apparaît donc dans la première colonne Region.

Le protocole de requête est illustré 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 de tableau croisé dynamique

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

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

La demande configure le tableau croisé dynamique avec les propriétés suivantes:

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

Le protocole de requête est illustré 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 un résultat de recette pour regrouper des valeurs de tableaux croisés dynamiques

Supprimer un tableau croisé dynamique

L'exemple de code spreadsheets.batchUpdate suivant montre comment utiliser UpdateCellsRequest pour supprimer un tableau croisé dynamique (le cas échéant) ancré sur la cellule A50 de la feuille spécifiée 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 dans la cellule d'ancrage.

Le protocole de requête est illustré 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

L'exemple de code spreadsheets.batchUpdate suivant montre comment utiliser UpdateCellsRequest pour modifier le tableau croisé dynamique créé dans Ajouter un tableau croisé dynamique.

Les sous-ensembles du champ pivotTable dans la ressource CellData ne peuvent pas être modifiés individuellement avec le paramètre fields. Pour apporter des modifications, l'intégralité du champ pivotTable doit être fournie. En gros, la modification d'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 (Commercial). Les colonnes sont triées par ordre décroissant en fonction du nombre total de ventes dans Panel. "Carmen" (15 ventes Panneau) apparaît à gauche de "Jessie" (13 ventes Panneau).
  • Réduit la colonne pour chaque région Region, à l'exception de "West", en masquant le groupe Commercial pour cette région. Pour ce faire, définissez collapsed sur true dans le valueMetadata pour cette colonne, dans le groupe de colonnes Région.

Le protocole de requête est illustré 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 d'un tableau croisé dynamique

L'exemple de code spreadsheets.get suivant montre comment obtenir les données d'un tableau croisé dynamique à partir d'une feuille de calcul. Le paramètre de requête fields spécifie que seules les données du tableau croisé dynamique doivent être renvoyées (par opposition aux données de valeur des cellules).

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

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

La réponse consiste en une ressource Spreadsheet, qui contient un objet Sheet avec des éléments SheetProperties. Il existe également un tableau d'éléments GridData contenant des informations sur PivotTable. Les informations du tableau croisé dynamique sont contenues dans la ressource CellData de la feuille pour la cellule sur laquelle le tableau est ancré (c'est-à-dire l'angle supérieur gauche du tableau). Si un champ de réponse est défini sur la valeur par défaut, il est omis de la réponse.

Dans cet exemple, la première feuille (SOURCE_SHEET_ID) contient les données sources du tableau brut, tandis que la deuxième feuille (SHEET_ID) contient le tableau croisé dynamique, ancré sur B3. Les accolades vides indiquent les feuilles ou les cellules qui ne contiennent pas de données de tableau croisé dynamique. Pour référence, cette requête renvoie également les ID de la feuille.

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