Google Sheets와 Slides를 사용하여 빅데이터에서 유용한 정보 도출하기

1. 빅데이터 분석부터 슬라이드 프레젠테이션까지

데이터 과학자가 빅데이터 분석을 수행할 수 있는 도구는 많지만 결국 분석가는 경영진에게 결과를 정당화해야 합니다. 종이나 데이터베이스에 있는 많은 숫자는 주요 이해관계자에게 거의 표시되지 않습니다. 이 중급 Google Apps Script Codelab에서는 Google Workspace와 Google Cloud 콘솔이라는 두 가지 Google 개발자 플랫폼을 사용하여 마지막 단계를 자동화할 수 있습니다.

Google Cloud의 개발자 도구를 사용하면 심층적인 데이터 분석을 실행할 수 있습니다. 그런 다음 결과를 가져와 스프레드시트에 삽입하고 데이터로 슬라이드 프레젠테이션을 생성할 수 있습니다. 이렇게 하면 관리팀에 데이터를 더 적절하게 제공할 수 있습니다. 이 Codelab에서는 Cloud Console의 BigQuery API (Apps Script 고급 서비스)와 Google Sheets 및 Google Slides용 기본 제공 Apps Script 서비스를 다룹니다.

동기

이 Codelab의 샘플 앱은 다음 코드 샘플에서 영감을 받았습니다.

GitHub에서 오픈소스로 제공되는 Google Apps Script BigQuery 서비스 샘플 앱
스프레드시트 데이터에서 슬라이드 생성 개발자 동영상에 소개되고 이 블로그 게시물에 게시된 샘플 앱
Google Slides API Codelab에 소개된 샘플 앱입니다.

Slides API Codelab 샘플 앱에도 BigQuery와 Slides가 포함되어 있지만 다음과 같은 여러 면에서 이 Codelab의 샘플 앱과 다릅니다.

Node.js 앱과 Apps Script 앱의 차이점
이들은 REST API를 사용하고 Google은 Apps Script 서비스를 사용합니다.
이 앱은 Sheets를 사용하지만 Drive는 사용하지 않는 반면, 이 앱은 Google Drive를 사용하지만 Google Sheets는 사용하지 않습니다.

이 Codelab에서는 실제 사용 사례와 유사한 방식으로 Google Cloud 전반의 기능과 API를 보여주면서 여러 기술을 하나의 앱으로 통합하고자 했습니다. 이 실습의 목표는 상상력을 발휘하고 Cloud Console과 Google Workspace를 모두 사용하여 조직과 고객의 어려운 문제를 해결하도록 영감을 주는 것입니다.

학습할 내용

여러 Google 서비스와 함께 Google Apps Script를 사용하는 방법
Google BigQuery를 사용하여 빅데이터를 분석하는 방법
Google 시트를 만들고 데이터를 삽입하는 방법
Sheets에서 차트를 만드는 방법
Sheets의 데이터와 차트를 Google Slides 프레젠테이션으로 전송하는 방법

필요한 항목

인터넷에 액세스할 수 있는 웹브라우저
Google 계정 (Google Workspace 계정의 경우 관리자 승인이 필요할 수 있음)
Google Sheets에 관한 기본 지식
Sheets A1 표기법을 읽을 수 있음
기본 JavaScript 기술
Apps Script 개발에 관한 지식(필수는 아니지만 도움이 됨)

2. 설문조사

이 Codelab/튜토리얼을 어떻게 사용할 예정인가요?

정보를 위해 읽고 기술 동료에게 전달할 수 있음

가능한 한 많이 살펴보고 최대한 많은 연습을 시도함

어떤 일이 있어도 Codelab 전체를 완료함

Google Workspace 개발자 도구 및 API 사용 경험을 평가해 주세요.

초급

중급

고급

Apps Script 사용 경험을 어떻게 평가하시겠어요?

초급

중급

고급

Cloud 콘솔 개발자 도구 및 API 사용 경험을 평가해 주세요.

초급

중급

고급

3. 개요

이 Codelab의 내용을 알았으니 이제 다음 작업을 진행합니다.

기존 Apps Script-BigQuery 샘플을 가져와 작동하도록 합니다.
샘플에서 BigQuery에 쿼리를 전송하고 결과를 가져오는 방법을 알아봅니다.
Google 시트를 만들고 BigQuery의 결과를 삽입합니다.
반환되어 시트에 삽입되는 데이터를 약간 변경하도록 코드를 수정합니다.
Apps Script의 Sheets 서비스를 사용하여 BigQuery 데이터의 차트를 만듭니다.
Slides 서비스를 사용하여 Google Slides 프레젠테이션을 만듭니다.
기본 제목 슬라이드에 제목과 부제목을 추가합니다.
데이터 표가 있는 슬라이드를 만들고 시트의 데이터 셀을 내보냅니다.
다른 슬라이드를 만들고 스프레드시트 차트를 삽입합니다.

Apps Script, BigQuery, Sheets, Slides에 관한 배경 정보를 살펴보겠습니다.

Google Apps Script 및 BigQuery

Google Apps Script는 Google REST API보다 높은 수준에서 작동하는 Google Workspace 개발 플랫폼입니다. 기술 수준과 상관없이 모든 개발자가 액세스할 수 있는 서버리스 개발 및 애플리케이션 호스팅 환경입니다. Apps Script는 기본적으로 Google Workspace 자동화, 확장, 통합을 위한 서버리스 JavaScript 런타임입니다.

