Tableaux croisés dynamiques

Ce guide explique comment et pourquoi utiliser l'API Google Sheets pour créer des tableaux croisés dynamiques dans vos feuilles de calcul.

Qu'est-ce qu'un tableau croisé dynamique ?

Les tableaux croisés dynamiques permettent de résumer les données de votre feuille de calcul en effectuant automatiquement différentes opérations (agrégation, tri, comptage ou calcul de la moyenne) et en affichant les résultats résumés dans un nouveau tableau. Un tableau croisé dynamique permet en quelque sorte d'interroger un ensemble de données source. Ces données sources se trouvent à un autre emplacement dans la feuille de calcul, et le tableau croisé dynamique présente une vue traitée des données.

Prenons l'exemple de l'ensemble de données sur les ventes suivant:

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

Vous pouvez utiliser un tableau croisé dynamique pour créer un rapport indiquant le nombre de modèles vendus dans chaque région:

Capture d'écran d'un tableau croisé dynamique affichant le nombre de numéros de modèle par région

Pour obtenir le code source utilisé pour générer ce tableau croisé dynamique, consultez la section Exemple ci-dessous.

Une fois qu'un tableau croisé dynamique est placé dans une feuille de calcul, les utilisateurs peuvent modifier de manière interactive la structure et les détails du résumé à l'aide de l'interface utilisateur de Sheets.

Utiliser des tableaux croisés dynamiques

Une définition de tableau croisé dynamique est associée à une seule cellule d'une feuille. Bien que son apparence affichée comporte de nombreuses cellules en hauteur et en largeur, il se trouve à une seule coordonnée de cellule de manière programmatique. Cette cellule devient l'angle supérieur gauche du tableau croisé dynamique affiché, dont l'étendue horizontale et verticale est déterminée par sa définition.

Ajouter un tableau croisé dynamique

Pour ajouter un tableau croisé dynamique, utilisez la méthode batchUpdate en fournissant une requête updateCells. Vous utilisez cette requête pour fournir une définition de PivotTable comme contenu d'une cellule, comme illustré ci-dessous:

{
  "updateCells": {
    "rows": {
      "values": [
        {
          "pivotTable": "MyPivotTable"
        }
      ],
      "start": {
        "sheetId": "sheetId",
        "rowIndex": 0,
        "columnIndex": 0
      },
      "fields": "pivotTable"
    }
  }
}

Le tableau croisé dynamique décrit par MyPivotTable est alors placé sur la feuille spécifiée, avec l'angle supérieur gauche à la cellule A1. (La hauteur et la largeur du tableau croisé dynamique sont dynamiques. Vous ne spécifiez que l'origine.)

Le type PivotTable vous permet de spécifier:

  • Plage de données source
  • Un ou plusieurs champs dont les données formeront les lignes du tableau croisé dynamique
  • Un ou plusieurs champs dont les données formeront les colonnes du tableau croisé dynamique
  • Critères de filtrage et d'agrégation
  • Mise en page du tableau croisé dynamique

Modifier et supprimer des tableaux croisés dynamiques

Aucune demande explicite de modification ou de suppression d'un tableau croisé dynamique n'a été envoyée. Utilisez plutôt une requête updateCells avec des contenus de cellule différents:

  • Pour modifier un tableau croisé dynamique, créez une définition modifiée de PivotTable et mettez à jour la cellule à l'aide de celle-ci, comme pour ajouter un tableau croisé dynamique.
  • Pour supprimer un tableau croisé dynamique, mettez à jour la cellule avec des valeurs vides. Pour obtenir un exemple, consultez l'exemple Supprimer un tableau croisé dynamique.

Cas d'utilisation

Les tableaux croisés dynamiques ont de nombreuses utilisations dans divers domaines, y compris l'analyse statistique, les applications ERP, les rapports financiers, etc. Voici quelques cas d'utilisation classiques des tableaux croisés dynamiques:

  • Ventes totales par région et par trimestre
  • Salaire moyen par poste et par zone géographique
  • Nombre d'incidents par produit et par heure de la journée

