このページは Cloud Translation API によって翻訳されました。

Google スプレッドシートと Google スライドでビッグデータを分析情報に変換

1. ビッグデータ分析からスライドプレゼンテーションへ

データサイエンティストがビッグデータ分析を行うためのツールは数多くありますが、それでもアナリストは結果を経営陣に正当化する必要があります。紙やデータベースに多くの数値があっても、主要なステークホルダーにはほとんど提示できません。この中級者向け Google Apps Script Codelab では、2 つの Google デベロッパープラットフォーム（Google Workspace と Google Cloud コンソール）を使用して、最後の作業を自動化します。

Google Cloud のデベロッパーツールを使用すると、詳細なデータ分析を行うことができます。その後、結果を取得してスプレッドシートに挿入し、そのデータでスライドプレゼンテーションを生成できます。これにより、データを管理職に提供するためのより適切な方法が提供されます。この Codelab では、Cloud コンソールの BigQuery API（Apps Script の拡張サービス）と、Google スプレッドシートおよび Google スライド用の組み込み Apps Script サービスについて説明します。

目的

この Codelab のサンプルアプリは、以下の他のコードサンプルを参考にしています。

Google Apps Script BigQuery Service サンプルアプリ。GitHub でオープンソース化されています。
サンプルアプリは、スプレッドシートデータからスライドを生成するのデベロッパー向け動画で紹介されており、こちらのブログ投稿で公開されています。
Google Sheets API Codelab で紹介されているサンプルアプリ。

Google スライド API の Codelab サンプルアプリには BigQuery と Google スライドも含まれていますが、この Codelab のサンプルアプリとはいくつかの点で異なります。

Node.js アプリと Apps Script アプリ
Apps Script サービスを使用しながら REST API を使用します。
Google ドライブは使用し Google スプレッドシートは使用しませんが、このアプリは Google スプレッドシートは使用しドライブは使用しません。

この Codelab では、複数のテクノロジーを 1 つのアプリにまとめ、実際のユースケースのように Google Cloud 全体の機能と API を紹介したいと考えました。目標は、皆さんが想像力を刺激し、Google Cloud コンソールと Google Workspace の両方を使って組織と顧客の困難な問題を解決することを検討できるようにすることです。

学習内容

複数の Google サービスで Google Apps Script を使用する方法
Google BigQuery を使用してビッグデータを分析する方法
Google スプレッドシートを作成してデータを挿入する
スプレッドシートでグラフを作成する方法
スプレッドシートから Google スライドプレゼンテーションにデータやグラフを転送する方法

必要なもの

インターネットにアクセスできるウェブブラウザ
Google アカウント（Google Workspace アカウントでは管理者の承認が必要になる場合があります）
Google スプレッドシートに関する基本的な知識
スプレッドシートの A1 表記を読むことができる
JavaScript の基本的なスキル
Apps Script 開発の知識は役立ちますが、必須ではありません

2. アンケート

この Codelab/チュートリアルをどのように使用しますか？

<ph type="x-smartling-placeholder"></ph> 情報提供を目的として全記事に目を通し、場合によっては技術担当者にもお見せする

できる限り練習を重ね、演習をできるだけ多くこなす

地獄や高海で、Codelab 全体を完了します

をご覧ください。

Google Workspace デベロッパーツールと Google Workspace のAPI、

初心者

中級者

上級者

Apps Script の使用経験を具体的に評価してください。

初心者

中級者

上級者

Cloud コンソールのデベロッパーツールの使用経験をどのように評価されますか。API、

<ph type="x-smartling-placeholder"></ph> 初心者

中級

上達

をご覧ください。

3. 概要

この Codelab の概要を理解したところで、次の作業を行います。

既存の Apps Script と BigQuery のサンプルを利用して動作させます。
サンプルから、BigQuery にクエリを送信して結果を取得する方法を学習します。
Google スプレッドシートを作成し、BigQuery の結果を挿入する。
コードを変更して、返されてシートに挿入されるデータを少し変更します。
Apps Script のスプレッドシートサービスを使用して、BigQuery データのグラフを作成する。
スライドサービスを使用して Google スライドプレゼンテーションを作成する。
デフォルトのタイトルスライドにタイトルとサブタイトルを追加します。
データテーブルを含むスライドを作成し、スプレッドシートのデータセルをそこにエクスポートします。
別のスライドを作成し、スプレッドシートのグラフを挿入します。