Google Apps Script는 Node.js와 유사한 서버 측 JavaScript지만, 빠른 비동기 이벤트 기반 애플리케이션 호스팅보다는 Google Workspace 및 기타 Google 서비스와의 긴밀한 통합을 더 중시합니다. 또한 평소 사용하던 것과 다른 개발 환경을 사용합니다. Apps Script를 사용하면 다음 작업을 할 수 있습니다.

브라우저 기반 코드 편집기를 사용하여 스크립트를 개발하되, Apps Script의 명령줄 배포 도구인 clasp를 사용하는 경우 로컬에서 개발할 수도 있습니다.
Google Workspace 및 기타 Google 또는 외부 서비스에 액세스하도록 맞춤설정된 특수 버전 JavaScript로 코드 작성 (Apps Script의 URL Fetch 또는 JDBC 서비스 사용).
Apps Script에서 대신 처리하므로 승인 코드를 작성하지 않아도 됩니다.
앱 호스팅 불필요 - 앱은 클라우드의 Google 서버에서 실행됨

Apps Script는 다음 두 가지 방법으로 다른 Google 기술과 상호 작용합니다.

기본 제공 서비스로 사용
고급 서비스로 사용

기본 제공 서비스에는 사용자 데이터, 다른 Google 시스템, 외부 시스템과 상호작용하기 위한 상위 수준 메서드가 있습니다. 고급 서비스는 기본적으로 Google Workspace API 또는 Google REST API를 래핑하는 씬 래퍼입니다. 고급 서비스는 REST API를 완전히 지원하며 대부분의 경우 기본 제공 서비스보다 더 많은 작업을 할 수 있지만 코드가 더 복잡합니다 (그래도 전체 REST API만 사용할 때보다는 더 쉽게 사용할 수 있습니다). 고급 서비스를 사용하려면 먼저 스크립트 프로젝트에 고급 서비스를 사용 설정해야 합니다.

가능하면 개발자는 기본 제공 서비스를 사용해야 합니다. 기본 제공 서비스는 사용하기 쉽고 고급 서비스보다 더 많은 작업을 처리할 수 있기 때문입니다. 하지만 일부 Google API에는 기본 제공 서비스가 없으므로 고급 서비스를 선택할 수밖에 없습니다. 예를 들어 Google BigQuery에는 기본 제공 서비스가 없지만 BigQuery 서비스는 있습니다. BigQuery 서비스는 Google BigQuery API를 사용하여 대규모 데이터 코퍼스 (예: 수 테라바이트)에 대해 쿼리를 실행할 수 있는 Cloud 콘솔 서비스로, 몇 초 만에 결과를 제공할 수 있습니다.

Apps Script에서 Sheets 및 Slides에 액세스

BigQuery와 달리 Sheets와 Slides에는 기본 제공 서비스가 있습니다. 또한 API에서만 제공되는 기능에 액세스할 수 있는 고급 서비스도 있습니다. 코드를 작성하기 전에 내장된 Sheets 및 Slides 서비스의 문서를 확인하세요. Sheets와 Slides의 고급 서비스에 관한 문서도 있습니다.

4. 작업 1: BigQuery를 실행하고 결과를 Sheets에 기록

소개

이 첫 번째 작업을 통해 이 Codelab의 상당 부분을 완료하게 됩니다. 이 섹션을 마치면 전체 Codelab의 절반 정도를 완료하게 됩니다. 여러 하위 섹션으로 구분된 이 과정에서는 다음을 수행합니다.

Google Apps Script와 Cloud 콘솔 프로젝트를 모두 만듭니다.
BigQuery 고급 서비스에 대한 액세스를 사용 설정합니다.
스크립트 편집기를 열고 애플리케이션 소스 코드를 입력합니다.
앱 승인 프로세스 (OAuth2)를 진행합니다.
BigQuery에 요청을 전송하는 애플리케이션을 실행합니다.
BigQuery 결과를 사용하여 생성된 새 Google 시트를 검토합니다.

설정

Apps Script 프로젝트를 만들려면 script.google.com로 이동하여 새 프로젝트를 클릭합니다.
Apps Script 프로젝트의 이름을 바꾸려면 제목 없는 프로젝트를 클릭하고 프로젝트 제목을 입력한 다음 이름 바꾸기를 클릭합니다.

다음으로 BigQuery에서 데이터를 쿼리할 Cloud Console 프로젝트를 만들어야 합니다.

Cloud 콘솔 프로젝트를 만들려면 이 바로가기 링크를 사용하여 프로젝트를 만들고 프로젝트 이름을 지정한 후 만들기를 클릭합니다.

프로젝트 생성이 완료되면 페이지에 알림이 표시됩니다. 페이지 상단의 프로젝트 목록에서 새 프로젝트를 선택하도록 합니다.
메뉴 를 클릭하고 API 및 서비스 > OAuth 동의 화면 (바로가기 링크)으로 이동합니다.
내부 > 만들기를 클릭하여 조직 내 Google Workspace 사용자를 위한 앱을 빌드합니다.
앱 이름 필드에 'Big Data Codelab'을 입력합니다.
사용자 지원 및 개발자 연락처 정보 필드에 연락처 이메일을 입력합니다.
저장하고 계속하기 > 저장하고 계속하기를 클릭합니다.
탐색 메뉴에서 더보기 를 클릭하고 프로젝트 설정 (바로가기 링크)을 선택합니다.
프로젝트 번호 아래에 나열된 값을 복사합니다. (별도의 프로젝트 ID 필드는 Codelab의 뒷부분에서 사용됩니다.)

다음으로 Apps Script 프로젝트를 Cloud 콘솔 프로젝트에 연결합니다.