Le nombre d'applications potentielles des tableaux croisés dynamiques est vaste, et la possibilité de les générer de manière programmatique est puissante. Vous pouvez générer des tableaux croisés dynamiques compatibles avec l'exploration interactive, mais adaptés à des circonstances spécifiques, par exemple:

  • Explorer les données sur les incidents pour les 24 dernières heures
  • Afficher ou analyser les données globales correspondant au compte sélectionné
  • Examiner les données de vente pour les territoires appartenant à l'utilisateur actuel

Exemple

Cet exemple crée un tableau croisé dynamique à partir d'un ensemble de données pour générer le rapport "Numéro de modèle par région" présenté dans l'introduction de cette page. Pour obtenir d'autres exemples, consultez la page Exemple de tableau croisé dynamique.

Apps Script

sheets/api/spreadsheet_snippets.gs
/**
 * Create pivot table
 * @param {string} spreadsheetId spreadsheet ID
 * @returns {*} pivot table's spreadsheet
 */
Snippets.prototype.pivotTable = (spreadsheetId) => {
  try {
    const spreadsheet = SpreadsheetApp.openById(spreadsheetId);

    // Create two sheets for our pivot table, assume we have one.
    let sheet = spreadsheet.getSheets()[0];
    sheet.copyTo(spreadsheet);

    const sourceSheetId = spreadsheet.getSheets()[0].getSheetId();
    const targetSheetId = spreadsheet.getSheets()[1].getSheetId();

    // Create pivot table
    const pivotTable = Sheets.newPivotTable();

    let gridRange = Sheets.newGridRange();
    gridRange.sheetId = sourceSheetId;
    gridRange.startRowIndex = 0;
    gridRange.startColumnIndex = 0;
    gridRange.endRowIndex = 20;
    gridRange.endColumnIndex = 7;
    pivotTable.source = gridRange;

    let pivotRows = Sheets.newPivotGroup();
    pivotRows.sourceColumnOffset = 1;
    pivotRows.showTotals = true;
    pivotRows.sortOrder = 'ASCENDING';
    pivotTable.rows = pivotRows;

    let pivotColumns = Sheets.newPivotGroup();
    pivotColumns.sourceColumnOffset = 4;
    pivotColumns.sortOrder = 'ASCENDING';
    pivotColumns.showTotals = true;
    pivotTable.columns = pivotColumns;

    let pivotValue = Sheets.newPivotValue();
    pivotValue.summarizeFunction = 'COUNTA';
    pivotValue.sourceColumnOffset = 4;
    pivotTable.values = [pivotValue];

    // Create other metadata for the updateCellsRequest
    let cellData = Sheets.newCellData();
    cellData.pivotTable = pivotTable;

    let rows = Sheets.newRowData();
    rows.values = cellData;

    let start = Sheets.newGridCoordinate();
    start.sheetId = targetSheetId;
    start.rowIndex = 0;
    start.columnIndex = 0;

    let updateCellsRequest = Sheets.newUpdateCellsRequest();
    updateCellsRequest.rows = rows;
    updateCellsRequest.start = start;
    updateCellsRequest.fields = 'pivotTable';

    // Batch update our spreadsheet
    let batchUpdate = Sheets.newBatchUpdateSpreadsheetRequest();
    let updateCellsRawRequest = Sheets.newRequest();
    updateCellsRawRequest.updateCells = updateCellsRequest;
    batchUpdate.requests = [updateCellsRawRequest];
    const response = Sheets.Spreadsheets.batchUpdate(batchUpdate,
      spreadsheetId);

    return response;
  } catch (err) {
    // TODO (developer) - Handle exception
    console.log('Failed with error %s', err.message);
  }
};

Java

