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 de tableau croisé dynamique avec l'API Sheets.
Ces exemples sont présentés sous la forme de requêtes HTTP afin de ne pas tenir compte du langage. Pour découvrir comment implémenter une mise à jour par lot dans différents langages à l'aide des bibliothèques clientes des 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 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 au format A1. Exemple de plage : Sheet1!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 répertorié 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 de "ventes" sources 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 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 | J-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,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 | 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 | J-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'ancrer à 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 (Quantity) 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 par ordre croissant de la quantité totale pour la région "Ouest". Par conséquent, "Moteur" (sans ventes à l'Ouest) apparaît 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. "W-24" (15 ventes) apparaît au-dessus de "W-25" (8 ventes). Pour ce faire, définissez le champ
valueBucketsur{}. - Un groupe de colonnes (Region) qui trie le plus grand nombre de ventes par ordre croissant
Là encore,
valueBucketest défini sur{}. "Nord" correspond au nombre total de ventes le moins élevé et apparaît donc dans la première colonne Region (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 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 calculées à partir des données sources, en l'ancréant 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 (Quantity et Total Price) Le premier indique le
nombre de ventes. La seconde est une valeur calculée basée sur le produit du coût d'une pièce et du nombre total de ventes, à l'aide de la formule suivante :
=Cost*SUM(Quantity). - Trois groupes de lignes : Item Category (Catégorie de l'article), Model Number (Numéro de modèle) et Cost (Coût).
- Un groupe de colonnes (Region)
- Les groupes de lignes et de colonnes trient par nom (plutôt que par quantité) dans chaque groupe, 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, sauf ceux de la ligne principale.
- 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 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é 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 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. Fondamentalement, 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 (Salesperson). Les colonnes sont triées par ordre décroissant en fonction du nombre total de ventes de Panel. "Carmen" (15 ventes Panel) apparaît à gauche de "Jessie" (13 ventes Panel).
- Réduit la colonne pour chaque région, à l'exception de "Ouest", masquant le groupe Commercial pour cette région. Pour ce faire, définissez
collapsedsurtruedans la colonnevalueMetadatadu groupe de colonnes Region (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 demande crée un tableau croisé dynamique comme celui-ci:
Lire les données du 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 (et non les données de valeur des cellules).
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 le coin 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 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
}
}
],
}