App Script 편집기로 전환하고 프로젝트 설정 을 클릭합니다.
Google Cloud Platform (GCP) 프로젝트에서 프로젝트 변경을 클릭합니다.
프로젝트 번호를 입력하고 프로젝트 설정을 클릭합니다.
다음으로 편집기 를 클릭하여 BigQuery 고급 서비스 추가를 시작합니다.
서비스 옆에 있는 서비스 추가 를 클릭합니다.
'서비스 추가' 대화상자에서 BigQuery API를 선택하고 추가를 클릭합니다.

마지막 단계는 Cloud 콘솔에서 BigQuery API를 사용 설정하는 것입니다.

이렇게 하려면 Cloud 콘솔로 전환하고 API 및 서비스 > 대시보드를 클릭합니다. (3단계에서 만든 프로젝트에 계속 있는지 확인합니다.)
API 및 서비스 사용 설정을 클릭합니다.
'big query'를 검색하고 BigQuery API (BigQuery Data Transfer API가 아님)를 선택한 다음 사용 설정을 클릭하여 사용 설정합니다.

이제 애플리케이션 코드를 입력하고 승인 절차를 거쳐 이 애플리케이션의 첫 번째 버전을 작동할 수 있습니다.

애플리케이션 업로드 및 실행

스크립트 편집기에서 기본 myFunction() 코드 블록을 다음 코드로 바꿉니다.

// File name for data results
var QUERY_NAME = "Most common words in all of Shakespeare's works";
// Replace this value with your Google Cloud Console Project ID
var PROJECT_ID = '';
if (!PROJECT_ID) throw Error('Project ID is required in setup');

/**
 * Runs a BigQuery query; puts results into a Sheet. You must turn on
 * the BigQuery advanced service before you can run this code.
 * @see https://developers.google.com/apps-script/advanced/bigquery#run_query
 * @see https://github.com/googleworkspace/apps-script-samples/blob/master/advanced/bigquery.gs
 *
 * @returns {Spreadsheet} Returns a spreadsheet with BigQuery results
 * @see https://developers.google.com/apps-script/reference/spreadsheet/spreadsheet
 */
function runQuery() {
  // Replace sample with your own BigQuery query.
  var request = {
    query:
        'SELECT ' +
            'LOWER(word) AS word, ' +
            'SUM(word_count) AS count ' +
        'FROM [bigquery-public-data:samples.shakespeare] ' +
        'GROUP BY word ' +
        'ORDER BY count ' +
        'DESC LIMIT 10'
  };
  var queryResults = BigQuery.Jobs.query(request, PROJECT_ID);
  var jobId = queryResults.jobReference.jobId;

  // Wait for BigQuery job completion (with exponential backoff).
  var sleepTimeMs = 500;
  while (!queryResults.jobComplete) {
    Utilities.sleep(sleepTimeMs);
    sleepTimeMs *= 2;
    queryResults = BigQuery.Jobs.getQueryResults(PROJECT_ID, jobId);
  }

  // Get all results from BigQuery.
  var rows = queryResults.rows;
  while (queryResults.pageToken) {
    queryResults = BigQuery.Jobs.getQueryResults(PROJECT_ID, jobId, {
      pageToken: queryResults.pageToken
    });
    rows = rows.concat(queryResults.rows);
  }

  // Return null if no data returned.
  if (!rows) {
    return Logger.log('No rows returned.');
  }

  // Create the results spreadsheet.
  var spreadsheet = SpreadsheetApp.create(QUERY_NAME);
  var sheet = spreadsheet.getActiveSheet();

  // Add headers to Sheet.
  var headers = queryResults.schema.fields.map(function(field) {
    return field.name.toUpperCase();
  });
  sheet.appendRow(headers);

  // Append the results.
  var data = new Array(rows.length);
  for (var i = 0; i < rows.length; i++) {
    var cols = rows[i].f;
    data[i] = new Array(cols.length);
    for (var j = 0; j < cols.length; j++) {
      data[i][j] = cols[j].v;
    }
  }

  // Start storing data in row 2, col 1
  var START_ROW = 2;      // skip header row
  var START_COL = 1;
  sheet.getRange(START_ROW, START_COL, rows.length, headers.length).setValues(data);

  Logger.log('Results spreadsheet created: %s', spreadsheet.getUrl());
}

저장 를 클릭합니다.
Code.gs 옆에 있는 더보기 > 이름 바꾸기를 클릭합니다. 제목을 Code.gs에서 bq-sheets-slides.js로 변경합니다.
다음으로 BigQuery를 쿼리하고 결과를 Google 시트에 작성하는 코드를 검토해 보겠습니다. runQuery() 상단에서 확인할 수 있습니다.

SELECT
    LOWER(word) AS word,
    SUM(word_count) AS count
FROM [bigquery-public-data:samples.shakespeare]
GROUP BY word
ORDER BY count
DESC LIMIT 10

이 쿼리는 BigQuery 공개 데이터 세트의 일부인 셰익스피어 작품을 살펴보고 모든 작품에서 가장 자주 등장하는 상위 10개 단어를 인기도 기준 내림차순으로 정렬하여 생성합니다. 이 컴파일을 직접 수행하는 데 얼마나 많은 작업이 필요한지 상상해 보면 BigQuery가 얼마나 유용한지 알 수 있습니다.

이 함수는 유효한 Cloud Console 프로젝트 ID가 필요한 PROJECT_ID 변수도 선언합니다. 변수 아래의 if 문구는 애플리케이션이 프로젝트 ID 없이 진행되지 않도록 하기 위한 것입니다.