sheets/snippets/src/main/java/PivotTables.java
import com.google.api.client.googleapis.json.GoogleJsonError;
import com.google.api.client.googleapis.json.GoogleJsonResponseException;
import com.google.api.client.http.HttpRequestInitializer;
import com.google.api.client.http.javanet.NetHttpTransport;
import com.google.api.client.json.gson.GsonFactory;
import com.google.api.services.sheets.v4.Sheets;
import com.google.api.services.sheets.v4.SheetsScopes;
import com.google.api.services.sheets.v4.model.AddSheetRequest;
import com.google.api.services.sheets.v4.model.BatchUpdateSpreadsheetRequest;
import com.google.api.services.sheets.v4.model.BatchUpdateSpreadsheetResponse;
import com.google.api.services.sheets.v4.model.CellData;
import com.google.api.services.sheets.v4.model.GridCoordinate;
import com.google.api.services.sheets.v4.model.GridRange;
import com.google.api.services.sheets.v4.model.PivotGroup;
import com.google.api.services.sheets.v4.model.PivotTable;
import com.google.api.services.sheets.v4.model.PivotValue;
import com.google.api.services.sheets.v4.model.Request;
import com.google.api.services.sheets.v4.model.RowData;
import com.google.api.services.sheets.v4.model.UpdateCellsRequest;
import com.google.auth.http.HttpCredentialsAdapter;
import com.google.auth.oauth2.GoogleCredentials;
import com.google.common.collect.Lists;
import java.io.IOException;
import java.util.ArrayList;
import java.util.Collections;
import java.util.List;

/* Class to demonstrate the use of Spreadsheet Create Pivot Tables API */
public class PivotTables {
  /**
   * Create pivot table.
   *
   * @param spreadsheetId - Id of the spreadsheet.
   * @return pivot table's spreadsheet
   * @throws IOException - if credentials file not found.
   */
  public static BatchUpdateSpreadsheetResponse pivotTables(String spreadsheetId)
      throws IOException {
        /* Load pre-authorized user credentials from the environment.
           TODO(developer) - See https://developers.google.com/identity for
            guides on implementing OAuth2 for your application. */
    GoogleCredentials credentials = GoogleCredentials.getApplicationDefault()
        .createScoped(Collections.singleton(SheetsScopes.SPREADSHEETS));
    HttpRequestInitializer requestInitializer = new HttpCredentialsAdapter(
        credentials);

    // Create the sheets API client
    Sheets service = new Sheets.Builder(new NetHttpTransport(),
        GsonFactory.getDefaultInstance(),
        requestInitializer)
        .setApplicationName("Sheets samples")
        .build();

    // Create two sheets for our pivot table.
    List<Request> sheetsRequests = new ArrayList<>();
    BatchUpdateSpreadsheetResponse result = null;
    try {
      sheetsRequests.add(new Request().setAddSheet(new AddSheetRequest()));
      sheetsRequests.add(new Request().setAddSheet(new AddSheetRequest()));

      BatchUpdateSpreadsheetRequest createSheetsBody = new BatchUpdateSpreadsheetRequest()
          .setRequests(sheetsRequests);
      BatchUpdateSpreadsheetResponse createSheetsResponse = service.spreadsheets()
          .batchUpdate(spreadsheetId, createSheetsBody).execute();
      int sourceSheetId = createSheetsResponse.getReplies().get(0).getAddSheet().getProperties()
          .getSheetId();
      int targetSheetId = createSheetsResponse.getReplies().get(1).getAddSheet().getProperties()
          .getSheetId();

      PivotTable pivotTable = new PivotTable()
          .setSource(
              new GridRange()
                  .setSheetId(sourceSheetId)
                  .setStartRowIndex(0)
                  .setStartColumnIndex(0)
                  .setEndRowIndex(20)
                  .setEndColumnIndex(7)
          )
          .setRows(Collections.singletonList(
              new PivotGroup()
                  .setSourceColumnOffset(1)
                  .setShowTotals(true)
                  .setSortOrder("ASCENDING")
          ))
          .setColumns(Collections.singletonList(
              new PivotGroup()
                  .setSourceColumnOffset(4)
                  .setShowTotals(true)
                  .setSortOrder("ASCENDING")
          ))
          .setValues(Collections.singletonList(
              new PivotValue()
                  .setSummarizeFunction("COUNTA")
                  .setSourceColumnOffset(4)
          ));
      List<Request> requests = Lists.newArrayList();
      Request updateCellsRequest = new Request().setUpdateCells(new UpdateCellsRequest()
          .setFields("*")
          .setRows(Collections.singletonList(
              new RowData().setValues(
                  Collections.singletonList(
                      new CellData().setPivotTable(pivotTable))
              )
          ))
          .setStart(new GridCoordinate()
              .setSheetId(targetSheetId)
              .setRowIndex(0)
              .setColumnIndex(0)

          ));

      requests.add(updateCellsRequest);
      BatchUpdateSpreadsheetRequest updateCellsBody = new BatchUpdateSpreadsheetRequest()
          .setRequests(requests);
      result = service.spreadsheets().batchUpdate(spreadsheetId, updateCellsBody).execute();
    } catch (GoogleJsonResponseException e) {
      // TODO(developer) - handle error appropriately
      GoogleJsonError error = e.getDetails();
      if (error.getCode() == 404) {
        System.out.printf("Spreadsheet not found with id '%s'.\n", spreadsheetId);
      } else {
        throw e;
      }
    }
    return result;
  }
}