まず、Apps Script、BigQuery、スプレッドシート、スライドの背景情報から始めましょう。

Google Apps Script と BigQuery

Google Apps Script は、Google REST API よりも上位レベルで運用される Google Workspace 開発プラットフォームです。サーバーレスの開発環境およびアプリケーションホスティング環境であり、あらゆるスキルレベルのデベロッパーが使用できます。Apps Script は基本的に、Google Workspace の自動化、拡張、統合のためのサーバーレス JavaScript ランタイムです。

Node.js に似たサーバーサイドの JavaScript を使用しますが、高速で非同期のイベントドリブン型のアプリケーションのホスティングではなく、Google Workspace や他の Google サービスとの緊密なインテグレーションに重点を置いています。また、これまでとは異なる開発環境を特徴としています。次のことが可能になります。

スクリプトの開発にはブラウザベースのコードエディタを使用しますが、Apps Script 用のコマンドラインデプロイツールである clasp を使用すれば、ローカルで開発することもできます。
Google Workspace やその他の Google サービスまたは外部サービスに（Apps Script の URL Fetch または JDBC サービスを使用して）アクセスできるようにカスタマイズされた特殊な JavaScript でコードを記述します。
Apps Script で自動的に処理されるため、認証コードの記述を回避できます。
アプリをホストする必要はありません。クラウド内の Google のサーバー上でアプリが実行されます。

Apps Script は次の 2 つの方法で他の Google テクノロジーと連携できます。

組み込みサービスとして
拡張サービスとして

組み込みサービスには、ユーザーデータ、その他の Google システム、外部システムとやり取りするための大まかな方法が用意されています。拡張サービスは基本的に、Google Workspace API または Google REST API のシンラッパーです。拡張サービスでは REST API をフルに利用して、組み込みサービス以上のことを行えますが、複雑なコードが必要になります（ただし、完全な REST API よりも簡単に使用できます）。拡張サービスは、使用する前にスクリプトプロジェクトでも有効にする必要があります。

可能な場合は組み込みサービスを使用してください。組み込みサービスは使いやすく、拡張サービス以上の機能も備えているためです。ただし、組み込みサービスが用意されていない Google API もあるため、拡張サービスが唯一の選択肢となる場合もあります。たとえば、Google BigQuery には組み込みサービスはありませんが、BigQuery Service は存在します。BigQuery サービスは、Google BigQuery API を使用して大規模なデータコーパス（数テラバイトなど）に対してクエリを実行できる Cloud コンソールサービスですが、数秒で結果を取得できます。

Google スプレッドシートとApps Script のスライド

BigQuery とは異なり、スプレッドシートとスライドにはどちらも組み込みサービスがあります。また、API でしか利用できない機能にアクセスするための高度なサービスもあります。コードの説明に入る前に、組み込みのスプレッドシートサービスとスライドサービスのドキュメントをご覧ください。スプレッドシートとスライドの両方の拡張サービスに関するドキュメントもあります。

4. タスク 1: BigQuery を実行して結果をスプレッドシートに記録する

はじめに

この Codelab の大部分はこの最初のタスクで実施します。実際に、この Codelab 全体の半分が終了したことになります。以下のことを複数のサブセクションに分けて説明します。

Google Apps Script プロジェクトと Cloud コンソールプロジェクトの両方を作成する。
BigQuery 拡張サービスへのアクセスを有効にします。
スクリプトエディタを開き、アプリケーションのソースコードを入力します。
アプリ承認プロセス（OAuth2）を操作する。
BigQuery にリクエストを送信するアプリケーションを実行します。
BigQuery の結果を使用して作成された新しい Google スプレッドシートを確認します。

セットアップ

Apps Script プロジェクトを作成するには、script.google.com に移動して [新しいプロジェクト] をクリックします。
Apps Script プロジェクトの名前を変更するには、[無題のプロジェクト] をクリックし、プロジェクトのタイトルを入力して [名前を変更] をクリックします。

次に、BigQuery でデータをクエリするための Cloud コンソールプロジェクトを作成する必要があります。

Cloud コンソールプロジェクトを作成するには、このショートカットリンクを使用してプロジェクトを作成し、プロジェクトに名前を付けて [作成] をクリックします。

