Feuilles connectées

Les feuilles connectées vous permettent d'analyser des pétaoctets de données directement dans Google Sheets. Vous pouvez connecter vos feuilles de calcul à un entrepôt de données BigQuery et effectuer l'analyse à l'aide des outils Sheets que vous connaissez, tels que les tableaux croisés dynamiques, les graphiques et les formules.

Ce guide utilise l'ensemble de données public Shakespeare pour montrer comment utiliser les feuilles connectées. L'ensemble de données contient les informations suivantes:

Champ Type Description
mot STRING Mot unique (où l'espace est le délimiteur) extrait d'un corpus.
word_count INTEGER Nombre de fois où ce mot apparaît dans le corpus.
corpus STRING Œuvre dont ce mot est extrait.
corpus_date INTEGER Année de publication de ce corpus.

Si votre application demande des données de feuilles connectées, elle doit fournir un jeton OAuth 2.0 qui accorde le champ d'application bigquery.readonly, en plus des autres champs d'application requis pour une requête API Google Sheets standard. Pour en savoir plus, consultez Choisir les champs d'application des API Google Sheets.

Gérer une source de données

Une source de données spécifie l'emplacement externe où se trouvent les données. La source de données est ensuite connectée à la feuille de calcul.

Ajouter une source de données BigQuery

Pour ajouter une source de données, fournissez un AddDataSourceRequest à l'aide de la méthode spreadsheets.batchUpdate. Le corps de la requête doit spécifier un champ dataSource de type DataSource.

"addDataSource":{
   "dataSource":{
      "spec":{
         "bigQuery":{
            "projectId":"PROJECT_ID",
            "tableSpec":{
               "tableProjectId":"bigquery-public-data",
               "datasetId":"samples",
               "tableId":"shakespeare"
            }
         }
      }
   }
}

Remplacez PROJECT_ID par un ID de projet Google Cloud valide.

Une fois la source de données créée, une feuille DATA_SOURCE associée est créée pour fournir un aperçu de 500 lignes maximum. L'aperçu n'est pas disponible immédiatement. Une exécution est déclenchée de manière asynchrone pour importer les données BigQuery.

Le AddDataSourceResponse contient les champs suivants:

  • dataSource: objet DataSource créé. Le dataSourceId est un identifiant unique à l'échelle d'une feuille de calcul. Il est renseigné et référencé pour créer chaque objet DataSource à partir de la source de données.

  • dataExecutionStatus: état d'une exécution qui importe les données BigQuery dans la feuille d'aperçu. Pour en savoir plus, consultez la section État d'exécution des données.

Mettre à jour ou supprimer une source de données

Utilisez la méthode spreadsheets.batchUpdate et fournissez une requête UpdateDataSourceRequest ou DeleteDataSourceRequest en conséquence.

Gérer les objets de source de données

Une fois qu'une source de données est ajoutée à la feuille de calcul, un objet de source de données peut être créé à partir de celle-ci. Un objet de source de données est un outil Sheets standard tel que des tableaux croisés dynamiques, des graphiques et des formules, qui est intégré aux feuilles connectées pour alimenter votre analyse de données.

Il existe quatre types d'objets:

  • DataSource table
  • DataSource pivotTable
  • Graphique "DataSource"
  • Formule DataSource

Ajouter une table de source de données

Connu sous le nom d'"extraction" dans l'éditeur Sheets, l'objet table importe un vidage statique des données de la source de données dans Sheets. Comme pour un tableau croisé dynamique, le tableau est spécifié et ancré à la cellule supérieure gauche.

L'exemple de code suivant montre comment utiliser la méthode spreadsheets.batchUpdate et un UpdateCellsRequest pour créer une table de source de données comportant jusqu'à 1 000 lignes de deux colonnes (word et word_count).

"updateCells":{
   "rows":{
      "values":[
         {
            "dataSourceTable":{
               "dataSourceId":"DATA_SOURCE_ID",
               "columns":[
                  {
                     "name":"word"
                  },
                  {
                     "name":"word_count"
                  }
               ],
               "rowLimit":{
                  "value":1000
               },
               "columnSelectionType":"SELECTED"
            }
         }
      ]
   },
   "fields":"dataSourceTable"
}

Remplacez DATA_SOURCE_ID par un identifiant unique à l'échelle de la feuille de calcul qui identifie la source de données.

Une fois la table de source de données créée, les données ne sont pas disponibles immédiatement. Dans l'éditeur Sheets, elle s'affiche en tant qu'aperçu. Vous devez actualiser la table de la source de données pour extraire les données BigQuery. Vous pouvez spécifier un élément RefreshDataSourceRequest dans le même élément batchUpdate. Notez que tous les objets de source de données fonctionnent de la même manière. Pour en savoir plus, consultez Actualiser un objet de source de données.

Une fois l'actualisation terminée et les données BigQuery récupérées, la table de la source de données est renseignée comme suit:

Table de la source de données affichant les données de l'ensemble de données public Shakespeare.

Ajouter un tableau croisé dynamique de source de données

Contrairement à un tableau croisé dynamique classique, un tableau croisé dynamique de source de données repose sur une source de données et référence les données par nom de colonne. L'exemple de code suivant montre comment utiliser la méthode spreadsheets.batchUpdate et un UpdateCellsRequest pour créer un tableau croisé dynamique indiquant le nombre total de mots par corpus.

"updateCells":{
   "rows":{
      "values":[
         {
            "pivotTable":{
               "dataSourceId":"DATA_SOURCE_ID",
               "rows":{
                  "dataSourceColumnReference":{
                     "name":"corpus"
                  },
                  "sortOrder":"ASCENDING"
               },
               "values":{
                  "summarizeFunction":"SUM",
                  "dataSourceColumnReference":{
                     "name":"word_count"
                  }
               }
            }
         }
      ]
   },
   "fields":"pivotTable"
    }

Remplacez DATA_SOURCE_ID par un identifiant unique à l'échelle de la feuille de calcul qui identifie la source de données.

Une fois les données BigQuery récupérées, le tableau croisé dynamique de la source de données est renseigné comme suit:

Tableau croisé dynamique d'une source de données affichant les données de l'ensemble de données public Shakespeare

Ajouter un graphique de source de données

L'exemple de code suivant montre comment utiliser la méthode spreadsheets.batchUpdate et un AddChartRequest pour créer un graphique de source de données avec un chartType de COLONNE, indiquant le nombre total de mots par corpus.

"addChart":{
   "chart":{
      "spec":{
         "title":"Corpus by word count",
         "basicChart":{
            "chartType":"COLUMN",
            "domains":[
               {
                  "domain":{
                     "columnReference":{
                        "name":"corpus"
                     }
                  }
               }
            ],
            "series":[
               {
                  "series":{
                     "columnReference":{
                        "name":"word_count"
                     },
                     "aggregateType":"SUM"
                  }
               }
            ]
         }
      },
      "dataSourceChartProperties":{
         "dataSourceId":"DATA_SOURCE_ID"
      }
   }
}

Remplacez DATA_SOURCE_ID par un identifiant unique à l'échelle de la feuille de calcul qui identifie la source de données.

Une fois les données BigQuery récupérées, le graphique de la source de données s'affiche comme suit:

Graphique de source de données affichant les données de l'ensemble de données public Shakespeare

Ajouter une formule de source de données

L'exemple de code suivant montre comment utiliser la méthode spreadsheets.batchUpdate et un UpdateCellsRequest pour créer une formule de source de données permettant de calculer le nombre moyen de mots.

"updateCells":{
   "rows":[
      {
         "values":[
            {
               "userEnteredValue":{
                  "formulaValue":"=AVERAGE(shakespeare!word_count)"
               }
            }
         ]
      }
   ],
   "fields":"userEnteredValue"
}

Une fois les données BigQuery récupérées, la formule de la source de données est renseignée comme suit:

Formule de source de données affichant les données de l'ensemble de données public Shakespeare.

Actualiser un objet de source de données

Vous pouvez actualiser un objet de source de données pour extraire les dernières données de BigQuery en fonction des spécifications actuelles de la source de données et des configurations d'objet. Vous pouvez utiliser la méthode spreadsheets.batchUpdate pour appeler RefreshDataSourceRequest. Spécifiez ensuite une ou plusieurs références d'objets à actualiser à l'aide de l'objet DataSourceObjectReferences.

Notez que vous pouvez à la fois créer et actualiser des objets de source de données dans une seule requête batchUpdate.

État d'exécution des données

Lorsque vous créez des sources de données ou actualisez des objets de source de données, une exécution en arrière-plan est créée pour extraire les données de BigQuery et renvoyer une réponse contenant le DataExecutionStatus. Si l'exécution démarre correctement, DataExecutionState est généralement à l'état RUNNING.

Le processus étant asynchrone, votre application doit mettre en œuvre un modèle d'interrogation pour récupérer régulièrement l'état des objets de source de données. Utilisez la méthode spreadsheets.get jusqu'à ce que l'état renvoie l'état SUCCEEDED ou FAILED. Dans la plupart des cas, l'exécution s'effectue rapidement, mais elle dépend de la complexité de votre source de données. En règle générale, l'exécution ne dépasse pas 10 minutes.