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 avec un tableau croisé dynamique à l'aide de 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érentes langues à l'aide des bibliothèques clientes de l'API Google, consultez Modifier 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 à l'aide de la méthode spreadsheets.get. Les plages sont spécifiées à l'aide de la notation A1. Par exemple, 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 indiqué 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 source "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 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 | 3/1/2016 |
| 3 | Porte | D-01X | 15 $ | 2 | Sud | Amir | 3/15/2016 |
| 4 | Moteur | ENG-0134 | 100 $ | 1 | Nord | Carmen | 3/20/2016 |
| 5 | Cadre | FR-0B1 | 34,00 $ | 8 | Est | Anna | 3/12/2016 |
| 6 | Panneau | P-034 | 6 $ | 4 | Nord | Devyn | 4/2/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 | 2/07/2016 |
| 10 | Porte | D-01Y | 29,00 $ | 6 | Ouest | Armando | 3/13/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 sur 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
valueLayoutpossibles sont équivalents. - Deux groupes de lignes (Catégorie de l'article et Numéro de modèle). La première trie par ordre croissant la quantité totale de la région "Ouest". Par conséquent, "Moteur" (sans 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) trié par ordre croissant des ventes les plus élevées.
Encore une fois,
valueBucketest défini sur{}. "Nord" enregistre le total de ventes le plus faible. Il apparaît donc dans 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 semblable à 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 sur 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 (plutôt que par Quantité) dans chaque groupe, ce qui met le tableau dans l'ordre alphabétique. Pour ce faire, omettez le champ
valueBucketdePivotGroup. - 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 semblable à 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 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. 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 (Vendeur). Les colonnes sont triées par ordre décroissant en fonction du nombre total de ventes de panneaux. "Carmen" (15 ventes sur le panel) s'affiche à gauche de "Jessie" (13 ventes sur le panel).
- Réduit la colonne pour chaque région, à l'exception de "Ouest", et masque le groupe Vendeur 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 semblable à 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 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 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 le PivotTable.
Les informations du tableau croisé dynamique sont contenues dans la ressource CellData de la feuille pour la cellule à 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 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
}
}
],
}