Utilizzare Fogli connessi

Fogli connessi è una funzionalità di Fogli Google che ti consente di analizzare i dati di BigQuery e Looker direttamente in Fogli. Accedi a Fogli connessi a livello di programmazione con il servizio Fogli di lavoro.

Azioni comuni di Fogli connessi

Utilizza le classi e gli oggetti DataSource per connetterti a BigQuery o Looker e analizzare i dati. La tabella seguente elenca le azioni DataSource più comuni e come crearle in Google Apps Script:

Azione	Classe Apps Script	Metodo da utilizzare
Connetti un foglio a un'origine dati supportata	`DataSourceSpec`	`SpreadsheetApp.newDataSourceSpec()`
Scegli un'origine dati	`DataSource`	`Spreadsheet.insertDataSourceSheet().getDataSource()`
Aggiungi un foglio di origine dati	`DataSourceSheet`	`Spreadsheet.insertDataSourceSheet()`
Aggiungi una tabella pivot	`DataSourcePivotTable`	`Range.insertDataSourcePivotTable()`
Estrai i dati in un'estrazione	`DataSourceTable`	`Range.insertDataSourceTable()`
Utilizza una formula	`DataSourceFormula`	`Range.setFormula()`
Aggiungi un grafico	`DataSourceChart`	`Sheet.insertDataSourceChart()`

Aggiungi gli ambiti di autorizzazione richiesti

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

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

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

Per accedere ai dati Looker, includi il metodo enableLookerExecution() nel codice Apps Script. L'accesso a Looker in Apps Script riutilizza il collegamento esistente dell'Account Google con Looker.

L'esempio seguente mostra il metodo SpreadsheetApp.enableLookerExecution() chiamato all'interno di una funzione:

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

Aggiungi altri ambiti OAuth al file manifest

Quando ti connetti a BigQuery, 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 BigQuery, puoi impostare ambiti espliciti.

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

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

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

Esempio: crea e aggiorna un oggetto di origine dati

Gli esempi seguenti mostrano come aggiungere un'origine dati, creare un oggetto di origine dati dall'origine dati, aggiornare l'oggetto di origine dati e ottenere lo stato di esecuzione.

Aggiungi un'origine dati

Gli esempi seguenti mostrano come aggiungere rispettivamente un'origine dati BigQuery e un'origine dati Looker.

BigQuery

Per aggiungere un'origine dati BigQuery a un foglio di lavoro, inserisci un foglio di origine dati con una specifica di origine dati. Il foglio di 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();

Looker

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

Sostituisci <INSTANCE_URL>,<MODEL_NAME>, <EXPLORE_NAME> nell'esempio seguente con un URL dell'istanza di Looker, un nome del modello e un nome di esplorazione validi, rispettivamente.

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

Aggiungi un oggetto di origine dati

Una volta aggiunta l'origine dati al foglio di lavoro, è possibile creare oggetti di origine dati dall'origine dati. In questo esempio, viene creata una tabella pivot utilizzando DataSourcePivotTable sull'oggetto BigQuery dataSource creato in nell'esempio di codice che aggiunge un'origine dati BigQuery.

A differenza dei dati normali nei fogli a griglia a cui si fa riferimento tramite l'indice delle celle o le notazioni A1, i dati delle origini dati vengono in genere indicati tramite i nomi delle colonne. Pertanto, la maggior parte dei setter di proprietà sugli 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);

Aggiorna un oggetto di origine dati

Aggiorna gli oggetti di 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 di origine dati, utilizza i seguenti metodi:

refreshData() avvia l'esecuzione dell'aggiornamento dei dati.
waitForCompletion() restituisce lo stato finale al termine dell'esecuzione dei dati. In questo modo non è necessario eseguire il polling continuo dello stato di esecuzione.
DataExecutionStatus.getErrorCode() recupera 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());
}

Utilizza i trigger con Fogli connessi

Automatizza le funzioni dell'origine dati di Fogli connessi con trigger ed eventi. Ad esempio, utilizza i trigger basati sul tempo per aggiornare ripetutamente gli oggetti di origine dati a un'ora specifica e utilizza i trigger di eventi del foglio di lavoro per attivare l'esecuzione dei dati in un evento predefinito.

L'esempio seguente aggiunge un'origine dati BigQuery con un parametro di query e aggiorna il foglio di origine dati quando il parametro di query viene modificato.

Sostituisci <YOUR_PROJECT_ID> 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 eventi nell'editor di Apps Script. Per scoprire come creare un trigger di eventi, consulta Trigger installabili.

Seleziona le seguenti opzioni per il trigger:

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

Una volta creato il trigger, il foglio di origine dati viene aggiornato automaticamente ogni volta che la cella del parametro viene modificata.

Risoluzione dei problemi

Messaggio di errore Risoluzione

Utilizza enableBigQuery() per attivare l'esecuzione dei dati per le origini dati BIGQUERY. Questo errore indica che SpreadsheetApp.enableBigQueryExecution() non viene chiamato prima di recuperare i dati BigQuery.
Chiama SpreadsheetApp.enableBigQueryExecution() nelle funzioni che utilizzano i metodi per l'esecuzione di BigQuery.
Ad esempio, refreshData() sugli oggetti di origine dati, Spreadsheet.insertDataSourceTable(), e DataSource.updateSpec().
Questi metodi richiedono un ambito OAuth bigquery.readonly aggiuntivo per funzionare.

Azioni sulle origini dati non consentite.
Contatta l'amministratore per attivare la funzionalità. Questo errore indica che l'account non ha Fogli connessi abilitato.
Fogli connessi è disponibile solo per gli utenti di Google Workspace con determinati abbonamenti.
Contatta l'amministratore per attivare la funzionalità.

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

Utilizzare Fogli connessi Mantieni tutto organizzato con le raccolte Salva e classifica i contenuti in base alle tue preferenze.