プロジェクトの作成が完了すると、ページに通知が表示されます。ページの上部にあるプロジェクトリストで新しいプロジェクトが選択されていることを確認します。
メニューをクリックし、[API とサービス >OAuth 同意画面（直接リンク）。
[内部 >作成: 組織内の Google Workspace ユーザー向けのアプリを作成します。
[App name] フィールドに「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 Data Transfer API]ではなく [BigQuery 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 コンソールプロジェクト ID を必要とする PROJECT_ID 変数も宣言します。変数の下の if ステートメントは、プロジェクト ID なしでアプリケーションが続行されないようにするために存在します。

Cloud コンソールのプロジェクトに切り替え、ナビゲーションバーでその他アイコンをクリックし、[プロジェクト設定] を選択します。
[プロジェクト ID] の下にリストされている値をコピーします。
App Script エディタに戻り、bq-sheets-slides.js で PROJECT_ID 変数を見つけて値を追加します。
[保存 ] をクリックします。 >実行。
[権限を確認] をクリックして続行します。

注: gmail.com アカウントを使用している場合は、「このアプリは確認されていません」というメッセージが表示されることがあります。ダイアログが表示されます。Google はこの機能を使用して、不明な作成者または信頼できない作成者のコードを使用している可能性があるユーザーに警告します。このダイアログが表示された場合は、スクリプトの作成者であるため、続行して問題ありません。画面の指示に従って、スクリプトの承認を続けます。

未確認のアプリダイアログで [詳細設定] をクリックします。
[プロジェクト名（安全でない）に移動] をクリックします。
[許可] をクリックします。

この Codelab 全体を通して、複数の権限プロンプトが表示されることがあります。このプロセスの詳細については、Google サービスの承認をご覧ください。

スクリプトの実行が開始すると、組み込みの実行ログが開き、スクリプトのアクションがリアルタイムで記録されます。

実行ログに「Execution complete」と表示されたら、Google ドライブ（drive.google.com）に移動して「Most common words in all of Shakespeare's works」という Google スプレッドシートを見つけます。（更新した場合は QUERY_NAME 変数に割り当てた名前）。
スプレッドシートを開くと、最も一般的な 10 個の単語と、その合計数が降順で並べ替えられて表示されます。

タスク 1 の概要

振り返りとして、シェイクスピアの全作品に対してすべての演劇のあらゆる単語を調べるクエリを行うコードを実行しました。単語を数え、出現順に並べ替えました。このデータを表示するために、Google スプレッドシート用の Apps Script 組み込みサービスも使用しました。

bq-sheets-slides.js に使用したコードは、この Codelab の GitHub リポジトリの step1 フォルダ（github.com/googlecodelabs/bigquery-sheets-slides）にも格納されています。このコードは、BigQuery の高度なサービスのページの元の例から着想を得ています。シェイクスピアが使用する 10 文字以上の最も人気のある単語を取得する、少し異なるクエリを実行しました。GitHub リポジトリで例を確認することもできます。

シェイクスピアの作品を使って作成できる他のクエリや他の一般公開データテーブルに関心がある場合は、BigQuery サンプルテーブルに対してクエリを実行する方法とこちらの GitHub リポジトリをご覧ください。

Apps Script でクエリを実行する前に、Cloud コンソールの BigQuery ページを使用してクエリを実行することもできます。確認するには、メニューをクリックし、[BigQuery UI] >SQL ワークスペース（直接リンク）。たとえば、このクエリは BigQuery グラフィカルインターフェースで次のように表示されます。

BigQueryUI

5. タスク 2: Google スプレッドシートでグラフを作成する

runQuery() の目的は、BigQuery を使用し、データの結果を Google スプレッドシートに送信することです。次に、このデータを使用してグラフを作成する必要があります。Google スプレッドシートを呼び出す createColumnChart() という新しい関数を作成します。newChart() メソッドを使用します。

Apps Script エディタで、createColumnChart() 関数を bq-sheets-slides.js の runQuery() の後に追加します。コードはシートを取得し、すべてのデータが含まれた棒グラフをリクエストします。最初の行に列ヘッダーが含まれているため、データ範囲はセル 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);
}

上で、スプレッドシートオブジェクトを返すことと、エントリ関数を作成するという 2 つの重要なステップを行いました。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 ドライブに別のスプレッドシートが作成されますが、今回はデータの横のシートにグラフを追加しています。

グラフを含むシート

6. タスク 3: 結果データをスライドに挿入する