Cloud Console 프로젝트로 전환하고 탐색 메뉴에서 더보기 를 클릭한 후 프로젝트 설정을 선택합니다.
프로젝트 ID 아래에 나열된 값을 복사합니다.
App Script 편집기로 다시 전환하고 bq-sheets-slides.js에서 PROJECT_ID 변수를 찾아 값을 추가합니다.
저장 > 실행을 클릭합니다.
계속하려면 권한 검토를 클릭합니다.

참고: gmail.com 계정을 사용하는 경우 스크립트를 처음 사용할 때 '이 앱은 인증되지 않았습니다' 대화상자가 표시될 수 있습니다. Google은 이를 사용하여 알 수 없거나 신뢰할 수 없는 작성자의 코드를 사용할 수 있는 사용자에게 경고합니다. 이 대화상자가 표시되면 스크립트 작성자이므로 계속 진행해도 됩니다. 화면에 표시되는 메시지에 따라 스크립트 승인을 계속 진행합니다.

인증되지 않은 앱 대화상자에서 고급을 클릭합니다.
your-project-name (안전하지 않음)으로 이동을 클릭합니다.
허용을 클릭합니다.

이 Codelab을 진행하는 동안 여러 권한 메시지가 표시될 수 있습니다. 이 절차에 대한 자세한 내용은 Google 서비스 승인을 참고하세요.

스크립트가 실행되면 내장 실행 로그가 열리고 스크립트 작업이 실시간으로 기록됩니다.

실행 로그에 'Execution completed'(실행 완료)가 표시되면 Google Drive(drive.google.com)로 이동하여 'Most common words in all of Shakespeare's works'(셰익스피어의 모든 작품에서 가장 자주 등장하는 단어)라는 Google 시트(또는 QUERY_NAME 변수를 업데이트한 경우 변수에 할당한 이름)를 찾습니다.
스프레드시트를 열어 가장 일반적인 단어 10개와 단어의 총 개수를 내림차순으로 확인합니다.

작업 1 요약

요약하자면, 모든 희곡의 모든 단어를 살펴보는 셰익스피어의 모든 작품을 쿼리하는 코드를 실행했습니다. 단어를 세고 등장 순서의 내림차순으로 정렬했습니다. 또한 Google Sheets용 Apps Script 기본 제공 서비스를 사용하여 이 데이터를 표시했습니다.

bq-sheets-slides.js에 사용한 코드는 이 Codelab의 GitHub 저장소(github.com/googlecodelabs/bigquery-sheets-slides)의 step1 폴더에서도 확인할 수 있습니다. 이 코드는 셰익스피어가 사용한 10자 이상의 가장 인기 있는 단어를 가져오는 약간 다른 쿼리를 실행한 BigQuery 고급 서비스 페이지의 원래 예에서 영감을 받았습니다. GitHub 저장소에서도 예시를 확인할 수 있습니다.

셰익스피어 작품 또는 기타 공개 데이터 테이블로 빌드할 수 있는 다른 쿼리에 관심이 있다면 BigQuery 샘플 테이블 쿼리 방법 및 이 GitHub 저장소를 참고하세요.

Apps Script에서 쿼리를 실행하기 전에 Cloud 콘솔의 BigQuery 페이지를 사용하여 쿼리를 실행할 수도 있습니다. 이를 찾으려면 메뉴 를 클릭하고 BigQuery UI > SQL 작업공간 (직접 링크)으로 이동합니다. 예를 들어 BigQuery 그래픽 인터페이스에 쿼리가 표시되는 방식은 다음과 같습니다.

BigQueryUI

5. 작업 2: Google Sheets에서 차트 만들기

runQuery()의 목적은 BigQuery를 사용하고 그 데이터 결과를 Google Sheets로 전송하는 것입니다. 이제 데이터를 사용하여 차트를 만들어야 합니다. Sheets의 newChart() 메서드를 호출하는 createColumnChart()라는 새 함수를 만들어 보겠습니다.

Apps Script 편집기에서 runQuery() 뒤에 bq-sheets-slides.js에 createColumnChart() 함수를 추가합니다. 코드는 시트를 가져와 모든 데이터가 포함된 열 차트를 요청합니다. 첫 번째 행에는 열 헤더가 포함되어 있으므로 데이터 범위는 A2 셀에서 시작됩니다.

/**
 * Uses spreadsheet data to create a column chart.
 * @param {Spreadsheet} Spreadsheet containing results data
 * @returns {EmbeddedChart} Visualizes the results
 * @see https://developers.google.com/apps-script/reference/spreadsheet/embedded-chart
 */
function createColumnChart(spreadsheet) {
  // Retrieve the populated (first) Sheet.
  var sheet = spreadsheet.getSheets()[0];
  // Data range in Sheet is from cell A2 to B11.
  var START_CELL = 'A2';  // skip header row
  var END_CELL = 'B11';
  // Place chart on Sheet starting on cell E5.
  var START_ROW = 5;      // row 5
  var START_COL = 5;      // col E
  var OFFSET = 0;

  // Create & place chart on the Sheet using above values.
  var chart = sheet.newChart()
     .setChartType(Charts.ChartType.COLUMN)
     .addRange(sheet.getRange(START_CELL + ':' + END_CELL))
     .setPosition(START_ROW, START_COL, OFFSET, OFFSET)
     .build();
  sheet.insertChart(chart);
}

createColumnChart() 함수에는 스프레드시트 객체 매개변수가 필요하므로 createColumnChart()에 전달할 수 있는 spreadsheet 객체를 반환하도록 runQuery()을 업데이트해야 합니다. runQuery() 끝에서 시트가 성공적으로 생성되었음을 로깅한 후 spreadsheet 객체를 반환합니다.

  Logger.log('Results spreadsheet created: %s', spreadsheet.getUrl());

  // NEW: Return the spreadsheet object for later use.
  return spreadsheet;
}