JavaScript

sheets/snippets/sheets_pivot_tables.js
function pivotTable(spreadsheetId, callback) {
  // Create two sheets for our pivot table
  const requests = [{
    addSheet: {},
  }, {
    addSheet: {},
  }];
  const batchUpdateRequest = {requests: requests};
  try {
    gapi.client.sheets.spreadsheets.batchUpdate({
      spreadsheetId: spreadsheetId,
      resource: batchUpdateRequest,
    }).then((response) => {
      const sourceSheetId = response.result.replies[0].addSheet.properties.sheetId;
      const targetSheetId = response.result.replies[1].addSheet.properties.sheetId;

      const requests = [{
        updateCells: {
          rows: {
            values: [{
              pivotTable: {
                source: {
                  sheetId: sourceSheetId,
                  startRowIndex: 0,
                  startColumnIndex: 0,
                  endRowIndex: 20,
                  endColumnIndex: 7,
                },
                rows: [{
                  sourceColumnOffset: 1,
                  showTotals: true,
                  sortOrder: 'ASCENDING',
                }],
                columns: [{
                  sourceColumnOffset: 4,
                  sortOrder: 'ASCENDING',
                  showTotals: true,
                }],
                values: [{
                  summarizeFunction: 'COUNTA',
                  sourceColumnOffset: 4,
                }],
                valueLayout: 'HORIZONTAL',
              },
            },
            ],
          },
          start: {
            sheetId: targetSheetId,
            rowIndex: 0,
            columnIndex: 0,
          },
          fields: 'pivotTable',
        },
      }];

      const body = {
        requests,
      };
      gapi.client.sheets.spreadsheets.batchUpdate({
        spreadsheetId: spreadsheetId,
        resource: body,
      }).then((response) => {
        if (callback) callback(response);
      });
    });
  } catch (err) {
    document.getElementById('content').innerText = err.message;
    return;
  }
}

Node.js

sheets/snippets/sheets_pivot_table.js
/**
 * Adds a pivot table to a spreadsheet.
 * @param {string} spreadsheetId The Spreadsheet to add the pivot table to.
 * @return {obj} spreadsheet information
 */
