Cette page a été traduite par l'API Cloud Translation.

Utiliser des feuilles connectées

Les feuilles connectées sont une fonctionnalité de Google Sheets qui vous permet d'analyser les données BigQuery directement dans Sheets. Vous pouvez accéder aux feuilles connectées par programmation à l'aide du service Feuille de calcul.

Actions courantes avec les feuilles connectées

Utilisez les classes et objets DataSource pour vous connecter à BigQuery et analyser les données. Le tableau ci-dessous liste les actions DataSource les plus courantes et explique comment les créer dans Apps Script:

Action	Classe Google Apps Script	Méthode à utiliser
Associer une feuille à BigQuery	`DataSourceSpec`	`SpreadsheetApp.newDataSourceSpec()`
Choisir une source de données BigQuery	`DataSource`	`Spreadsheet.insertDataSourceSheet().getDataSource()`
Ajouter une feuille de source de données BigQuery	`DataSourceSheet`	`Spreadsheet.insertDataSourceSheet()`
Ajouter un tableau croisé dynamique	`DataSourcePivotTable`	`Range.insertDataSourcePivotTable()`
Extraire des données dans une extraction	`DataSourceTable`	`Range.insertDataSourceTable()`
Utiliser une formule	`DataSourceFormula`	`Range.setFormula()`
Add a chart	`DataSourceChart`	`Sheet.insertDataSourceChart()`

Ajouter les champs d'application d'autorisation requis

Pour accéder aux données BigQuery, vous devez inclure la méthode enableBigQueryExecution() dans votre code Google Apps Script. Cette méthode ajoute le champ d'application OAuth bigquery.readonly requis à votre projet Google Apps Script.

L'exemple suivant montre la méthode SpreadsheetApp.enableBigQueryExecution() appelée dans une fonction:

function addDataSource() {
  SpreadsheetApp.enableBigQueryExecution();
  var spreadsheet = SpreadsheetApp.getActive();
  }

Ajouter des champs d'application OAuth supplémentaires au fichier manifeste

La plupart des champs d'application OAuth sont automatiquement ajoutés au fichier manifeste en fonction des fonctions utilisées dans votre code. Si vous avez besoin de champs d'application supplémentaires pour accéder à certaines données BigQuery, vous pouvez définir des champs d'application explicites.

Par exemple, pour interroger des données BigQuery hébergées dans Google Drive, vous devez ajouter un champ d'application OAuth pour Drive à votre fichier manifeste.

L'exemple suivant montre la partie oauthScopes d'un fichier manifeste. Il ajoute un champ d'application OAuth pour Drive en plus des champs d'application OAuth spreadsheet et bigquery.readonly minimaux requis:

{ ...
  "oauthScopes": [
    "https://www.googleapis.com/auth/bigquery.readonly",
    "https://www.googleapis.com/auth/spreadsheets",
    "https://www.googleapis.com/auth/drive" ],
... }

Exemple: Créer et actualiser un objet de source de données

L'exemple suivant montre comment ajouter une source de données BigQuery, créer un objet de source de données à partir de la source de données, actualiser l'objet de source de données et obtenir l'état de l'exécution. Dans cet exemple, les extraits de code sont exécutés dans l'ordre.

Ajouter une source de données BigQuery

Pour ajouter une source de données BigQuery à une feuille de calcul, insérez une feuille de source de données avec une spécification de source de données. La feuille de source de données est actualisée automatiquement pour récupérer les données d'aperçu.

Remplacez <YOUR_PROJECT_ID> ci-dessous par un ID de projet Google Cloud valide.

// For operations that fetch data from BigQuery, enableBigQueryExecution() must be called.
SpreadsheetApp.enableBigQueryExecution();
var spreadsheet = SpreadsheetApp.create('Test connected sheets');
Logger.log('New test spreadsheet: %s', spreadsheet.getUrl());

// Build data source spec by selecting a table.
var dataSourceSpec = SpreadsheetApp.newDataSourceSpec()
    .asBigQuery()
    .setProjectId('<YOUR_PROJECT_ID>')
    .setTableProjectId('bigquery-public-data')
    .setDatasetId('ncaa_basketball')
    .setTableId('mbb_historical_tournament_games')
    .build();
