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

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

データサイエンティストがビッグデータ分析を行うためのツールは数多くありますが、最終的にはその結果を経営陣に説明する必要があります。データベースに収集した生の数値データは、印刷物として見せたとしてもほとんど役に立ちません。この中級の Google Apps Script コードラボでは、G Suite と Google Cloud Platform（GCP）という 2 つの Google デベロッパープラットフォームを活用して、データを効果的に提示できるようにします。

Google Cloud のデベロッパーツールを使用すると、詳細なデータ分析を実行し、その結果をスプレッドシートにまとめ、そのデータを使用してスライドプレゼンテーションを生成できます。これにより、経営陣に結果を伝えるためのより適切なステージが提供されます。この Codelab では、GCP の BigQuery API（Apps Script の拡張サービス）と、Google スプレッドシートおよび Google スライド向けの組み込み Apps Script サービスについて説明します。

動機/先行技術

この Codelab のサンプルアプリは、他のコードサンプルに触発されたものです。

Google Apps Script BigQuery Service のサンプルアプリと GitHub でのオープンソース
スプレッドシートのデータからスライドを生成するデベロッパー動画で紹介され、こちらのブログ投稿で公開されているサンプルアプリ
Google Slides API のコードラボで紹介されているサンプルアプリ

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

Node.js アプリと Apps Script アプリ
Apps Script サービスを使用しているときに REST API を使用する
Google ドライブは使用するが Google スプレッドシートは使用しない。このアプリはスプレッドシートは使用するがドライブは使用しない

この Codelab では、実際のユースケースにより近い形で Google Cloud 全体の機能と API を紹介しながら、できるだけ多くのテクノロジーを 1 つのアプリに統合したいと考えました。その目的は、皆様が自社や顧客の複雑な問題を解決するにあたり GCP と G Suite をどのように活用できるかを示すことにあります。

学習内容

複数の Google サービス（GCP と G Suite）で Google Apps Script を使用する方法
Google BigQuery を使用してビッグデータ分析を行う方法
Google スプレッドシートを作成してデータを入力する方法
スプレッドシートで新しいグラフを作成する方法
スプレッドシートのグラフとデータを Google スライドのプレゼンテーションに移行させる方法

必要なもの

インターネットアクセスとウェブブラウザ
Google アカウント（G Suite アカウントの場合、管理者の承認が必要となる可能性があります）
JavaScript の基本的なスキル
Apps Script 開発の知識があると役立ちますが、必須ではありません

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

情報収集のために読み、必要に応じて技術系の同僚に転送する

できる限り読み進め、できる限り多くの演習を試す

何があっても、Codelab 全体を完了する

G Suite デベロッパーツールと API のご利用経験についてどのように評価されますか？

初心者

中級者

上級者

Apps Script のご利用経験についてお答えください。

初心者

中級者

上級者

GCP デベロッパーツールと API のご利用経験について、満足度をお聞かせください。

初心者

中級者

上級者

この Codelab の概要を把握したところで、具体的に何を行うのかを見ていきましょう。

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

まず、Apps Script、BigQuery、スプレッドシート、スライドの概要について説明します。

Google Apps Script と BigQuery

Google Apps Script は、Google REST API よりも高レベルの機能を持った G Suite 開発プラットフォームです。サーバーレスな開発環境およびアプリケーションホスティング環境であり、多様なスキルレベルのデベロッパーが使用できます。Apps Script とは「G Suite の自動化、拡張、統合を可能にするサーバーレスの JavaScript ランタイム」であると言えます。

Apps Script は Node.js に類似したサーバーサイド JavaScript ですが、高速な非同期のイベント駆動型アプリケーションホスティングに比べ、G Suite やその他の Google サービスとの緊密な統合が重視されています。また、従来とはまったく異なる開発環境を特徴としており、次のことが可能になります。