runQuery()과 createColumnChart()를 모두 호출하는 createBigQueryPresentation() 함수를 만듭니다. BigQuery와 차트 생성 기능을 논리적으로 분리하는 것이 좋습니다.

/**
 * Runs the query, adds data and a chart to a spreadsheet.
 */
function createBigQueryPresentation() {
  var spreadsheet = runQuery();
  createColumnChart(spreadsheet);
}

지금까지 스프레드시트 객체를 반환하고 진입 함수를 만드는 두 가지 중요한 단계를 수행했습니다. runQuery()를 더 유용하게 만들려면 로그 줄을 runQuery()에서 createBigQueryPresentation()로 이동해야 합니다. 이제 메서드가 다음과 같이 표시됩니다.

/**
 * Runs a BigQuery query, adds data and a chart to a spreadsheet.
 */
function createBigQueryPresentation() {
  var spreadsheet = runQuery();
  Logger.log('Results spreadsheet created: %s', spreadsheet.getUrl()); // MOVED HERE
  createColumnChart(spreadsheet);
}

위의 변경사항 (PROJECT_ID 제외)에 따라 bq-sheets-slides.js은 이제 다음과 같이 표시됩니다. 이 코드는 GitHub 저장소의 step2에서도 확인할 수 있습니다.

// File name for data results
var QUERY_NAME = "Most common words in all of Shakespeare's works";
// Replace this value with your Google Cloud Console Project ID
var PROJECT_ID = '';
if (!PROJECT_ID) throw Error('Project ID is required in setup');

/**
 * Runs a BigQuery query; puts results into a sheet. You must turn on
 * the BigQuery advanced service before you can run this code.
 * @see https://developers.google.com/apps-script/advanced/bigquery#run_query
 * @see https://github.com/googleworkspace/apps-script-samples/blob/master/advanced/bigquery.gs
 *
 * @returns {Spreadsheet} Returns a spreadsheet with BigQuery results
 * @see https://developers.google.com/apps-script/reference/spreadsheet/spreadsheet
 */
function runQuery() {
  // Replace sample with your own BigQuery query.
  var request = {
    query:
        'SELECT ' +
            'LOWER(word) AS word, ' +
            'SUM(word_count) AS count ' +
        'FROM [bigquery-public-data:samples.shakespeare] ' +
        'GROUP BY word ' +
        'ORDER BY count ' +
        'DESC LIMIT 10'
  };
  var queryResults = BigQuery.Jobs.query(request, PROJECT_ID);
  var jobId = queryResults.jobReference.jobId;

  // Wait for BigQuery job completion (with exponential backoff).
  var sleepTimeMs = 500;
  while (!queryResults.jobComplete) {
    Utilities.sleep(sleepTimeMs);
    sleepTimeMs *= 2;
    queryResults = BigQuery.Jobs.getQueryResults(PROJECT_ID, jobId);
  }

  // Get all results from BigQuery.
  var rows = queryResults.rows;
  while (queryResults.pageToken) {
    queryResults = BigQuery.Jobs.getQueryResults(PROJECT_ID, jobId, {
      pageToken: queryResults.pageToken
    });
    rows = rows.concat(queryResults.rows);
  }

  // Return null if no data returned.
  if (!rows) {
    return Logger.log('No rows returned.');
  }

  // Create the results spreadsheet.
  var spreadsheet = SpreadsheetApp.create(QUERY_NAME);
  var sheet = spreadsheet.getActiveSheet();

  // Add headers to sheet.
  var headers = queryResults.schema.fields.map(function(field) {
    return field.name.toUpperCase();
  });
  sheet.appendRow(headers);

  // Append the results.
  var data = new Array(rows.length);
  for (var i = 0; i < rows.length; i++) {
    var cols = rows[i].f;
    data[i] = new Array(cols.length);
    for (var j = 0; j < cols.length; j++) {
      data[i][j] = cols[j].v;
    }
  }

  // Start storing data in row 2, col 1
  var START_ROW = 2;      // skip header row
  var START_COL = 1;
  sheet.getRange(START_ROW, START_COL, rows.length, headers.length).setValues(data);

  // Return the spreadsheet object for later use.
  return spreadsheet;
}

/**
 * Uses spreadsheet data to create a columnar chart.
 * @param {Spreadsheet} Spreadsheet containing results data
 * @returns {EmbeddedChart} Visualizes the results
 * @see https://developers.google.com/apps-script/reference/spreadsheet/embedded-chart
 */
function createColumnChart(spreadsheet) {
  // Retrieve the populated (first) sheet.
  var sheet = spreadsheet.getSheets()[0];
  // Data range in sheet is from cell A2 to B11.
  var START_CELL = 'A2';  // skip header row
  var END_CELL = 'B11';
  // Place chart on Sheet starting on cell E5.
  var START_ROW = 5;      // row 5
  var START_COL = 5;      // col E
  var OFFSET = 0;

  // Create & place chart on the sheet using above values.
  var chart = sheet.newChart()
     .setChartType(Charts.ChartType.COLUMN)
     .addRange(sheet.getRange(START_CELL + ':' + END_CELL))
     .setPosition(START_ROW, START_COL, OFFSET, OFFSET)
     .build();
  sheet.insertChart(chart);
}

/**
 * Runs a BigQuery query, adds data and a chart to a spreadsheet.
 */
function createBigQueryPresentation() {
  var spreadsheet = runQuery();
  Logger.log('Results spreadsheet created: %s', spreadsheet.getUrl());
  createColumnChart(spreadsheet);
}

