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 montrent comment effectuer certaines opérations courantes sur les tableaux croisés dynamiques avec l'API Sheets.
Ces exemples sont présentés sous forme de requêtes HTTP pour être indépendants du langage. Pour découvrir comment implémenter une mise à jour par lot dans différents langages à l'aide de s 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'ID
de la feuille de calcul dans son URL.
Vous pouvez obtenir l'ID de la feuille à l'aide
de la
spreadsheets.get
méthode. Les plages sont spécifiées à l'aide de la notation
A1. Par exemple, Sheet1!A1:D5.
De plus, l'espace réservé SOURCE_SHEET_ID indique votre 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 contient les données sources "ventes" suivantes dans sa première feuille ("Sheet1"). 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 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 | D-01X | 15,00 $ | 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,00 $ | 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,00 $ | 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,00 $ | 2 | Ouest | Armando | 03/07/2016 |
| 16 | Cadre | FR-0B1 | 34,00 $ | 6 | Sud | Carmen | 15/07/2016 |
| 17 | Roue | W-25 | 20,00 $ | 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 suivant
spreadsheets.batchUpdate
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. Comme il n'y a qu'un seul groupe de valeurs, les deux paramètres possibles sont équivalents.
valueLayout - Deux groupes de lignes (Catégorie de l'élément et Numéro de modèle). Le premier trie par ordre croissant la valeur totale de la Quantité de la Région "Ouest". Par conséquent, "Moteur" (sans ventes à l'ouest) apparaît au-dessus de "Porte" (avec 15 ventes à l'ouest). Le groupe Numéro de modèle trie par ordre décroissant le total des ventes dans toutes les régions. Par conséquent, "W-24" (15 ventes) apparaît au-dessus de "W-25" (8 ventes). Pour ce faire,
en définissant le
valueBucketchamp sur{}. - Un groupe de colonnes (Région) qui trie par ordre croissant le nombre de ventes.
Là encore,
valueBucketest défini sur{}. "Nord" a le total de ventes le plus faible et apparaît donc comme la première 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. Le second est une valeur calculée basée sur le 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'élément, 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 (plutôt que par Quantité) dans chaque groupe, en classant le tableau par ordre alphabétique. Pour ce faire, le
valueBucketchamp est omis duPivotGroup.- Pour simplifier l'apparence du tableau, la requête masque les sous-totaux pour tous les groupes de lignes et de colonnes, à l'exception des 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.
Une 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 les
UpdateCellsRequest
pour modifier le tableau croisé dynamique créé dans Ajouter un tableau croisé dynamique.
Les sous-ensembles du
pivotTable
champ dans la
CellData ressource
ne peuvent pas être modifiés individuellement avec le fields paramètre. Pour effectuer des modifications, l'intégralité du champ pivotTable doit être fournie. En fait, la modification d'un tableau croisé dynamique nécessite de 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 de Panneaux) apparaît à gauche de "Jessica" (13 ventes de Panneaux).
- Réduit la colonne de chaque Région, à l'exception de "Ouest", en masquant le
Commercial groupe pour cette région. Pour ce faire,
collapsedest défini surtruedans levalueMetadatade cette colonne du 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 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 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
Spreadsheet
ressource, qui contient un
Sheet objet avec des
SheetProperties
éléments. Il existe également un tableau d'éléments
GridData
contenant des informations sur le
PivotTable.
Les informations du tableau croisé dynamique sont contenues dans la ressource
de la feuille pour la cellule sur laquelle le tableau est ancré (c'est-à-dire le coin supérieur gauche
du tableau).CellData 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 référence, cette requête renvoie également les ID de 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} } ], }