ブラウザベースのコードエディタでの開発に加え、Apps Script 用のコマンドラインデプロイツールである clasp を使用すれば、ローカルでの開発も可能です。
G Suite やその他の Google サービスまたは外部サービスに（Apps Script の URLfetch サービスや Jdbc サービス経由で）アクセスできるようカスタマイズされた特殊な JavaScript でコーディングを行うことができます。
Apps Script で自動的に処理されるため、認証コードを作成する必要がありません。
アプリは常にクラウドの Google サーバーで実行されるため、アプリのホスティングが不要です。

注: Apps Script の詳細は、この Codelab では扱いません。さまざまなオンラインリソースでご確認いただけます。公式ドキュメントには、概要とクイックスタート、チュートリアル、動画も用意されています。最後に、この Codelab を始める前に、Apps Script の入門 Codelab を完了しておくことを忘れないでください。

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

組み込み/ネイティブサービス
拡張サービス

組み込みサービスは、G Suite または Google サービスのデータ、あるいはその他の有用なユーティリティメソッドへのアクセスに使用できる高レベルの方法です。拡張サービスは G Suite または Google REST API のシンラッパーです。拡張サービスでは REST API をフルに利用して、組み込みサービスよりも高度な機能を開発できますが、それだけコードが複雑になります（ただし REST API 自体よりも簡単に使用できます）。拡張サービスは、使用する前にスクリプトプロジェクトに対して有効にしておく必要もあります。

可能な場合は、組み込みサービスの使用をおすすめします。組み込みサービスは使いやすく、手間のかかる作業を拡張サービスよりも簡単に行うことができます。ただし、組み込みサービスが用意されていない Google API の場合は、拡張サービスが唯一の方法になります。その一例としては、Google BigQuery が挙げられます。Google BigQuery では、組み込みサービスは用意されていませんが、BigQuery 拡張サービスが用意されているため、拡張サービスを使用することになります。BigQuery とは、テラバイト単位の大規模なデータコーパスに対して、単純なクエリから複雑なクエリまでを実行できる GCP サービスです。結果は数秒で得られます。

Apps Script から Google スプレッドシートやスライドへのアクセス

BigQuery とは異なり、Google スプレッドシートと Google スライドにはどちらにも組み込みサービス（および拡張サービス。拡張サービスは API でしか使用できない機能にアクセスする場合にのみ使用します）が用意されています。コードを使用する前に、組み込みのスプレッドシートサービスとスライドサービスの両方のドキュメントをご覧ください。もちろん、高度なサービスのドキュメントもあります。スプレッドシートとスライドのドキュメントをそれぞれご覧ください。

はじめに

この最初のタスクは、この Codelab の大きな部分を占めています。実際、このセクションを終了すれば、Codelab 全体の半分が終了したことになります。このセクションでは、次のことを行います。

Google Apps Script プロジェクトを新たに開始する
BigQuery 拡張サービスへのアクセスを有効にする
開発エディタに移動してアプリケーションソースコードを入力する
アプリ承認プロセス（OAuth2）を実行する
BigQuery にリクエストを送信するアプリケーションを実行する
BigQuery の結果から新規に作成した Google スプレッドシートを表示する

セットアップ

a) script.google.com に移動して、新しい Apps Script プロジェクトを作成します。各種の G Suite サービスがあり、また新しいプロジェクトの作成方法はバージョンによって異なります。Gmail アカウントのみを使用しており、プロジェクトの開発を初めて行う場合は、空白の画面と最初のプロジェクトを作成するボタンが表示されます。

b）それ以外の場合は、すべてのプロジェクトと左上に大きな [+新規 ] ボタンが表示されることがあります。その場合は、そのボタンをクリックします。

c）上記に該当しない場合は、次のような画面が表示されることがあります。その場合は、左上のハンバーガー メニューアイコンを探して、[+New script] を選択します。

d) コマンドラインを使用したい場合は、ツールは clasp です。具体的には、clasp create コマンドを実行します。

e) 新しいスクリプトプロジェクトを作成する最後の方法は、ショートカットリンク（https://script.google.com/create）にアクセスすることです。

