Utiliser des feuilles connectées

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

Actions courantes avec 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 répertorie les actions DataSource les plus courantes et explique comment les créer dans Google Apps Script :

Action	Classe Apps Script	Méthode à utiliser
Connecter une feuille à une source de données compatible	`DataSourceSpec`	`SpreadsheetApp.newDataSourceSpec()`
Choisir 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()`
Extraire des données dans un extrait	`DataSourceTable`	`Range.insertDataSourceTable()`
Utiliser une formule	`DataSourceFormula`	`Range.setFormula()`
Ajouter un graphique	`DataSourceChart`	`Sheet.insertDataSourceChart()`

Ajouter les niveaux d'accès d'autorisation requis

Pour accéder aux données BigQuery, incluez la méthode enableBigQueryExecution() dans votre code Apps Script. Cette méthode ajoute le niveau d'accès OAuth bigquery.readonly requis à votre projet 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, incluez la méthode enableLookerExecution() dans votre code Apps Script. L'accès à Looker dans Apps Script réutilise 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 niveaux d'accès 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 de niveaux d'accès supplémentaires 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 niveau d'accès OAuth Drive à votre fichier manifeste.

L'exemple suivant montre la partie oauthScopes d'un fichier manifeste. Il ajoute un niveau d'accès OAuth Drive en plus des niveaux d'accès 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

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 respectivement une source de données BigQuery et une source de données Looker.

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>, <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 BigQuery dataSource 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 provenant de 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és 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

Actualisez 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 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() démarre 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() obtient le code d'erreur en cas d'échec de l'exécution des données.

L'exemple suivant 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 les feuilles connectées

Automatisez les fonctions de source de données de vos feuilles connectées à l'aide de déclencheurs et d'événements. Par exemple, utilisez des déclencheurs temporels pour actualiser les objets de source de données de manière répétée à un moment spécifique, 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 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> 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 précédent, 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 de script 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 de la 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() 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 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é à 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 de Google Workspace 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 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é à 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 de 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.