// Add data source and its associated data source sheet.
var dataSourceSheet = spreadsheet.insertDataSourceSheet(dataSourceSpec);
var dataSource = dataSourceSheet.getDataSource();

Ajouter un objet de source de données

Une fois la source de données ajoutée à la feuille de calcul, vous pouvez créer des objets de source de données à partir de celle-ci. Dans cet exemple, un tableau croisé dynamique est créé à l'aide de DataSourcePivotTable.

Contrairement aux données standards des feuilles de grille référencées par un index de cellule ou des notations A1, les données provenant de sources de données sont généralement référencées par des noms de colonne. Par conséquent, la plupart des setters de propriété sur les objets de source de données utilisent le nom de la colonne en entrée.

var rootCell = spreadsheet.insertSheet('pivotTableSheet').getRange('A1');

// Add data source pivot table and set data source specific configurations.
var dataSourcePivotTable = rootCell.createDataSourcePivotTable(dataSource);
var rowGroup = dataSourcePivotTable.addRowGroup('season');
rowGroup.sortDescending().setGroupLimit(5);
dataSourcePivotTable.addColumnGroup('win_school_ncaa');
dataSourcePivotTable.addPivotValue('win_pts', SpreadsheetApp.PivotTableSummarizeFunction.AVERAGE);
dataSourcePivotTable.addPivotValue('game_date', SpreadsheetApp.PivotTableSummarizeFunction.COUNTA);
var filterCriteria = SpreadsheetApp.newFilterCriteria()
    .whenTextEqualToAny(['Duke', 'North Carolina'])
    .build();
dataSourcePivotTable.addFilter('win_school_ncaa', filterCriteria);

// Get a regular pivot table instance and set shared configurations.
var pivotTable = dataSourcePivotTable.asPivotTable();
pivotTable.setValuesDisplayOrientation(SpreadsheetApp.Dimension.ROWS);

Actualiser un objet de source de données

Vous pouvez actualiser les objets de sources de données pour récupérer les dernières données de BigQuery en fonction des spécifications de la source de données et des configurations d'objet.

Le processus d'actualisation des données est asynchrone. Pour actualiser un objet de source de données, utilisez les méthodes suivantes:

refreshData() lance l'actualisation des données.
waitForCompletion() renvoie l'état final une fois l'exécution des données terminée. Ainsi, il n'est plus nécessaire d'interroger régulièrement l'état d'exécution.
DataExecutionStatus.getErrorCode() obtient le code d'erreur en cas d'échec d'exécution des données.

L'exemple ci-dessous illustre une actualisation des données du tableau croisé dynamique:

var status = dataSourcePivotTable.getStatus();
Logger.log('Initial state: %s', status.getExecutionState());

dataSourcePivotTable.refreshData();

status = dataSourcePivotTable.waitForCompletion(/* timeoutInSeconds= */ 60);
Logger.log('Ending state: %s', status.getExecutionState());
if (status.getExecutionState() == SpreadsheetApp.DataExecutionState.ERROR) {
  Logger.log('Error: %s (%s)', status.getErrorCode(), status.getErrorMessage());
}

Utiliser des déclencheurs avec des feuilles connectées

Automatisez les fonctions de vos sources de données dans vos feuilles connectées à l'aide de déclencheurs et d'événements. Par exemple, utilisez des déclencheurs basés sur le temps pour actualiser les objets de source de données à plusieurs reprises à un moment précis, et utilisez des déclencheurs d'événements de feuille de calcul pour déclencher l'exécution des données sur un événement prédéfini.

L'exemple ci-dessous ajoute une source de données avec un paramètre de requête et actualise la feuille de la source de données lorsque le paramètre de requête est modifié.

Remplacez <YOUR_PROJECT_ID> ci-dessous par un ID de projet Google Cloud valide.