스크립트 편집기에서 스크립트 프로젝트를 저장합니다. 그런 다음 함수 목록에서 createBigQueryPresentation를 선택하고 실행을 클릭합니다. 완료되면 Google Drive에 다른 스프레드시트가 생성되지만 이번에는 시트의 데이터 옆에 차트가 포함됩니다.

차트가 포함된 시트

6. 작업 3: 결과 데이터를 슬라이드 자료에 넣기

Codelab의 마지막 부분에서는 Google Slides 프레젠테이션을 만들고, 제목 슬라이드에 제목과 부제목을 추가한 다음 데이터 셀과 차트용 슬라이드를 빌드합니다.

Apps Script 편집기에서 createColumnChart() 뒤에 bq-sheets-slides.js에 createSlidePresentation() 함수를 추가합니다. 슬라이드 자료에 관한 모든 작업은 이 함수에서 이루어집니다. 먼저 슬라이드 자료를 만든 후 기본 제목 슬라이드에 제목과 부제목을 추가합니다.

/**
 * Create presentation with spreadsheet data and a chart
 * @param {Spreadsheet} Spreadsheet containing results data
 * @param {EmbeddedChart} Sheets chart to embed on a slide
 * @returns {Presentation} Slide deck with the results
  * @see https://developers.google.com/apps-script/reference/slides/presentation
 */
function createSlidePresentation(spreadsheet, chart) {
  // Create the presentation.
  var deck = SlidesApp.create(QUERY_NAME);

  // Populate the title slide.
  var [title, subtitle] = deck.getSlides()[0].getPageElements();
  title.asShape().getText().setText(QUERY_NAME);
  subtitle.asShape().getText().setText('using Google Cloud Console and Google Workspace APIs:\n' +
    'Google Apps Script, BigQuery, Sheets, Slides');

createSlidePresentation()의 다음 단계는 Google 시트의 셀 데이터를 새 슬라이드 자료로 가져오는 것입니다. 다음 코드 스니펫을 함수에 추가합니다.

  // Data range to copy is from cell A1 to B11
  var START_CELL = 'A1';  // include header row
  var END_CELL = 'B11';
  // Add the table slide and insert an empty table on it with
  // the dimensions of the data range; fails if the sheet is empty.
  var tableSlide = deck.appendSlide(SlidesApp.PredefinedLayout.BLANK);
  var sheetValues = spreadsheet.getSheets()[0].getRange(
      START_CELL + ':' + END_CELL).getValues();
  var table = tableSlide.insertTable(sheetValues.length, sheetValues[0].length);

  // Populate the table with spreadsheet data.
  for (var i = 0; i < sheetValues.length; i++) {
    for (var j = 0; j < sheetValues[0].length; j++) {
      table.getCell(i, j).getText().setText(String(sheetValues[i][j]));
    }
  }

createSlidePresentation()의 마지막 단계는 슬라이드를 하나 더 추가하고 스프레드시트에서 차트를 가져와 Presentation 객체를 반환하는 것입니다. 다음 코드 스니펫을 함수에 추가합니다.

  // Add a chart slide and insert the chart on it.
  var chartSlide = deck.appendSlide(SlidesApp.PredefinedLayout.BLANK);
  chartSlide.insertSheetsChart(chart);

  // Return the presentation object for later use.
  return deck;
}

함수가 완료되었으므로 함수의 서명을 다시 살펴보세요. createSlidePresentation()에는 스프레드시트와 차트 객체 매개변수가 모두 필요합니다. Spreadsheet 객체를 반환하도록 runQuery()를 이미 조정했습니다. 이제 createColumnChart()도 비슷한 방식으로 변경하여 차트 객체 (EmbeddedChart)를 반환해야 합니다. createColumnChart()로 돌아가 함수 끝에 다음 코드 스니펫을 추가합니다.

  // NEW: Return the chart object for later use.
  return chart;
}

이제 createColumnChart()가 차트 객체를 반환하므로 차트를 변수에 저장해야 합니다. 그런 다음 스프레드시트와 차트 변수를 createSlidePresentation()에 모두 전달합니다. 새로 만든 스프레드시트의 URL을 로깅하므로 새 슬라이드 프레젠테이션의 URL도 로깅하겠습니다. createBigQueryPresentation()를 다음과 같이 업데이트합니다.

/**
 * Runs a BigQuery query, adds data and a chart to a spreadsheet,
 * and adds the data and chart to a new slide presentation.
 */
function createBigQueryPresentation() {
  var spreadsheet = runQuery();
  Logger.log('Results spreadsheet created: %s', spreadsheet.getUrl());
  var chart = createColumnChart(spreadsheet); // UPDATED
  var deck = createSlidePresentation(spreadsheet, chart); // NEW
  Logger.log('Results slide deck created: %s', deck.getUrl()); // NEW
}

createBigQueryPresentation()을 저장하고 다시 실행합니다. 하지만 실행하기 전에 앱에서 Google Slides 프레젠테이션을 보고 관리할 수 있도록 사용자로부터 권한을 한 번 더 요청해야 합니다. 이러한 권한을 검토하고 허용하면 이전과 같이 실행됩니다.
이제 방금 생성된 스프레드시트 외에도, 아래와 같은 슬라이드 3개 (제목, 데이터 테이블, 데이터 차트)가 포함된 새 Slides 프레젠테이션이 함께 표시됩니다.

7. 결론

축하합니다. Google Cloud의 양쪽을 모두 사용하는 애플리케이션을 만들었습니다. 공개 데이터 세트 중 하나를 쿼리하는 Google BigQuery 요청을 실행하고, 결과를 저장할 Google Sheets 스프레드시트를 만들고, 데이터를 기반으로 차트를 추가하고, 마지막으로 스프레드시트의 데이터 및 차트 결과를 표시하는 Google Slides 프레젠테이션을 만듭니다.

