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 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
dePivotGroup
. - 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
surVERTICAL
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:
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
surtrue
dans levalueMetadata
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:
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
} } ], }