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

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

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

Действия общих связанных таблиц

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

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

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

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

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

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

Для доступа к данным Looker добавьте метод enableLookerExecution() в код Apps Script. Доступ к 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 файла манифеста. Она добавляет область действия Drive OAuth в дополнение к минимально необходимым областям действия spreadsheet и bigquery.readonly OAuth:

{ ...
  "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() .
Для работы этих методов требуется дополнительная область действия OAuth bigquery.readonly.

Запрещено действовать на основе данных из различных источников.
Для включения этой функции обратитесь к администратору. Эта ошибка указывает на то, что для учетной записи не включена функция «Подключенные таблицы».
Функция Connected Sheets доступна только пользователям Google Workspace с определенными подписками.
Для включения этой функции обратитесь к администратору.

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

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