Utilizzare Fogli connessi

Fogli connessi è una funzionalità di Fogli Google che consente di analizzare i dati di BigQuery direttamente all'interno di Fogli. Puoi accedere a Fogli connessi in modo programmatico con il servizio Foglio di lavoro.

Azioni comuni di Fogli connessi

Utilizza le classi e gli oggetti DataSource per connetterti a BigQuery e analizzare i dati. Nella tabella seguente sono elencate le azioni DataSource più comuni e come crearle in Apps Script:

Azione lezione Google Apps Script Metodo di utilizzo
Collegare un foglio a BigQuery DataSourceSpec SpreadsheetApp.newDataSourceSpec()
Scegli un'origine dati BigQuery DataSource Spreadsheet.insertDataSourceSheet().getDataSource()
Aggiungi un foglio dell'origine dati BigQuery DataSourceSheet Spreadsheet.insertDataSourceSheet()
Aggiungere una tabella pivot DataSourcePivotTable Range.insertDataSourcePivotTable()
Pull dei dati in un'estrazione DataSourceTable Range.insertDataSourceTable()
Utilizza una formula DataSourceFormula Range.setFormula()
Aggiungi un grafico DataSourceChart Sheet.insertDataSourceChart()

Aggiungi ambiti di autorizzazione richiesti

Per accedere ai dati BigQuery, devi includere il metodo enableBigQueryExecution() nel codice di Google Apps Script. Questo metodo aggiunge l'ambito OAuth bigquery.readonly richiesto al progetto Google Apps Script.

Il seguente esempio mostra il metodo SpreadsheetApp.enableBigQueryExecution() richiamato all'interno di una funzione:

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

Aggiungere altri ambiti OAuth al file manifest

La maggior parte degli ambiti OAuth viene aggiunta automaticamente al file manifest in base alle funzioni utilizzate nel codice. Se hai bisogno di ambiti aggiuntivi per accedere a determinati dati di BigQuery, puoi impostare ambiti espliciti.

Ad esempio, per eseguire query su dati BigQuery ospitati su Google Drive, devi aggiungere un ambito OAuth di Drive al file manifest.

Il seguente esempio mostra la parte oauthScopes di un file manifest. Aggiunge un ambito OAuth di Drive oltre agli ambiti OAuth minimi obbligatori di spreadsheet e bigquery.readonly:

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

Esempio: creare e aggiornare un oggetto dell'origine dati

L'esempio seguente mostra come aggiungere un'origine dati BigQuery, creare un oggetto dell'origine dati dall'origine dati, aggiornare l'oggetto dell'origine dati e ottenere lo stato di esecuzione. In questo esempio, gli snippet di codice vengono eseguiti in sequenza.

Aggiungere un'origine dati BigQuery

Per aggiungere un'origine dati BigQuery a un foglio di lavoro, inserisci un foglio dell'origine dati con la specifica di un'origine dati. Il foglio dell'origine dati viene aggiornato automaticamente per recuperare i dati di anteprima.

Sostituisci <YOUR_PROJECT_ID> di seguito con un ID progetto Google Cloud valido.

// 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();

Aggiungere un oggetto di origine dati

Dopo aver aggiunto l'origine dati al foglio di lavoro, è possibile creare gli oggetti dell'origine dati. In questo esempio viene creata una tabella pivot utilizzando DataSourcePivotTable.

A differenza dei dati normali nei fogli a griglia a cui si fa riferimento tramite l'indice di cella o le annotazioni A1, i dati delle origini dati sono solitamente indicati dai nomi delle colonne. Di conseguenza, la maggior parte dei setter delle proprietà per gli oggetti di origine dati utilizza il nome della colonna come input.

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);

Aggiornare un oggetto di origine dati

Puoi aggiornare gli oggetti dell'origine dati per recuperare i dati più recenti da BigQuery in base alle specifiche dell'origine dati e alle configurazioni degli oggetti.

La procedura di aggiornamento dei dati è asincrona. Per aggiornare un oggetto dell'origine dati, utilizza i seguenti metodi:

  1. refreshData() avvia l'esecuzione dell'aggiornamento dei dati.
  2. waitForCompletion() restituisce lo stato finale al termine dell'esecuzione dei dati. In questo modo non è necessario continuare a eseguire il polling dello stato di esecuzione.
  3. DataExecutionStatus.getErrorCode() riceve il codice di errore nel caso in cui l'esecuzione dei dati non vada a buon fine.

L'esempio seguente illustra un aggiornamento dei dati della tabella pivot:

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());
}

Utilizzare gli attivatori con Fogli connessi

Automatizzare le funzioni dell'origine dati Fogli connessi con attivatori ed eventi. Ad esempio, utilizza gli attivatori basati sul tempo per aggiornare gli oggetti dell'origine dati ripetutamente in un momento specifico e utilizza gli attivatori di eventi del foglio di lavoro per attivare l'esecuzione dei dati in un evento predefinito.

L'esempio riportato di seguito aggiunge un'origine dati con un parametro di query e aggiorna il foglio dell'origine dati quando il parametro di query viene modificato.

Sostituisci <YOUR_PROJECT_ID> di seguito con un ID progetto Google Cloud valido.

// 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();
}

Nell'esempio precedente, la funzione addDataSource() aggiunge un'origine dati al foglio di lavoro. Dopo aver eseguito addDataSource(), crea un attivatore di evento nell'editor di Apps Script. Per scoprire come creare un attivatore di eventi, consulta Trigger installabili.

Seleziona le seguenti opzioni per l'attivatore:

  • Origine evento: Da foglio di lavoro
  • Tipo di evento: Alla modifica
  • Funzione da eseguire: refreshOnParameterEdit

Una volta creato l'attivatore, il foglio dell'origine dati si aggiorna automaticamente ogni volta che la cella del parametro viene modificata.

Risolvi il problema

Messaggio di errore Risoluzione
Utilizza enableBigQuery() per attivare l'esecuzione di dati per le origini dati BIGQUERY. Questo errore indica che SpreadsheetApp.enableBigQueryExecution() non viene chiamato prima del recupero dei dati BigQuery.
Chiama SpreadsheetApp.enableBigQueryExecution() nelle funzioni che utilizzano i metodi per l'esecuzione di BigQuery.
Ad esempio, refreshData() sugli oggetti dell'origine dati, Spreadsheet.insertDataSourceTable() e DataSource.updateSpec().
Questi metodi richiedono un ambito OAuth bigquery.readonly aggiuntivo.
Azioni sulle origini dati non consentite.
Contatta l'amministratore per attivare la funzionalità.		 Questo errore indica che nell'account Fogli connessi non è attivato.
Fogli connessi è disponibile solo per Google Workspace gli utenti con determinati abbonamenti.
Contatta l'amministratore per attivare la funzionalità.