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 Spreadsheet.

Actions courantes dans les feuilles connectées

Utilisez les classes et les objets DataSource pour vous connecter à BigQuery ou Looker et analyser les données. Le tableau suivant 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 à une source de données compatible	`DataSourceSpec`	`SpreadsheetApp.newDataSourceSpec()`
Choisissez 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 niveaux d'accès des autorisations 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 niveaux d'accès OAuth sont automatiquement ajoutés au fichier manifeste en fonction des fonctions utilisées dans votre code. Si vous avez besoin d'autres niveaux d'accès pour accéder à certaines données BigQuery, vous pouvez définir des niveaux d'accès 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. Il ajoute un champ d'application OAuth Drive en plus des champs d'application OAuth spreadsheet et bigquery.readonly 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

Les exemples suivants montrent comment ajouter une source de données, 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 d'exécution.

Ajouter une source de données

Les exemples suivants montrent comment ajouter une source de données BigQuery et une source de données 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, des objets de source de données peuvent être créés à 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 grille référencées par l'index de cellule ou les notations A1, les données des sources de données sont généralement référencées par nom de colonne. Par conséquent, la plupart des setters de propriété sur les objets de source de données utilisent le nom de 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 extraire les dernières données de BigQuery en fonction des spécifications de la source de données et des configurations des objets.

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'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 d'interroger l'état d'exécution.
DataExecutionStatus.getErrorCode() récupère 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 Google Sheets connecté à 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 lors d'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 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 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 source de données s'actualise automatiquement chaque fois que la cellule de paramètre est modifiée.

Résoudre les problèmes

Message d'erreur Solution

Utilisez enableBigQuery() afin d'autoriser les exécutions 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 champ d'application OAuth bigquery.readonly supplémentaire pour fonctionner.

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 pour le compte.
Les feuilles connectées ne sont disponibles que pour les utilisateurs Google Workspace disposant de certains abonnements.
Contactez votre administrateur pour activer cette fonctionnalité.

Message d'erreur	Solution
Utilisez `enableBigQuery()` afin d'autoriser les exécutions 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 champ d'application OAuth bigquery.readonly supplémentaire pour fonctionner.
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 pour le compte. Les feuilles connectées ne sont disponibles que pour les utilisateurs Google Workspace disposant de certains abonnements. Contactez votre administrateur pour activer cette fonctionnalité.

Utiliser des feuilles connectées Restez organisé à l'aide des collections Enregistrez et classez les contenus selon vos préférences.