Codelab の最後の部分では、Google スライドプレゼンテーションの作成、タイトルスライドへのタイトルとサブタイトルの追加、データセルとグラフのスライドの作成を行います。

Apps Script エディタで、createSlidePresentation() 関数を bq-sheets-slides.js の createColumnChart() の後に追加します。スライド資料に関するすべての作業はこの関数で行います。まずスライド資料を作成し、デフォルトのタイトルスライドにタイトルとサブタイトルを追加しましょう。

/**
 * 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() の最後のステップでは、スライドをもう 1 つ追加し、スプレッドシートからグラフをインポートして、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() を調整していますが、グラフオブジェクト（EmbeddedChart）を返すように createColumnChart() にも同様の変更を加える必要があります。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 スライドプレゼンテーションを表示、管理するための権限をユーザーに追加しておく必要があります。これらの権限を確認して許可すると、以前と同様に実行されます。
これで、作成されたスプレッドシートに加えて、以下に示すように 3 枚のスライド（タイトル、データ表、データグラフ）を含む新しいスライドプレゼンテーションも返されます。

7. まとめ

これで、Google Cloud の両側を使用するアプリケーションが作成されました。一般公開データセットの 1 つに対してクエリを実行し、結果を保存する Google スプレッドシートスプレッドシートを作成して、データに基づいたグラフを追加し、最後にスプレッドシートのデータとグラフの結果を表示する Google スライドプレゼンテーションを作成する Google BigQuery リクエストを実行します。

技術的に行った手順は以下のとおりです。大まかに言えば、ビッグデータ分析から関係者に提示できる結果を生み出し、すべてコードを使用して自動化しました。このサンプルがご自身のプロジェクトに合わせてカスタマイズするきっかけになれば幸いです。この 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 ブログ
Twitter: Google Developers（@googledevs）
Google Workspace デベロッパーブログ
Twitter: Google Workspace デベロッパー（@workspacedevs）

その他の Codelab

入門

[Google スプレッドシート] Google スプレッドシートを使った Apps Script の基礎
[REST API] Google Workspace とGoogle API を使用してファイルとGoogle ドライブ内の複数のフォルダ

中級

[Apps Script] CLASP Apps Script コマンドラインツール
[Apps Script] Gmail 向け Google Workspace アドオン
[Apps Script] Google Chat のカスタム bot
[REST API] Google スプレッドシートをアプリケーションのレポートツールとして使用する
[REST API] BigQuery API を使用して Google スライドプレゼンテーションを生成する

9. 次のステップ: コードの課題

以下に、この Codelab で作成したサンプルを拡張または拡張する方法をいくつか示します。このリストはすべてを網羅しているわけではありませんが、次のステップに進むためのインスピレーションを与えるアイデアをいくつか紹介しています。

アプリケーション - JavaScript の使用や Apps Script による制限によって制限したくない場合このアプリケーションを、Google BigQuery、スプレッドシート、スライドで REST API を使用するお好みのプログラミング言語に移植します。
BigQuery - 別のクエリを使用して、興味のあるシェイクスピアデータセットを取得します。別のサンプルクエリは、元の Apps Script BigQuery サンプルアプリにあります。
BigQuery - BigQuery の他の一般公開データセットをいくつか試して、より意味のあるデータセットを見つけてください。
BigQuery - 先ほど、シェイクスピアの作品を使って構築できるその他のクエリ、または他の一般公開データテーブルについて説明しました。こちらのウェブページと GitHub リポジトリから入手できます。
スプレッドシート - グラフギャラリーで他の種類のグラフを試します。
スプレッドシートとBigQuery - 独自の大規模なスプレッドシートデータセットを使用します。BigQuery チームは 2016 年に、デベロッパーがスプレッドシートをデータソースとして使用できる機能を導入しました。詳しくは、Google BigQuery は Google ドライブと統合されています。
スライド - 生成されたプレゼンテーションに、ビッグデータ分析に関連する画像やその他のアセットなどのスライドを追加します。スライドの組み込みサービスのリファレンスドキュメントをご覧ください。
Google Workspace - Apps Script から他の Google Workspace サービスや Google の組み込みサービスを使用できます。たとえば、Gmail、カレンダー、ドキュメント、ドライブ、マップ、アナリティクス、YouTube など、その他の高度なサービス。組み込みサービスと拡張サービスの両方のリファレンスの概要で詳細をご確認ください。