新しいプロジェクトの開始に使用した方法に関係なく、最終的には Apps Script コードエディタ（次のスクリーンショットのような画面）が表示されます。
[ファイル] > [保存] をクリックして、プロジェクトに名前を付けます。
次に、BigQuery クエリを実行するために、Google Cloud コンソールプロジェクトを作成する必要があります。

新しいプロジェクトを作成し、名前を付け、請求先アカウントを選択して、[作成] をクリックします。

プロジェクトの作成が完了すると、ページの右上に通知が表示されます。[プロジェクト「<プロジェクト名>」を作成] エントリをクリックして、プロジェクトを開きます。
左上のメニューアイコンをクリックし、[API とサービス] > [認証情報] に移動します。[OAuth 同意画面] タブ（直接リンク）をクリックします。
[アプリケーション名] フィールドに「Big Data Codelab」と入力し、下部の [保存] ボタンをクリックします。
右上にあるその他アイコンをクリックしてメニューを開き、[プロジェクト設定 ]（直接リンク）を選択します。
[プロジェクト番号] の下に表示されている値をコピーします。（この Codelab の後半で使用する [プロダクト ID] という別のフィールドがあります）。
App Script エディタに戻り、[リソース] > [Cloud Platform プロジェクト] の順にクリックします。
テキストボックスにプロジェクト番号を入力し、[プロジェクトを設定] をクリックします。確認メッセージが表示されたら、[確認] をクリックします。
完了したら、[閉じる] ボタンをクリックしてダイアログを閉じます。
新しいプロジェクトの設定が完了したら、BigQuery の拡張サービスを有効にする必要があります。[リソース] -> [Google の拡張サービス] を選択し、BigQuery API を [ON] にします。
下部に「これらのサービスを Google Cloud Platform API ダッシュボードでも有効にする必要があります。」と記載されています。そのリンクをクリックすると、別のブラウザタブでデベロッパーコンソール（「devconsole」）が開きます。
デベロッパーコンソールで、上部の [+ API とサービスの有効化] ボタンをクリックし、「bigquery」を検索して BigQuery API（BigQuery Data Transfer API ではない）を選択し、[有効にする] をクリックしてオンにします。このブラウザタブを開いたままにします。

注: API が有効になると、このページに「この API を使用するには、認証情報を作成する必要があります」などのメッセージが表示されることがありますが、現時点では気にする必要はありません。この手順は Apps Script が自動的に処理します。
コードエディタのブラウザタブに戻ると、[Advanced Google Services] メニューが表示されたままになっているので、[OK] をクリックしてダイアログを閉じ、コードエディタに戻ります。上部にあるプロジェクト名をクリックし、「BigQuery デモ」など適切な名前を付けます。ここでは「最終段階」という名前を付けました。

これで、アプリケーションコードを入力し、承認プロセスを経て、実行に取り掛かることができます。

アプリケーションをアップロードして実行する

下のボックス内のコードをコピーして、コードエディタのすべての内容を置き換えます。

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

