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 vous pouvez obtenir certains tableaux croisés dynamiques courants avec l'API Sheets.
Ces exemples sont présentés sous la forme de requêtes HTTP et neutres. Pour découvrir comment implémenter une mise à jour par lot dans différents langages à l'aide de les bibliothèques clientes de l'API Google, consultez la section Mettre à jour feuilles de calcul.
Dans ces exemples, les espaces réservés SPREADSHEET_ID et SHEET_ID
indique où vous devez fournir ces identifiants. Vous pouvez accéder à la feuille de calcul
ID dans l'URL de la feuille de calcul. Vous pouvez obtenir
l'ID de la feuille de calcul à l'aide de l'élément
spreadsheets.get. La
sont spécifiées au format A1. Une
exemple de plage est 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 répertorié sous Tableau croisé dynamique
données sources de la table.
Données sources du tableau croisé dynamique
Pour ces exemples, supposons que la feuille de calcul utilisée dispose de la source suivante "ventes" dans sa première feuille ("Feuille1"). Les chaînes de la première ligne sont des étiquettes pour les colonnes individuelles. Pour voir des exemples de lecture sur 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 $ | 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 | 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
Les éléments suivants :
spreadsheets.batchUpdate
exemple de code montre comment utiliser
UpdateCellsRequest
pour créer un tableau croisé dynamique à partir des données sources, en l'ancréant sur la cellule A50 du
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. Depuis
il n'y a qu'un seul groupe de valeurs, les deux
valueLayoutsont équivalents. - Deux groupes de lignes (Catégorie de l'article et Numéro de modèle). Le premier tri
valeur croissante de la quantité totale de "Ouest" Région : Par conséquent,
"Moteur" (sans ventes à l'Ouest) apparaît au-dessus de "Porte". (avec 15 ventes à l'Ouest). La
Numéro de modèle effectue un tri par ordre décroissant des ventes totales dans toutes
par région, par exemple "W-24" (15 ventes) apparaît au-dessus de "W-25" (8 ventes). C'est fait
en paramétrant le
valueBucketsur{}. - Un groupe de colonnes (Region) qui trie le plus grand nombre de ventes par ordre croissant
Là encore,
valueBucketest défini sur{}. "Nord" génère le moins de ventes totales ; pour qu'elle apparaisse dans la première colonne Region.
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
Les éléments suivants :
spreadsheets.batchUpdate
exemple de code montre comment utiliser
UpdateCellsRequest
pour créer un tableau croisé dynamique avec un
groupe de valeurs de calcul à partir des données sources,
l'ancrer 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 en fonction du produit
le coût d'une pièce et le nombre total de ses 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 sont triés par nom (plutôt que par quantité) dans chaque
en classant
le tableau par ordre alphabétique. Pour ce faire, omettez le
valueBucketdans le champPivotGroup - Pour simplifier l'apparence du tableau, la requête masque les sous-totaux pour tous les groupes de lignes et de colonnes, sauf les groupes de lignes et de colonnes principaux.
- La requête définit
valueLayoutàVERTICALpour améliorer l'apparence du tableau.valueLayoutest seulement important s'il y a deux groupes de valeurs ou plus.
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
Les éléments suivants :
spreadsheets.batchUpdate
exemple de code montre comment utiliser
UpdateCellsRequest
pour supprimer un tableau croisé dynamique (le cas échéant) ancré dans la cellule A50 de la feuille
spécifié 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 sur l'ancre.
cellule.
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
Les éléments suivants :
spreadsheets.batchUpdate
exemple de code montre comment utiliser
UpdateCellsRequest
pour modifier le tableau croisé dynamique créé dans Ajouter un tableau croisé dynamique.
Sous-ensembles du
pivotTable
dans
Ressource CellData
ne peut pas être modifié individuellement avec le paramètre fields. Pour apporter des modifications,
l'intégralité du champ pivotTable doit être renseignée. En bref, modifier
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 trient dans l’ordre décroissant par le nombre total de ventes de Panel. "Carmen" (15 ventes Panneau) semble à gauche de "Jessie" (13 ventes Panel).
- Réduit la colonne pour chaque région, à l'exception de "Ouest", en masquant la
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 demande crée un tableau croisé dynamique comme celui-ci:
Lire les données du tableau croisé dynamique
Les éléments suivants :
Exemple de code spreadsheets.get
montre comment obtenir les données d'un tableau croisé dynamique à partir d'une feuille de calcul. Requête fields
spécifie que seules les données du tableau croisé dynamique doivent être renvoyées (comme
par opposition aux données
de la 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'un
Spreadsheet
ressource, qui contient
Objet Sheet avec
SheetProperties
éléments. Il y a aussi un tableau
GridData
contenant des informations
PivotTable
Les informations du tableau croisé dynamique sont contenues dans le
Ressource CellData
pour la cellule sur laquelle le tableau est ancré (c'est-à-dire, dans la partie supérieure gauche
. Si un champ de réponse est défini sur la valeur par défaut, il est omis du champ
de réponse.
Dans cet exemple, la première feuille (SOURCE_SHEET_ID) contient le tableau brut
les données sources, tandis que la deuxième feuille (SHEET_ID) contient le tableau croisé dynamique,
ancrée sur B3. Les accolades vides indiquent les feuilles ou les cellules qui n'ont pas
contiennent des données de tableau croisé dynamique. Pour référence, cette requête renvoie également la feuille
ID.
{
"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
}
}
],
}