Использовать связанные листы

Connected Sheets – это функция Google Таблиц, которая позволяет анализировать данные BigQuery непосредственно в Таблицах. Вы можете получить доступ к подключенным таблицам программным способом с помощью службы электронных таблиц.

Распространенные действия с подключенными таблицами

Используйте классы и объекты DataSource для подключения к BigQuery и анализа данных. В таблице ниже перечислены наиболее распространенные действия DataSource и способы их создания в Apps Script.

Действие Класс сценариев Google Apps Метод использования
Подключите таблицу к BigQuery DataSourceSpec SpreadsheetApp.newDataSourceSpec()
Выберите источник данных BigQuery DataSource Spreadsheet.insertDataSourceSheet().getDataSource()
Добавьте таблицу источника данных BigQuery. DataSourceSheet Spreadsheet.insertDataSourceSheet()
Добавьте сводную таблицу DataSourcePivotTable Range.insertDataSourcePivotTable()
Извлечение данных в экстракт DataSourceTable Range.insertDataSourceTable()
Используйте формулу DataSourceFormula Range.setFormula()
Добавить диаграмму DataSourceChart Sheet.insertDataSourceChart()

Добавьте необходимые области авторизации

Чтобы получить доступ к данным BigQuery, необходимо включить метод enableBigQueryExecution() в код скрипта Google Apps. Этот метод добавляет необходимую область OAuth bigquery.readonly в ваш проект Google Apps Script.

В следующем примере показан метод SpreadsheetApp.enableBigQueryExecution() , вызываемый внутри функции:

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

Добавьте дополнительные области OAuth в файл манифеста.

Большинство областей OAuth автоматически добавляются в файл манифеста на основе функций, используемых в вашем коде. Если вам нужны дополнительные области действия для доступа к определенным данным BigQuery, вы можете указать явные области действия .

Например, чтобы запросить данные BigQuery, размещенные на Google Диске , вам необходимо добавить область действия Drive OAuth в файл манифеста.

В следующем примере показана часть oauthScopes файла манифеста. Он добавляет область OAuth для диска в дополнение к минимально необходимой spreadsheet и областям OAuth bigquery.readonly :

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

Пример. Создание и обновление объекта источника данных.

В следующем примере показано, как добавить источник данных BigQuery, создать объект источника данных из источника данных, обновить объект источника данных и получить статус выполнения. В этом примере фрагменты кода выполняются последовательно.

Добавьте источник данных BigQuery

Чтобы добавить источник данных BigQuery в электронную таблицу, вставьте лист источника данных со спецификацией источника данных. Лист источника данных автоматически обновляется для получения данных предварительного просмотра.

Замените <YOUR_PROJECT_ID> ниже действительным идентификатором проекта Google Cloud.

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

Добавьте объект источника данных

После добавления источника данных в электронную таблицу на основе источника данных можно создавать объекты источника данных. В этом примере сводная таблица создается с помощью DataSourcePivotTable .

В отличие от обычных данных в таблицах сетки, на которые ссылаются индексы ячеек или нотации A1, на данные из источников данных обычно ссылаются имена столбцов. Поэтому большинство установщиков свойств объектов источника данных используют имя столбца в качестве входных данных.

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

Обновление объекта источника данных

Вы можете обновить объекты источников данных, чтобы получить последние данные из BigQuery на основе спецификаций источника данных и конфигураций объектов.

Процесс обновления данных является асинхронным. Чтобы обновить объект источника данных, используйте следующие методы:

  1. refreshData() запускает выполнение обновления данных.
  2. waitForCompletion() возвращает конечное состояние после завершения выполнения данных. Это устраняет необходимость постоянного опроса статуса выполнения.
  3. DataExecutionStatus.getErrorCode() получает код ошибки в случае сбоя выполнения данных.

В приведенном ниже примере показано обновление данных сводной таблицы:

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

Используйте триггеры с подключенными таблицами

Автоматизируйте функции источников данных Connected Sheets с помощью триггеров и событий . Например, используйте триггеры, управляемые по времени, для многократного обновления объектов источника данных в определенное время, а также используйте триггеры событий электронной таблицы, чтобы инициировать выполнение данных по предопределенному событию.

В приведенном ниже примере добавляется источник данных с параметром запроса и обновляется лист источника данных при редактировании параметра запроса.

Замените <YOUR_PROJECT_ID> ниже действительным идентификатором проекта Google Cloud.

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

В приведенном выше примере функция addDataSource() добавляет источник данных в электронную таблицу. После выполнения addDataSource() создайте триггер событий в редакторе сценариев приложений. Чтобы узнать, как создать триггер события, см. раздел «Устанавливаемые триггеры» .

Выберите следующие параметры для триггера:

  • Источник события : Из электронной таблицы.
  • Тип события : При редактировании
  • Функция для запуска : refreshOnParameterEdit

После создания триггера лист источника данных автоматически обновляется каждый раз при редактировании ячейки параметра.

Устранение неполадок

Сообщение об ошибке Разрешение
Используйте enableBigQuery() , чтобы включить выполнение данных для источников данных BIGQUERY. Эта ошибка указывает на то, что SpreadsheetApp.enableBigQueryExecution() не вызывается перед получением данных BigQuery.
Вызовите SpreadsheetApp.enableBigQueryExecution() в функциях, которые используют методы для выполнения BigQuery.
Например, refreshData() для объектов источника данных, Spreadsheet.insertDataSourceTable() и DataSource.updateSpec() .
Для работы этих методов требуется дополнительная область OAuth bigquery.readonly.
Запрещено действовать с источниками данных.
Пожалуйста, обратитесь к своему администратору, чтобы включить эту функцию.		 Эта ошибка означает, что в учетной записи не включены подключенные таблицы.
Подключенные таблицы доступны только пользователям Google Workspace с определенными подписками.
Обратитесь к администратору, чтобы включить эту функцию.