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 effectuer certaines opérations courantes sur les tableaux croisés dynamiques avec l'API Sheets.
Ces exemples sont présentés sous la forme de requêtes HTTP pour être neutres en termes de langue. Pour découvrir comment implémenter une mise à jour par lot 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. Vous trouverez l'identifiant de la feuille de calcul dans l'URL de la feuille de calcul.
Vous pouvez obtenir l'ID de la feuille de calcul à l'aide de la méthode spreadsheets.get. Les plages sont spécifiées à l'aide de la notation A1. Par exemple, la plage Feuille1!A1:D5.
De plus, l'espace réservé SOURCE_SHEET_ID indique la feuille contenant les données sources. Dans ces exemples, il s'agit du tableau listé 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 ("Feuille1"). Les chaînes de la première ligne sont des libellés pour les colonnes individuelles. Pour voir des exemples de lecture à partir d'autres feuilles de votre feuille de calcul, consultez la 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 | 1/3/2016 |
| 3 | Porte | D-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 | Jessie | 02/07/2016 |
| 10 | Porte | D-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 | Jessie | 20/06/2016 |
| 14 | Moteur | ENG-0211 | 283,00 $ | 1 | Nord | Amir | 21/06/2016 |
| 15 | Porte | D-01X | 15 $ | 2 | Ouest | Armando | 3/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'ancrant 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 :
- Un groupe de valeurs (Quantité) qui indique le nombre de ventes. Étant donné qu'il n'y a qu'un seul groupe de valeurs, les deux paramètres
valueLayoutpossibles sont équivalents. - Deux groupes de lignes (Catégorie de l'article et Numéro de modèle). Le premier trie les valeurs croissantes de la quantité totale de la région "Ouest". Par conséquent, "Moteur" (sans aucune vente à l'ouest) apparaît au-dessus de "Porte" (avec 15 ventes à l'ouest). Le groupe Numéro de modèle est trié par ordre décroissant du total des ventes dans toutes les régions. "W-24" (15 ventes) apparaît donc au-dessus de "W-25" (8 ventes). Pour ce faire, définissez le champ
valueBucketsur{}. - Un groupe de colonnes (Région) qui trie les données par ordre croissant des ventes les plus élevées.
Là encore,
valueBucketest défini sur{}. La région "Nord" enregistre le montant total des ventes le plus faible. Elle apparaît donc en premier dans la colonne 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" } ] } }, { "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 requête crée un tableau croisé dynamique comme celui-ci :
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 calculées à partir des données sources, en l'ancrant 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 (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 de son nombre total de ventes, à l'aide de la formule suivante :
=Cost*SUM(Quantity). - Trois groupes de lignes (Catégorie de l'article, Numéro de modèle et Coût).
- Un groupe de colonnes (Région).
- Les groupes de lignes et de colonnes sont triés par nom (et non par Quantité) dans chaque groupe, ce qui permet de classer le tableau par ordre alphabétique. Pour ce faire, omettez le champ
valueBucketdePivotGroup.- 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 principaux.
- La requête définit
valueLayoutsurVERTICALpour améliorer l'apparence du tableau.valueLayoutn'est important que s'il existe au moins deux groupes de valeurs.
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 requête crée un tableau croisé dynamique comme celui-ci :
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é dans 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 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
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 de 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 fait, pour modifier un tableau croisé dynamique, vous devez le remplacer par un nouveau.
La requête 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 de panneaux. "Carmen" (15 ventes Panel) apparaît à gauche de "Jessie" (13 ventes Panel).
- Réduit la colonne pour chaque région, à l'exception de "Ouest", en masquant le groupe Commercial pour cette région. Pour ce faire, définissez
collapsedsurtruedansvalueMetadatapour 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 requête crée un tableau croisé dynamique comme celui-ci :
Lire les données d'un tableau croisé dynamique
L'exemple de code spreadsheets.get suivant montre comment obtenir des données de tableau croisé dynamique à partir d'une feuille de calcul. Le paramètre de requête fields indique que seules les données du tableau croisé dynamique doivent être renvoyées (par opposition aux données de valeur de cellule).
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'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 brutes du tableau, 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 information, cette requête renvoie également les ID des feuilles.
{ "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} } ], }