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

Utiliser des feuilles connectées

Les feuilles connectées sont une fonctionnalité Google Sheets qui vous permet d'analyser les données BigQuery et Looker directement dans Sheets. Vous pouvez accéder aux feuilles connectées de manière programmatique avec le service Sheets.

Actions courantes dans Feuilles connectées

Utilisez les classes et objets DataSource pour vous connecter à BigQuery ou à Looker et analyser les données. Le tableau suivant répertorie 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 à une source de données compatible	`DataSourceSpec`	`SpreadsheetApp.newDataSourceSpec()`
Sélectionnez une source de données	`DataSource`	`Spreadsheet.insertDataSourceSheet().getDataSource()`
Ajouter une feuille de source de données	`DataSourceSheet`	`Spreadsheet.insertDataSourceSheet()`
Ajouter un tableau croisé dynamique	`DataSourcePivotTable`	`Range.insertDataSourcePivotTable()`
Insérer 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();
  }

Pour accéder aux données Looker, vous devez inclure la méthode enableLookerExecution() dans votre code Google Apps Script. L'accès à Looker dans Apps Script réutilisera votre association de compte Google existante avec Looker.

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

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

Ajouter des habilitations OAuth supplémentaires au fichier manifeste

Lorsque vous vous connectez à BigQuery, 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 portées supplémentaires pour accéder à certaines données BigQuery, vous pouvez définir des portées explicites.

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

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

{ ...
  "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

Les exemples suivants montrent comment ajouter une source de données, créer un objet source de données à partir de la source de données, actualiser l'objet source de données et obtenir l'état d'exécution.

Add a data source

Les exemples suivants montrent comment ajouter une source de données BigQuery et Looker, respectivement.

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 automatiquement actualisée 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();

Looker

Pour ajouter une source de données Looker à 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 automatiquement actualisée pour récupérer les données d'aperçu.

Remplacez <INSTANCE_URL>,<MODEL_NAME> et <EXPLORE_NAME> dans l'exemple suivant par une URL d'instance Looker, un nom de modèle et un nom d'exploration valides, respectivement.

// For operations that fetch data from Looker, enableLookerExecution() must be called.
SpreadsheetApp.enableLookerExecution();
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()
    .asLooker()
    .setInstanceUrl('<INSTANCE_URL>')
    .setModelName('<MODEL_NAME>')
    .setExploreName('<EXPLORE_NAME>')
    .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 la source de données. Dans cet exemple, un tableau croisé dynamique est créé à l'aide de DataSourcePivotTable sur le dataSource BigQuery créé dans l'exemple de code qui ajoute une source de données BigQuery.

Contrairement aux données standards des feuilles de calcul qui sont référencées par l'indice de la cellule ou les notations A1, les données des sources de données sont généralement référencées par des noms de colonnes. Par conséquent, la plupart des setters de propriété sur les objets de source de données utilisent le nom de la colonne comme 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 source 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'objets.

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

refreshData() lance l'exécution de l'actualisation des données.
waitForCompletion() renvoie l'état final une fois l'exécution des données terminée. Il n'est donc plus nécessaire de continuer à interroger l'état de l'exécution.
DataExecutionStatus.getErrorCode() obtient le code d'erreur en cas d'échec de l'exécution des données.

L'exemple ci-dessous illustre l'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 les feuilles connectées

Automatisez les fonctions de votre source de données Sheets connectée à 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 de manière répétée à une heure spécifique, et utilisez des déclencheurs d'événement de feuille de calcul pour déclencher l'exécution des données sur un événement prédéfini.

L'exemple suivant ajoute une source de données BigQuery 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 s'actualise automatiquement chaque fois que la cellule du paramètre est modifiée.

Résoudre les problèmes

Message d'erreur Solution

Utilisez enableBigQuery() pour activer les exécutions de données pour les sources de données BIGQUERY. Cette erreur indique que SpreadsheetApp.enableBigQueryExecution() n'est pas appelé avant d'extraire les données BigQuery.
Appelez SpreadsheetApp.enableBigQueryExecution() dans les fonctions qui utilisent des méthodes pour l'exécution de BigQuery.
Par exemple, refreshData() sur les objets de source de données, Spreadsheet.insertDataSourceTable() et DataSource.updateSpec().
Pour fonctionner, ces méthodes nécessitent une habilitation OAuth bigquery.readonly supplémentaire.

Vous n'êtes pas autorisé à intervenir sur les sources de données.
Veuillez contacter votre administrateur pour activer cette fonctionnalité. Cette erreur indique que les feuilles connectées ne sont pas activées dans le compte.
Les feuilles connectées ne sont disponibles que pour les Google Workspace utilisateurs disposant de certains abonnements.
Contactez votre administrateur pour activer cette fonctionnalité.

Message d'erreur	Solution
Utilisez `enableBigQuery()` pour activer les exécutions de données pour les sources de données BIGQUERY.	Cette erreur indique que `SpreadsheetApp.enableBigQueryExecution()` n'est pas appelé avant d'extraire les données BigQuery. Appelez `SpreadsheetApp.enableBigQueryExecution()` dans les fonctions qui utilisent des méthodes pour l'exécution de BigQuery. Par exemple, `refreshData()` sur les objets de source de données, `Spreadsheet.insertDataSourceTable()` et `DataSource.updateSpec()`. Pour fonctionner, ces méthodes nécessitent une habilitation OAuth bigquery.readonly supplémentaire.
Vous n'êtes pas autorisé à intervenir sur les sources de données. Veuillez contacter votre administrateur pour activer cette fonctionnalité.	Cette erreur indique que les feuilles connectées ne sont pas activées dans le compte. Les feuilles connectées ne sont disponibles que pour les Google Workspace utilisateurs disposant de certains abonnements. Contactez votre administrateur pour activer cette fonctionnalité.