/**
 * Runs a BigQuery query; puts results into Sheet. You must enable
 * the BigQuery advanced service before you can run this code.
 * @see http://developers.google.com/apps-script/advanced/bigquery#run_query
 * @see http://github.com/googleworkspace/apps-script-samples/blob/main/advanced/bigquery.gs
 *
 * @returns {Spreadsheet} Returns a spreadsheet with BigQuery results
 * @see http://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 BQ 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 new 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 から 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 がいかに便利であるかがわかるのではないでしょうか。

この機能を試す準備はほぼ整っていますが、まだ少し時間がかかります。このコードスニペットの上部付近に示されているように、有効なプロジェクト ID が必要です。そのため、アプリケーションコードにプロジェクト ID を追加する必要があります。この情報を取得するには、デベロッパーコンソールのページが表示されているブラウザのウィンドウまたはタブに戻ります。（開いたままにしておくように言いましたよね？）
Google アカウントのアバターの左上にあるプルダウンメニューセレクタ（）をクリックし、[プロジェクトの設定] を選択します。プロジェクト名、ID、番号が表示されます。プロジェクト ID をコピーし、bq-sheets-slides.js の上部にある PROJECT_ID 変数をデベロッパーコンソールから取得した値に設定します。注: メニューセレクタが動かなくなった場合は、ページを再読み込みしてください。
if ステートメントは、プロジェクト ID が指定されていない場合にアプリケーションがこれ以上実行されないようにするために使用されます。追加したら、ファイルを保存し、メニューバーから [実行] > [関数を実行] > [runQuery] の順に選択してコードを実行します。[許可を確認] ダイアログで [このアプリは未確認です] をクリックします。次の手順を示すアニメーション GIF（別のアプリの例）を以下に示します。
権限の確認をリクエストすると、上の図のような新しいダイアログが表示されます。スクリプトを実行する適切な Google アカウントを選択し、[詳細] を選択して下にスクロールし、[<プロジェクト名>（安全ではないページ）に移動] をクリックして OAuth2 アプリケーションの承認画面に移動します。（この画面が、ユーザーと下の OAuth2 認証ダイアログの間にある理由については、確認プロセスをご覧ください）。

注: 一度承認すれば、アプリを実行するたびにこのプロセスを繰り返す必要はありません。このダイアログは、このチュートリアルのタスク 3 に進んだときに、Google スライドプレゼンテーションを作成および管理するユーザー権限を求めるダイアログとして再度表示されます。
OAuth2 ダイアログウィンドウで [許可] をクリックすると、スクリプトの実行が開始され、上部にパステルイエローのダイアログが表示されます。実行はかなり迅速に行われるため、実行中であることや実行が完了したことに気づかない場合があります。
このダイアログは完了すると消えます。表示されていない場合は、完了している可能性があります。Google ドライブ（drive.google.com）に移動して、「Most common words in all of Shakespeare's works」という名前の新しい Google スプレッドシートを探します。または、QUERY_NAME 変数に割り当てた名前の新しい Google スプレッドシートを探します。
そのスプレッドシートを開くと、10 行にわたって、単語と合計出現数が降順で並べ替えられて表示されます。

タスク 1 の概要

何が起こったのかを認識します。シェークスピアの全作品に対してクエリを実行するコードを実行しました。これは膨大なデータではないとはいえ、自分で全作品をスキャンして全単語を調べ、単語数を管理し、出現数の降順で並べ替えることができる量よりは確実に多い量の処理になります。これを自分の代わりに BigQuery に実行させるだけでなく、Apps Script の Google スプレッドシート用組み込みサービスを使用すれば、このデータを簡単に利用できるように整理できます。

上記で貼り付けた bq-sheets-slides.js（選択したファイル名）のコード（PROJECT_ID を除く。PROJECT_ID には実際のプロジェクト ID を指定する必要があります）は、この Codelab の GitHub リポジトリ（github.com/googlecodelabs/bigquery-sheets-slides）の step1 フォルダにもあります。このコードは、BigQuery の高度なサービスページの元の例に触発されたものです。この例では、シェイクスピアが使用した 10 文字以上の単語のうち、最も一般的な単語を特定するクエリが実行されています。このサンプルは GitHub リポジトリでも確認できます。

シェイクスピアの作品や他の一般公開データテーブルに対して試すことができる他のクエリに関心がある場合は、こちらのウェブページとこちらのウェブページもご覧ください。使用するクエリに関係なく、Apps Script でクエリを実行する際には、事前に BigQuery コンソールでクエリをテストできます。BigQuery のユーザーインターフェースは、bigquery.cloud.google.com で開発者が利用できます。たとえば、BigQuery UI を使用したクエリは次のようになります。

上記の手順では Apps Script のコードエディタを使用しましたが、コマンドラインを使用してローカルで開発することもできます。必要に応じて、bq-sheets-slides.js という名前のスクリプトを作成し、上記のコードを貼り付けてから、clasp push コマンドで Google にアップロードします。（先ほどご案内した clasp のリンクと使用方法を再度ご案内します）。

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

グラフを作成する。以下の createColumnChart() の本文を runQuery() の直後の bq-sheets-slides.js に追加します。データを含むスプレッドシートを取得し、すべてのデータが反映された棒グラフをリクエストします。最初の行はデータではなく列ヘッダーであるため、データ範囲はセル A2 から開始します。

/**
 * Uses spreadsheet data to create columnar chart.
 * @param {Spreadsheet} Spreadsheet containing results data
 * @returns {EmbeddedChart} visualizing the results
 * @see http://developers.google.com/apps-script/reference/spreadsheet/embedded-chart
 */