// Add data source with query parameter.
function addDataSource() {
  SpreadsheetApp.enableBigQueryExecution();
  var spreadsheet = SpreadsheetApp.getActive();

  // Add a new sheet and use A1 cell as the parameter cell.
  var parameterCell = spreadsheet.insertSheet('parameterSheet').getRange('A1');
parameterCell.setValue('Duke');

  // Add data source with query parameter.
  var dataSourceSpec = SpreadsheetApp.newDataSourceSpec()
      .asBigQuery()
      .setProjectId('<YOUR_PROJECT_ID>')
      .setRawQuery('select * from `bigquery-public-data`.`ncaa_basketball`.`mbb_historical_tournament_games` WHERE win_school_ncaa = @SCHOOL')
      .setParameterFromCell('SCHOOL', 'parameterSheet!A1')
      .build();
  var dataSourceSheet = spreadsheet.insertDataSourceSheet(dataSourceSpec);
  dataSourceSheet.asSheet().setName('ncaa_data');
}

// Function used to configure event trigger to refresh data source sheet.
function refreshOnParameterEdit(e) {
  var editedRange = e.range;
if (editedRange.getSheet().getName() != 'parameterSheet') {
  return;
}
// Check that the edited range includes A1.
if (editedRange.getRow() > 1 || editedRange.getColumn() > 1) {
   return;
}

  var spreadsheet = e.source;
  SpreadsheetApp.enableBigQueryExecution();
  spreadsheet.getSheetByName('ncaa_data').asDataSourceSheet().refreshData();
}

Dans l'exemple ci-dessus, la fonction addDataSource() ajoute une source de données à la feuille de calcul. Après avoir exécuté addDataSource(), créez un déclencheur d'événement dans l'éditeur Apps Script. Pour savoir comment créer un déclencheur d'événement, consultez la section Déclencheurs installables.

Sélectionnez les options suivantes pour votre déclencheur:

Source de l'événement: À partir d'une feuille de calcul
Type d'événement: Lors d'une modification
Fonction à exécuter: refreshOnParameterEdit

Une fois le déclencheur créé, la feuille de la source de données est automatiquement actualisée chaque fois que la cellule du paramètre est modifiée.

Dépannage

Message d'erreur Résolution

Utilisez enableBigQuery() afin d'activer l'exécution de données pour les sources de données BIGQUERY. Cette erreur indique que SpreadsheetApp.enableBigQueryExecution() n'est pas appelé avant la récupération des données BigQuery.
Appelez SpreadsheetApp.enableBigQueryExecution() dans les fonctions qui utilisent des méthodes d'exécution BigQuery.
Par exemple, refreshData() sur les objets de source de données, Spreadsheet.insertDataSourceTable() et DataSource.updateSpec().
Ces méthodes nécessitent un niveau d'accès OAuth bigquery.readonly supplémentaire pour fonctionner.

Vous n'êtes pas autorisé à agir sur les sources de données.
Veuillez contacter votre administrateur pour qu'il active cette fonctionnalité. Cette erreur indique que les feuilles connectées ne sont pas activées pour ce compte.
Les feuilles connectées ne sont disponibles que pour Google Workspace les utilisateurs ayant certains abonnements.
Contactez votre administrateur pour qu'il active cette fonctionnalité.

Message d'erreur	Résolution
Utilisez `enableBigQuery()` afin d'activer l'exécution de données pour les sources de données BIGQUERY.	Cette erreur indique que `SpreadsheetApp.enableBigQueryExecution()` n'est pas appelé avant la récupération des données BigQuery. Appelez `SpreadsheetApp.enableBigQueryExecution()` dans les fonctions qui utilisent des méthodes d'exécution BigQuery. Par exemple, `refreshData()` sur les objets de source de données, `Spreadsheet.insertDataSourceTable()` et `DataSource.updateSpec()`. Ces méthodes nécessitent un niveau d'accès OAuth bigquery.readonly supplémentaire pour fonctionner.
Vous n'êtes pas autorisé à agir sur les sources de données. Veuillez contacter votre administrateur pour qu'il active cette fonctionnalité.	Cette erreur indique que les feuilles connectées ne sont pas activées pour ce compte. Les feuilles connectées ne sont disponibles que pour Google Workspace les utilisateurs ayant certains abonnements. Contactez votre administrateur pour qu'il active cette fonctionnalité.