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

연결된 시트 사용

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

일반적인 연결된 시트 작업

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

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

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

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

Looker 데이터에 액세스하려면 Google Apps Script 코드에 enableLookerExecution() 메서드를 포함해야 합니다. Apps Script에서 Looker에 액세스하면 Looker와의 기존 Google 계정 연결이 재사용됩니다.

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

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

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

BigQuery와 연결하면 대부분의 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와 Looker 데이터 소스를 각각 추가하는 방법을 보여줍니다.

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

Looker

스프레드시트에 Looker 데이터 소스를 추가하려면 데이터 소스 사양이 있는 데이터 소스 시트를 삽입하세요. 미리보기 데이터를 가져올 수 있도록 데이터 소스 시트가 자동으로 새로고침됩니다.

다음 샘플에서 <INSTANCE_URL>,<MODEL_NAME>, <EXPLORE_NAME>를 각각 유효한 Looker 인스턴스 URL, 모델 이름, 탐색 이름으로 바꿉니다.

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

데이터 소스 객체 추가

데이터 소스가 스프레드시트에 추가되면 데이터 소스에서 데이터 소스 객체를 만들 수 있습니다. 이 예에서는 BigQuery 데이터 소스를 추가하는 코드 샘플에서 만든 BigQuery dataSource에서 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());
}

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

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

다음 샘플은 쿼리 매개변수가 있는 BigQuery 데이터 소스를 추가하고 쿼리 매개변수가 수정되면 데이터 소스 시트를 새로고침합니다.

아래의 <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()를 호출합니다.
예: 데이터 소스 객체의 refreshData(), Spreadsheet.insertDataSourceTable(), DataSource.updateSpec()
이 메서드가 작동하려면 추가 bigquery.readonly OAuth 범위가 필요합니다.

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

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