이 단계는 기술적으로 수행한 작업입니다. 간단히 말하면, 빅데이터 분석에서 이해관계자 대상 발표 주제에 이르는 모든 요소를 작업했습니다. 모든 작업은 코드를 이용해 자동으로 수행했습니다. 이 샘플을 통해 나만의 프로젝트에 맞게 맞춤설정할 수 있기를 바랍니다. 이 Codelab이 끝나면 이 샘플 앱을 더욱 개선할 수 있는 몇 가지 제안사항을 제공해 드립니다.

마지막 작업의 변경사항 (PROJECT_ID 제외)에 따라 bq-sheets-slides.js는 이제 다음과 같이 표시됩니다.

/**
 * Copyright 2018 Google LLC
 *
 * Licensed under the Apache License, Version 2.0 (the "License");
 * you may not use this file except in compliance with the License.
 * You may obtain a copy of the License at apache.org/licenses/LICENSE-2.0.
 *
 * Unless required by applicable law or agreed to in writing, software
 * distributed under the License is distributed on an "AS IS" BASIS,
 * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
 * See the License for the specific language governing permissions and
 * limitations under the License.
 */

// File name for data results
var QUERY_NAME = "Most common words in all of Shakespeare's works";
// Replace this value with your Google Cloud Console Project ID
var PROJECT_ID = '';
if (!PROJECT_ID) throw Error('Project ID is required in setup');

/**
 * Runs a BigQuery query; puts results into a spreadsheet. You must turn on
 * the BigQuery advanced service before you can run this code.
 * @see https://developers.google.com/apps-script/advanced/bigquery#run_query
 * @see https://github.com/googleworkspace/apps-script-samples/blob/master/advanced/bigquery.gs
 *
 * @returns {Spreadsheet} Returns a spreadsheet with BigQuery results
 * @see https://developers.google.com/apps-script/reference/spreadsheet/spreadsheet
 */
function runQuery() {
  // Replace sample with your own BigQuery query.
  var request = {
    query:
        'SELECT ' +
            'LOWER(word) AS word, ' +
            'SUM(word_count) AS count ' +
        'FROM [bigquery-public-data:samples.shakespeare] ' +
        'GROUP BY word ' +
        'ORDER BY count ' +
        'DESC LIMIT 10'
  };
  var queryResults = BigQuery.Jobs.query(request, PROJECT_ID);
  var jobId = queryResults.jobReference.jobId;

  // Wait for BigQuery job completion (with exponential backoff).
  var sleepTimeMs = 500;
  while (!queryResults.jobComplete) {
    Utilities.sleep(sleepTimeMs);
    sleepTimeMs *= 2;
    queryResults = BigQuery.Jobs.getQueryResults(PROJECT_ID, jobId);
  }

  // Get all results from BigQuery.
  var rows = queryResults.rows;
  while (queryResults.pageToken) {
    queryResults = BigQuery.Jobs.getQueryResults(PROJECT_ID, jobId, {
      pageToken: queryResults.pageToken
    });
    rows = rows.concat(queryResults.rows);
  }

  // Return null if no data returned.
  if (!rows) {
    return Logger.log('No rows returned.');
  }

  // Create the results spreadsheet.
  var spreadsheet = SpreadsheetApp.create(QUERY_NAME);
  var sheet = spreadsheet.getActiveSheet();

  // Add headers to sheet.
  var headers = queryResults.schema.fields.map(function(field) {
    return field.name.toUpperCase();
  });
  sheet.appendRow(headers);

  // Append the results.
  var data = new Array(rows.length);
  for (var i = 0; i < rows.length; i++) {
    var cols = rows[i].f;
    data[i] = new Array(cols.length);
    for (var j = 0; j < cols.length; j++) {
      data[i][j] = cols[j].v;
    }
  }

  // Start storing data in row 2, col 1
  var START_ROW = 2;      // skip header row
  var START_COL = 1;
  sheet.getRange(START_ROW, START_COL, rows.length, headers.length).setValues(data);

  // Return the spreadsheet object for later use.
  return spreadsheet;
}

/**
 * Uses spreadsheet data to create a column chart.
 * @param {Spreadsheet} Spreadsheet containing results data
 * @returns {EmbeddedChart} Visualizes the results
 * @see https://developers.google.com/apps-script/reference/spreadsheet/embedded-chart
 */
function createColumnChart(spreadsheet) {
  // Retrieve the populated (first) sheet.
  var sheet = spreadsheet.getSheets()[0];
  // Data range in sheet is from cell A2 to B11.
  var START_CELL = 'A2';  // skip header row
  var END_CELL = 'B11';
  // Place chart on sheet starting on cell E5.
  var START_ROW = 5;      // row 5
  var START_COL = 5;      // col E
  var OFFSET = 0;

  // Create & place chart on the sheet using above values.
  var chart = sheet.newChart()
     .setChartType(Charts.ChartType.COLUMN)
     .addRange(sheet.getRange(START_CELL + ':' + END_CELL))
     .setPosition(START_ROW, START_COL, OFFSET, OFFSET)
     .build();
  sheet.insertChart(chart);
  
  // Return the chart object for later use.
  return chart;
}

/**
 * Create presentation with spreadsheet data and a chart
 * @param {Spreadsheet} Spreadsheet containing results data
 * @param {EmbeddedChart} Sheets chart to embed on a slide
 * @returns {Presentation} Slide deck with the results
 * @see https://developers.google.com/apps-script/reference/slides/presentation
 */
