Эта страница переведена с помощью Cloud Translation API.

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

Подключённые таблицы — это функция Google Таблиц, которая позволяет анализировать данные BigQuery и Looker непосредственно в Таблицах. Доступ к подключённым таблицам осуществляется программно с помощью сервиса электронных таблиц.

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

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

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

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

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

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

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

Для доступа к данным Looker необходимо включить метод enableLookerExecution() в код скрипта Google Apps. Доступ к Looker в скрипте Apps Script позволит повторно использовать существующую связь с аккаунтом Google в Looker.

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

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

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

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

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

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

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

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

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

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

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

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

Красавчик

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

Замените <INSTANCE_URL> , <MODEL_NAME> , <EXPLORE_NAME> в следующем примере на действительный URL-адрес экземпляра Looker, имя модели и имя исследования соответственно.

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

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

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

В отличие от обычных данных в таблицах, на которые ссылаются по индексу ячейки или нотации 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 на основе спецификаций источника данных и конфигураций объектов.

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

refreshData() запускает выполнение обновления данных.
waitForCompletion() возвращает конечное состояние после завершения обработки данных. Это устраняет необходимость постоянного опроса статуса выполнения.
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 с помощью триггеров и событий . Например, используйте триггеры, управляемые по времени, для периодического обновления объектов источников данных в определённое время, а также триггеры событий электронных таблиц для запуска обработки данных при наступлении предопределённого события.

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

Замените <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() создайте триггер события в редакторе Apps Script. Чтобы узнать, как создать триггер события, см. раздел Устанавливаемые триггеры .

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

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

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

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

Сообщение об ошибке Разрешение

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

Не разрешено действовать на основе источников данных.
Чтобы включить эту функцию, обратитесь к администратору. Эта ошибка означает, что в учетной записи не включены подключенные таблицы.
Подключенные таблицы доступны только пользователям Google Workspace с определенными подписками.
Чтобы включить эту функцию, обратитесь к администратору.

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

Использовать связанные листы Оптимизируйте свои подборки Сохраняйте и классифицируйте контент в соответствии со своими настройками.