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

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

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

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

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

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

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

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

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

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

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

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

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