function createSlidePresentation(spreadsheet, chart) {
  // Create the presentation.
  var deck = SlidesApp.create(QUERY_NAME);

  // Populate the title slide.
  var [title, subtitle] = deck.getSlides()[0].getPageElements();
  title.asShape().getText().setText(QUERY_NAME);
  subtitle.asShape().getText().setText('using Google Cloud Console and Google Workspace APIs:\n' +
    'Google Apps Script, BigQuery, Sheets, Slides');

  // Data range to copy is from cell A1 to B11
  var START_CELL = 'A1';  // include header row
  var END_CELL = 'B11';
  // Add the table slide and insert an empty table on it with
  // the dimensions of the data range; fails if the sheet is empty.
  var tableSlide = deck.appendSlide(SlidesApp.PredefinedLayout.BLANK);
  var sheetValues = spreadsheet.getSheets()[0].getRange(
      START_CELL + ':' + END_CELL).getValues();
  var table = tableSlide.insertTable(sheetValues.length, sheetValues[0].length);

  // Populate the table with spreadsheet data.
  for (var i = 0; i < sheetValues.length; i++) {
    for (var j = 0; j < sheetValues[0].length; j++) {
      table.getCell(i, j).getText().setText(String(sheetValues[i][j]));
    }
  }

  // Add a chart slide and insert the chart on it.
  var chartSlide = deck.appendSlide(SlidesApp.PredefinedLayout.BLANK);
  chartSlide.insertSheetsChart(chart);

  // Return the presentation object for later use.
  return deck;
}

/**
 * Runs a BigQuery query, adds data and a chart to a spreadsheet,
 * and adds the data and chart to a new slide presentation.
 */
function createBigQueryPresentation() {
  var spreadsheet = runQuery();
  Logger.log('Results spreadsheet created: %s', spreadsheet.getUrl());
  var chart = createColumnChart(spreadsheet);
  var deck = createSlidePresentation(spreadsheet, chart);
  Logger.log('Results slide deck created: %s', deck.getUrl());
}

이 코드 샘플은 GitHub 저장소의 final 폴더에서도 확인할 수 있습니다.

8. 추가 리소스

다음은 이 Codelab에서 다룬 내용을 자세히 살펴보고 Google 개발자 도구에 프로그래매틱 방식으로 액세스하는 다른 방법을 알아볼 수 있는 추가 리소스입니다.

이 애플리케이션의 리소스

문서

동영상

뉴스 및 업데이트

Google Cloud Platform 블로그
Google Cloud 데이터 애널리틱스 블로그
Google Developers 블로그
트위터: Google Developers (@googledevs)
Google Workspace 개발자 블로그
트위터: Google Workspace Developers (@workspacedevs)

기타 Codelab

도입

[Google Sheets] Google Sheets를 사용한 Apps Script의 기본사항
[REST API] Google Workspace 및 Google API를 사용하여 Google Drive의 파일 및 폴더에 액세스하기

중급

[Apps Script] CLASP Apps Script 명령줄 도구
[Apps Script] Gmail용 Google Workspace 부가기능
[Apps Script] 행아웃 채팅용 맞춤 봇
[REST API] Google Sheets를 애플리케이션의 보고 도구로 사용하기
[REST API] BigQuery API를 사용하여 Google Slides 프레젠테이션 생성

9. 다음 단계: 코드 챌린지

아래에는 이 Codelab에서 빌드한 샘플을 개선하거나 보강할 수 있는 다양한 방법이 나와 있습니다. 이 목록은 완전한 목록이 아니지만 다음 단계를 진행하는 데 도움이 되는 몇 가지 아이디어를 제공합니다.

애플리케이션 - JavaScript 사용 또는 Apps Script에서 부과하는 제한사항에 국한되지 않으려면 어떻게 해야 하나요? 이 애플리케이션을 Google BigQuery, Sheets, Slides의 REST API를 사용하는 선호하는 프로그래밍 언어로 포팅합니다.
BigQuery: 관심 있는 셰익스피어 데이터 세트에 대해 다른 쿼리를 실험해 보세요. 다른 샘플 쿼리는 원래 Apps Script BigQuery 샘플 앱에서 확인할 수 있습니다.
BigQuery: BigQuery의 다른 공개 데이터 세트를 실험하여 나에게 더 의미 있는 데이터 세트를 찾아보세요.
BigQuery: 앞서 셰익스피어 작품이나 기타 공개 데이터 테이블로 빌드할 수 있는 다른 쿼리를 언급했습니다. 이 웹페이지와 이 GitHub 저장소에서 확인할 수 있습니다.
Sheets: 차트 갤러리에서 다른 차트 유형을 실험해 보세요.
Sheets 및 BigQuery: 자체 대규모 스프레드시트 데이터 세트를 사용합니다. 2016년에 BigQuery팀은 개발자가 시트를 데이터 소스로 사용할 수 있는 기능을 도입했습니다. 자세한 내용은 Google BigQuery와 Google Drive 통합을 참고하세요.
슬라이드: 빅데이터 분석과 관련된 이미지나 기타 애셋과 같은 다른 슬라이드를 생성된 프레젠테이션에 추가합니다. Slides 내장 서비스의 참조 문서는 다음과 같습니다.
Google Workspace: Apps Script에서 다른 Google Workspace 또는 Google 내장 서비스를 사용합니다. 예를 들어 Gmail, Calendar, Docs, Drive, Maps, Analytics, YouTube 등의 서비스와 기타 고급 서비스가 있습니다. 자세한 내용은 기본 제공 서비스와 고급 서비스의 참조 개요를 참고하세요.