async function pivotTable(spreadsheetId) {
  const {GoogleAuth} = require('google-auth-library');
  const {google} = require('googleapis');

  const auth = new GoogleAuth({
    scopes: 'https://www.googleapis.com/auth/spreadsheets',
  });

  const service = google.sheets({version: 'v4', auth});
  try {
    // Create two sheets for our pivot table
    let requests = [
      {
        addSheet: {},
      },
      {
        addSheet: {},
      },
    ];
    let resource = {requests};
    let response = await service.spreadsheets.batchUpdate({
      spreadsheetId,
      resource,
    });
    const sourceSheetId = response.data.replies[0].addSheet.properties.sheetId;
    const targetSheetId = response.data.replies[1].addSheet.properties.sheetId;

    requests = [
      {
        updateCells: {
          rows: {
            values: [
              {
                pivotTable: {
                  source: {
                    sheetId: sourceSheetId,
                    startRowIndex: 0,
                    startColumnIndex: 0,
                    endRowIndex: 20,
                    endColumnIndex: 7,
                  },
                  rows: [
                    {
                      sourceColumnOffset: 1,
                      showTotals: true,
                      sortOrder: 'ASCENDING',
                    },
                  ],
                  columns: [
                    {
                      sourceColumnOffset: 4,
                      sortOrder: 'ASCENDING',
                      showTotals: true,
                    },
                  ],
                  values: [
                    {
                      summarizeFunction: 'COUNTA',
                      sourceColumnOffset: 4,
                    },
                  ],
                  valueLayout: 'HORIZONTAL',
                },
              },
            ],
          },
          start: {
            sheetId: targetSheetId,
            rowIndex: 0,
            columnIndex: 0,
          },
          fields: 'pivotTable',
        },
      },
    ];
    resource = {
      requests,
    };
    response = service.spreadsheets.batchUpdate({
      spreadsheetId,
      resource,
    });
    return response;
  } catch (err) {
    // TODO (developer) - Handle exception
    throw err;
  }
}

PHP

sheets/snippets/src/SpreadsheetPivotTables.php
use Google\Client;
use Google\Service\Drive;
use Google\Service\Sheets\BatchUpdateSpreadsheetRequest;


function pivotTables($spreadsheetId)
    {
        /* Load pre-authorized user credentials from the environment.
           TODO(developer) - See https://developers.google.com/identity for
            guides on implementing OAuth2 for your application. */
        $client = new Google\Client();
        $client->useApplicationDefaultCredentials();
        $client->addScope(Google\Service\Drive::DRIVE);
        $service = new Google_Service_Sheets($client);
        try{
            $requests = [
                new Google_Service_Sheets_Request([
                    'addSheet' => [
                        'properties' => [
                            'title' => 'Sheet 1'
                            ]
                ]
            ]),
            new Google_Service_Sheets_Request([
                'addSheet' => [
                    'properties' => [
                        'title' => 'Sheet 2'
                        ]
                        ]
                        ])
                    ];
                    // Create two sheets for our pivot table
                    $batchUpdateRequest = new Google_Service_Sheets_BatchUpdateSpreadsheetRequest([
                        'requests' => $requests
                    ]);
        $batchUpdateResponse = $service->spreadsheets->batchUpdate($spreadsheetId, $batchUpdateRequest);
        $sourceSheetId = $batchUpdateResponse->replies[0]->addSheet->properties->sheetId;
        $targetSheetId = $batchUpdateResponse->replies[1]->addSheet->properties->sheetId;
        $requests = [
            'updateCells' => [
                'rows' => [
                    'values' => [
                        [
                            'pivotTable' => [
                                'source' => [
                                    'sheetId' => $sourceSheetId,
                                    'startRowIndex' => 0,
                                    'startColumnIndex' => 0,
                                    'endRowIndex' => 20,
                                    'endColumnIndex' => 7
                                ],
                                'rows' => [
                                    [
                                        'sourceColumnOffset' => 1,
                                        'showTotals' => true,
                                        'sortOrder' => 'ASCENDING',
                                    ],
                                ],
                                'columns' => [
                                    [
                                        'sourceColumnOffset' => 4,
                                        'sortOrder' => 'ASCENDING',
                                        'showTotals' => true,
                                    ]
                                ],
                                'values' => [
                                    [
                                        'summarizeFunction' => 'COUNTA',
                                        'sourceColumnOffset' => 4
                                        ]
                                    ],
                                'valueLayout' => 'HORIZONTAL'
                            ]
                        ]
                        ]
                ],
                'start' => [
                    'sheetId' => $targetSheetId,
                    'rowIndex' => 0,
                    'columnIndex' => 0
                ],
                'fields' => 'pivotTable'
            ]
        ];
        return $batchUpdateResponse;
    }
        catch(Exception $e) {
            // TODO(developer) - handle error appropriately
            echo 'Message: ' .$e->getMessage();
        }
    }