function createColumnChart(spreadsheet) {
  // Retrieve the populated (first and only) 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 params.
  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() でスプレッドシートオブジェクトが必要となるため、spreadsheet オブジェクトを返して createColumnChart() に渡すように runQuery() を更新する必要があります。Google スプレッドシートが作成されたことをロギングしたら、runQuery() の最後（ログ行の直後）で spreadsheet オブジェクトを返します。

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

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

運転createBigQueryPresentation()機能。BigQuery とグラフ作成機能を論理的に分離するのは有効な手段です。次に、runQuery() と createColumnChart() の両方を呼び出す、アプリの処理の開始ポイントとなる createBigQueryPresentation() 関数を作成します。追加するコードは次のようになります。

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

コードの再利用性を高めます。スプレッドシートオブジェクトを返し、処理開始用の関数を作成するという、2 つの重要な手順を踏みました。ここで、runQuery() を再利用したいものの、URL のロギングは望まない同僚がいたとしたらどうでしょうか。runQuery() を一般的な用途で使いやすくするには、ログ行を移動する必要があります。移動先はどこになるでしょうか。正解は createBigQueryPresentation() です。ログ行を移動させた後は次のようになります。

/**
 * Runs a BigQuery query, adds data and a chart in a Sheet.
 */
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 フォルダにもあります）。

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

/**
 * Runs a BigQuery query; puts results into Sheet. You must enable
 * the BigQuery advanced service before you can run this code.
 * @see http://developers.google.com/apps-script/advanced/bigquery#run_query
 * @see http://github.com/googleworkspace/apps-script-samples/blob/main/advanced/bigquery.gs
 *
 * @returns {Sheet} Returns a sheet with results
 * @see http://developers.google.com/apps-script/reference/spreadsheet/sheet
 */
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 BQ 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 new 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 columnar chart.
 * @param {Spreadsheet} Spreadsheet containing results data
 * @returns {EmbeddedChart} visualizing the results
 * @see http://developers.google.com/apps-script/reference/spreadsheet/embedded-chart
 */
function createColumnChart(spreadsheet) {
  // Retrieve the populated (first and only) 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 params.
  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 in a Sheet.
 */
function createBigQueryPresentation() {
  var spreadsheet = runQuery();
  Logger.log('Results spreadsheet created: %s', spreadsheet.getUrl());
  createColumnChart(spreadsheet);
}

ファイルを保存し、コードエディタの最上部で runQuery() ではなく createBigQueryPresentation() を実行するように切り替えます。実行すると、別の Google スプレッドシートが作成されますが、今度はスプレッドシート内のデータの横にグラフが表示されます。

このコードラボの最後として、ここでは新しい Google スライドプレゼンテーションを作成します。タイトルスライドにはタイトルとサブタイトルを入力します。2 つの新しいスライドを追加して、1 つ目のスライドには各データセルを入力し、2 つ目のスライドにはグラフを入力します。

スライド資料を作成する。スライドに対する処理はすべて createSlidePresentation() で行います。この関数は、bq-sheets-slides.js の createColumnChart() の直後に追加します。最初に新しいスライドを作成し、タイトルとサブタイトルを入力します。このタイトルスライドは、プレゼンテーションを新規作成する際のデフォルトとなります。

/**
 * Create presentation with spreadsheet data & chart
 * @param {Spreadsheet} Spreadsheet with results data
 * @param {EmbeddedChart} Sheets chart to embed on slide
 * @returns {Presentation} Slide deck with results
 */
function createSlidePresentation(spreadsheet, chart) {
  // Create the new 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('via GCP and G Suite 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 of
  // the dimensions of the data range; fails if Sheet 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() を調整しましたが、今度は createColumnChart() に同様の変更を加えて、グラフ（EmbeddedChart）オブジェクトを返すようにします。アプリケーションのコードに戻って、createColumnChart() の最後にもう 1 行追加します。

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

更新createBigQueryPresentation()。createColumnChart() からグラフが返されるので、そのグラフを変数に保存し、スプレッドシートとグラフの両方を createSlidePresentation() に渡します。新規に作成されたスプレッドシートの URL をロギングするのと同様に、新しいスライドプレゼンテーションの URL もロギングしましょう。createBigQueryPresentation() を次のように更新します。

/**
 * Runs a BigQuery query, adds data and a chart in a Sheet,
 * 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 枚のスライド（タイトル、データテーブル、データグラフ）を含む新しいスライドプレゼンテーションが追加されていることを確認します。

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

技術的な側面からみれば、上述のとおりですが、大まかにいえば、ビッグデータ分析から関係者に提示できる資料の作成までを、コードを使って自動的に行ったことになります。このサンプルが、このサンプルをカスタマイズして独自のプロジェクトに活用するきっかけになれば幸いです。この Codelab の最後に、このサンプルアプリをさらに強化する方法についていくつか提案します。

最後のタスクの変更（PROJECT_ID を除く）を行うと、bq-sheets-slides.js は次のようになります（GitHub リポジトリの final フォルダにもあります）。

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.
 */

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

/**
 * Runs a BigQuery query; puts results into Sheet. You must enable
 * the BigQuery advanced service before you can run this code.
 * @see http://developers.google.com/apps-script/advanced/bigquery#run_query
 * @see http://github.com/googleworkspace/apps-script-samples/blob/main/advanced/bigquery.gs
 *
 * @returns {Spreadsheet} Returns a spreadsheet with BigQuery results
 * @see http://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 BQ 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 new 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 columnar chart.
 * @param {Spreadsheet} Spreadsheet containing results data
 * @returns {EmbeddedChart} visualizing the results
 * @see http://developers.google.com/apps-script/reference/spreadsheet/embedded-chart
 */
function createColumnChart(spreadsheet) {
  // Retrieve the populated (first and only) 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 params.
  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 & chart
 * @param {Spreadsheet} Spreadsheet with results data
 * @param {EmbeddedChart} Sheets chart to embed on slide
 * @returns {Presentation} Returns a slide deck with results
 * @see http://developers.google.com/apps-script/reference/slides/presentation
 */
function createSlidePresentation(spreadsheet, chart) {
  // Create the new 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('via GCP and G Suite 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 of
  // the dimensions of the data range; fails if Sheet 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 in a Sheet,
 * 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());
}

この Codelab で役割を果たさないのは、この Codelab の「2 番目のファイル」、つまり Apps Script マニフェストファイル appsscript.json です。コードエディタのブラウザタブに移動し、上部のメニューから [View] > [Show manifest file] を選択してアクセスできます。内容は次のようになります。

appsscript.json

{
  "timeZone": "America/Los_Angeles",
  "dependencies": {
    "enabledAdvancedServices": [{
      "userSymbol": "BigQuery",
      "serviceId": "bigquery",
      "version": "v2"
    }]
  },
  "exceptionLogging": "STACKDRIVER"
}

マニフェストファイルは、Apps Script がアプリケーションで使用可能な実行環境を把握するために使用するシステムレベルの構成ファイルです。マニフェストファイルの内容の説明はこの Codelab の範囲外ですが、その機能については理解できます。

以下に、この Codelab の内容をさらに掘り下げたり、Google デベロッパーツールにプログラムでアクセスしたりするために役立つ資料を示します。この Codelab の内容は、リポジトリのコードと同じ内容になるようにしています。

このアプリケーションのリソース

この Codelab のリンク: g.co/codelabs/bigquery-sheets-slides
ソースコード repo
デベロッパー向けの動画とブログ投稿
Google Cloud NEXT '18 セッション

ドキュメント

Google Apps Script ドキュメントサイト
Apps Script スプレッドシートサービス
Apps Script スライドサービス
Apps Script BigQuery 拡張サービス

Another Google (Apps) secret?（Apps Script 入門、動画）
Accessing Google Maps from a spreadsheet?（動画）
Google Apps Script 動画ライブラリ
Launchpad Online の動画シリーズ（以前の動画）
G Suite Dev Show 動画シリーズ

BigQuery 用の Google スプレッドシートデータコネクタ（お知らせ）
Google BigQuery integrates with Google Drive（link1、link2）
Google Developers ブログ
Google Cloud Platform ブログ
Google Cloud ビッグデータ & ML 関連ブログ
Google Developers Twitter（@GoogleDevs）
G Suite デベロッパーブログ
G Suite デベロッパー Twitter（@googleworkspace）
G Suite デベロッパーマンスリーニュースレター

その他の Codelab

初級

[REST API] G Suite と Google API（Drive API）
[App Maker] App Maker でデータベースウェブアプリを作成する

中級

[Apps Script] CLASP Apps Script コマンドラインツール
[Apps Script] Gmail アドオン
[Apps Script] Docs アドオンと GCP Natural Language API
[Apps Script] Hangouts Chat bot フレームワーク
[REST API] カスタムレポートツール（Sheets API）
[REST API] Github ライセンス BigQuery アナライザー用のカスタムスライドジェネレータ（Slides+BigQuery API）

リファレンスアプリ

Markdown から Google スライドへのコンバータ（Slides API）

以下に、この Codelab で作成したサンプルを強化または拡張するためのさまざまな「コードチャレンジ」を示します。このリストはすべてを網羅したものではありませんが、次のステップに進むためのアイデアを得るのに役立つはずです。

アプリケーション。JavaScript の使用や Apps Script によって課せられる制限に縛られたくないですか？このアプリケーションを、Google BigQuery、スプレッドシート、スライドの REST API を使用する任意のプログラミング言語に移植します。
BigQuery にエクスポートします。シェイクスピアのデータセットで別のクエリを試してみましょう。興味のあるクエリが見つかるかもしれません。別のサンプルクエリについては、元の Apps Script BigQuery サンプルアプリをご覧ください。
BigQuery にエクスポートします。BigQuery の他の一般公開データセットを試して、より有意義なデータセットを見つけてください。
BigQuery にエクスポートします。先ほど、シェイクスピアの作品や他の一般公開データテーブルに対して試すことができる他のクエリについて説明しましたが、こちらのウェブページとこちらのウェブページも再度ご紹介します。
スプレッドシート。他のグラフの種類を試してみます。
スプレッドシートと BigQuery。状況を逆転させてみましょう。スプレッドシートに大規模なデータセットがあるかもしれません。2016 年に、BigQuery チームは、デベロッパーがスプレッドシートをデータソースとして使用できる機能を導入しました（詳しくは、ブログ投稿の 1 と 2 をご覧ください）。
スライド。生成されたプレゼンテーションに、ビッグデータ分析に関連付けられた画像などのアセットを追加します。スライドの組み込みサービスに関するガイドをご覧ください。
G Suite。Apps Script から他の G Suite サービスや Google 組み込みサービスを使用する。Gmail、Google ドライブ、カレンダー、ドキュメント、マップ、アナリティクス、YouTube などのサービスや、その他の高度なサービス。組み込みサービスと高度なサービスの両方の詳細については、リファレンスの概要をご覧ください。