이 페이지는 Cloud Translation API를 통해 번역되었습니다.

연결된 시트 사용

연결된 시트는 Sheets 내에서 바로 BigQuery 데이터를 분석할 수 있는 Google Sheets 기능입니다. 스프레드시트 서비스를 사용하여 연결된 시트에 프로그래매틱 방식으로 액세스할 수 있습니다.

연결된 시트의 일반적인 작업

DataSource 클래스와 객체를 사용하여 BigQuery에 연결하고 데이터를 분석합니다. 아래 표에는 가장 일반적인 DataSource 작업과 Apps Script에서 이러한 작업을 만드는 방법이 나와 있습니다.

작업	Google Apps Script 클래스	사용할 메서드
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 데이터에 액세스하려면 Google Apps Script 코드에 enableBigQueryExecution() 메서드를 포함해야 합니다. 이 방법을 사용하면 필요한 bigquery.readonly OAuth 범위가 Google Apps Script 프로젝트에 추가됩니다.

다음 샘플은 함수 내에서 호출되는 SpreadsheetApp.enableBigQueryExecution() 메서드를 보여줍니다.

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

매니페스트 파일에 OAuth 범위 추가

대부분의 OAuth 범위는 코드에 사용된 함수에 따라 매니페스트 파일에 자동으로 추가됩니다. 특정 BigQuery 데이터에 액세스하기 위해 추가 범위가 필요한 경우 명시적 범위를 설정할 수 있습니다.

예를 들어 Google Drive 내에서 호스팅되는 BigQuery 데이터를 쿼리하려면 매니페스트 파일에 Drive OAuth 범위를 추가해야 합니다.

다음 샘플은 매니페스트 파일의 oauthScopes 부분을 보여줍니다. 필요한 최소 spreadsheet 및 bigquery.readonly OAuth 범위 외에 드라이브 OAuth 범위를 추가합니다.

{ ...
  "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 프로젝트 ID로 바꿉니다.

// 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 표기법으로 참조되는 그리드 시트의 일반 데이터와 달리 데이터 소스의 데이터는 일반적으로 열 이름별로 참조됩니다. 따라서 데이터 소스 객체의 속성 setter는 대부분 열 이름을 입력으로 사용합니다.

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

연결된 시트에서 트리거 사용하기

트리거 및 이벤트를 사용하여 연결된 시트 데이터 소스 함수를 자동화합니다. 예를 들어 시간 기반 트리거를 사용하여 특정 시간에 데이터 소스 객체를 반복적으로 새로고침하고, 스프레드시트 이벤트 트리거를 사용하여 사전 정의된 이벤트에서 데이터 실행을 트리거합니다.

아래 샘플은 쿼리 매개변수와 함께 데이터 소스를 추가하고 쿼리 매개변수가 수정되면 데이터 소스 시트를 새로고침합니다.

아래의 <YOUR_PROJECT_ID>를 유효한 Google Cloud 프로젝트 ID로 바꿉니다.

// 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 데이터 소스에 대한 데이터 실행을 사용 설정합니다. 이 오류는 BigQuery 데이터를 가져오기 전에 SpreadsheetApp.enableBigQueryExecution()가 호출되지 않았음을 나타냅니다.
BigQuery 실행에 메서드를 사용하는 함수에서 SpreadsheetApp.enableBigQueryExecution()를 호출합니다.
예: 데이터 소스 객체 Spreadsheet.insertDataSourceTable(), DataSource.updateSpec()의 refreshData().
이 메서드가 작동하려면 추가 bigquery.readonly OAuth 범위가 필요합니다.

데이터 소스에 대한 작업이 허용되지 않습니다.
기능을 사용 설정하려면 관리자에게 문의하세요. 이 오류는 해당 계정에서 '연결된 시트'가 사용 설정되지 않았음을 나타냅니다.
연결된 시트는 특정 구독 중인 Google Workspace 사용자만 사용할 수 있습니다.
기능을 사용 설정하려면 관리자에게 문의하세요.

오류 메시지	해상도
`enableBigQuery()`를 사용하여 BIGQUERY 데이터 소스에 대한 데이터 실행을 사용 설정합니다.	이 오류는 BigQuery 데이터를 가져오기 전에 `SpreadsheetApp.enableBigQueryExecution()`가 호출되지 않았음을 나타냅니다. BigQuery 실행에 메서드를 사용하는 함수에서 `SpreadsheetApp.enableBigQueryExecution()`를 호출합니다. 예: 데이터 소스 객체 `Spreadsheet.insertDataSourceTable()`, `DataSource.updateSpec()`의 `refreshData()`. 이 메서드가 작동하려면 추가 bigquery.readonly OAuth 범위가 필요합니다.
데이터 소스에 대한 작업이 허용되지 않습니다. 기능을 사용 설정하려면 관리자에게 문의하세요.	이 오류는 해당 계정에서 '연결된 시트'가 사용 설정되지 않았음을 나타냅니다. 연결된 시트는 특정 구독 중인 Google Workspace 사용자만 사용할 수 있습니다. 기능을 사용 설정하려면 관리자에게 문의하세요.