Python

sheets/snippets/sheets_pivot_tables.py
import google.auth
from googleapiclient.discovery import build
from googleapiclient.errors import HttpError


def pivot_tables(spreadsheet_id):
  """
  Creates the batch_update the user has access to.
  Load pre-authorized user credentials from the environment.
  TODO(developer) - See https://developers.google.com/identity
  for guides on implementing OAuth2 for the application.
  """
  creds, _ = google.auth.default()
  # pylint: disable=maybe-no-member
  try:
    service = build("sheets", "v4", credentials=creds)
    # Create two sheets for our pivot table.
    body = {"requests": [{"addSheet": {}}, {"addSheet": {}}]}
    batch_update_response = (
        service.spreadsheets()
        .batchUpdate(spreadsheetId=spreadsheet_id, body=body)
        .execute()
    )
    source_sheet_id = (
        batch_update_response.get("replies")[0]
        .get("addSheet")
        .get("properties")
        .get("sheetId")
    )
    target_sheet_id = (
        batch_update_response.get("replies")[1]
        .get("addSheet")
        .get("properties")
        .get("sheetId")
    )
    requests = []
    requests.append(
        {
            "updateCells": {
                "rows": {
                    "values": [
                        {
                            "pivotTable": {
                                "source": {
                                    "sheetId": source_sheet_id,
                                    "startRowIndex": 0,
                                    "startColumnIndex": 0,
                                    "endRowIndex": 20,
                                    "endColumnIndex": 7,
                                },
                                "rows": [
                                    {
                                        "sourceColumnOffset": 1,
                                        "showTotals": True,
                                        "sortOrder": "ASCENDING",
                                    },
                                ],
                                "columns": [{
                                    "sourceColumnOffset": 4,
                                    "sortOrder": "ASCENDING",
                                    "showTotals": True,
                                }],
                                "values": [{
                                    "summarizeFunction": "COUNTA",
                                    "sourceColumnOffset": 4,
                                }],
                                "valueLayout": "HORIZONTAL",
                            }
                        }
                    ]
                },
                "start": {
                    "sheetId": target_sheet_id,
                    "rowIndex": 0,
                    "columnIndex": 0,
                },
                "fields": "pivotTable",
            }
        }
    )
    body = {"requests": requests}
    response = (
        service.spreadsheets()
        .batchUpdate(spreadsheetId=spreadsheet_id, body=body)
        .execute()
    )
    return response

  except HttpError as error:
    print(f"An error occurred: {error}")
    return error


if __name__ == "__main__":
  # Pass: spreadsheet_id
  pivot_tables("1CM29gwKIzeXsAppeNwrc8lbYaVMmUclprLuLYuHog4k")

Ruby

sheets/snippets/lib/spreadsheet_snippets.rb
requests = [{
  update_cells: {
    rows:   {
      values: [
        {
          pivot_table: {
            source:       {
              sheet_id:           source_sheet_id,
              start_row_index:    0,
              start_column_index: 0,
              end_row_index:      20,
              end_column_index:   7
            },
            rows:         [
              {
                source_column_offset: 1,
                show_totals:          true,
                sort_order:           'ASCENDING',
              },
            ],
            columns:      [
              {
                source_column_offset: 4,
                sort_order:           'ASCENDING',
                show_totals:          true,
              }
            ],
            values:       [
              {
                summarize_function:   'COUNTA',
                source_column_offset: 4
              }
            ],
            value_layout: 'HORIZONTAL'
          }
        }
      ]
    },
    start:  {
      sheet_id:     target_sheet_id,
      row_index:    0,
      column_index: 0
    },
    fields: 'pivotTable'
  }
}]
result = service.batch_update_spreadsheet(spreadsheet_id, body, {})