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

Google スプレッドシートと Google スライドを使用して、ビッグデータから分析情報を取得

データサイエンティストがビッグデータ分析を行うためのツールはいろいろありますが、結局のところ、これらの結果を管理者に正当化する必要はないのでしょうか？紙やデータベースに書かれている数値の多くは、主要な関係者にしか提示できません。この中級の Google Apps Script Codelab では、Google のデベロッパープラットフォームである G Suite と Google Cloud Platform（GCP）のペアを活用して、その全体像に到達できるようにします。

Google Cloud のデベロッパーツールを使用すると、詳細なデータ分析を実施し、その結果を取得してスプレッドシートに保存し、そのデータを使用してスライドプレゼンテーションを生成できます。これにより、結果を管理する適切な段階を提供できます。この Codelab では、GCP の BigQuery API（Apps Script 高度なサービス）と、Google スプレッドシートおよび Google スライドの組み込みの Apps Script サービスについて説明します。

モチベーション/先行アート

この Codelab のサンプルアプリは、次のコードサンプルに基づいています。

Google Apps Script BigQuery Service サンプルアプリ（GitHub でオープンソース化）
サンプルアプリ: スプレッドシートデータからスライドを生成する（デベロッパー向け）とこちらのブログ投稿で公開
Google Sheets API Codelab に掲載されているサンプルアプリ

slides API のサンプルアプリにも BigQuery と Google スライドがありますが、この Codelab のサンプルアプリとは次の点で異なります。

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

この Codelab では、Google Cloud のさまざまな機能と API を実際のユースケースに近づけながら、多くのテクノロジーを 1 つのアプリにまとめたいと考えました。目的は、想像力を働かせて、GCP と G Suuite の両方を活用して、組織や顧客にとって困難な問題を解決することです。

学習内容

複数の 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 でスプレッドシートサービスを使用して BigQuery データのグラフを作成する
Google スライドサービスを使用して新しいスライドプレゼンテーションを作成する
すべての新しいスライド資料に対して自動的に作成されるデフォルトのタイトルスライドにタイトルとサブタイトルを追加する
データテーブルを含む新しいスライドを作成し、スプレッドシートのデータセルをインポートする
別のスライドを追加し、そのスプレッドシートにスプレッドシートのグラフを追加する

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 サーバーで実行されるため、アプリのホスティングが不要です。

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

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

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

組み込みサービスが提供する高レベルのメソッドを使用すると、G Suite または Google の商品データやその他の便利なユーティリティメソッドにアクセスできます。高度なサービスは、G Suite または Google REST API のシンラッパーにすぎません。高度なサービスでは、REST API のすべての機能を使用できるうえ、多くの場合、組み込みサービス以上の機能を使用できますが、コードの複雑さが増します（REST API 自体よりも使い方は簡単です）。高度なサービスを使用するには、事前にスクリプトプロジェクトに対して有効にしておく必要があります。

可能であれば、組み込みサービスをおすすめします。高度サービスよりも使いやすく、手間のかかる作業が伴います。ただし、一部の Google API には組み込みサービスがないため、高度なサービスしか利用できない場合があります。Google BigQuery はその一例です。組み込みサービスはありませんが、BigQuery の高度なサービスはあります。（サービスを使用しないほうがよいでしょう）。BigQuery を初めて使用する場合は、数テラバイト規模の非常に大規模なデータコーパスに対してシンプル（または複雑な）クエリを実行できる GCP サービスですが、数秒で結果を得ることができます。

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

BigQuery とは異なり、Google スプレッドシートと Google スライドには組み込みのサービスがあります（高度なサービスでは、API でのみ利用できる機能にのみアクセスできます）。組み込みのスプレッドシートとスライドのサービスについては、事前に各ドキュメントをご覧ください。もちろん、高度なサービスに関するドキュメントもご用意しています（Google スプレッドシートと Google スライド）。

はじめに

この最初のタスクでは、この Codelab について簡単に説明します。実際のところ、ここで実際の作業が終わると、Codelab 全体の半分が完了します。いくつかのサブセクションに分かれている場合、次のすべての作業を行います。

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

セットアップ

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

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

c）どちらも行わない場合、画面は次のようになります。その場合は、左上隅でハンバーガー メニューアイコンを探し、[+ 新しいスクリプト] を選択します。

d）コマンドラインを使用する方向け。ツールは clasp です。具体的には、clasp create コマンドを実行します。

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

新しいプロジェクトの開始時にどちらの手法を使用したかにかかわらず、次のような Apps Script コードエディタの画面が表示されるはずです。
[File > Save] をクリックして、プロジェクトに名前を付けます。
次に、BigQuery クエリを実行するために、Google Cloud Console プロジェクトを作成する必要があります。

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

プロジェクトの作成が完了すると、ページの右上に通知が表示されます。Create Project: <Project Name> エントリをクリックしてプロジェクトを開きます。
左上のメニューアイコンをクリックして、[APIs & Services > Credentials] に移動します。[OAuth 同意画面] タブをクリックします（直接リンク）。
[Application name] フィールドに「Big Data Codelab」と入力し、下部の [保存] ボタンをクリックします。
右上にあるその他アイコンをクリックしてメニューを展開し、[Project settings]（直接リンク）を選択します。
[プロジェクト番号] に表示されている値をコピーします。（この Codelab では、後で使用するフィールド Product ID を使用します）。
App Script エディタに戻り、[リソース > Cloud Platform プロジェクト] をクリックします。
テキストボックスにプロジェクト番号を入力し、[プロジェクトを設定] をクリックします。プロンプトが表示されたら、[確認] をクリックします。
完了したら、[閉じる] ボタンをクリックしてダイアログを閉じます。
新しいプロジェクトの設定が完了したら、BigQuery の高度なサービスを有効にする必要があります。[リソース] - [高度な Google サービス] を選択して、BigQuery API のビットをオンにします。
下部に「これらのサービス」は「Google Cloud Platform API ダッシュボード」でも有効になっている必要があるため、このリンクをクリックすると、デベロッパーコンソールまたは「devconsole」への別のブラウザタブが開きます。
DevConsole で、上部にある [+ API とサービスの有効化] ボタンをクリックし、「BigQuery Data Transfer API ではなく BigQuery API」を選択して [有効にする] をクリックします。このブラウザタブは開いたままにしておきます。

注: API が有効になると、「このページで認証情報を作成する必要がありますが、この API を使用するには認証情報が必要です」という注記がページに表示されます。
コードエディタのブラウザタブに戻ると、[Google サービスの詳細設定] メニューが表示されているので、[OK] をクリックしてダイアログを閉じ、コードエディタを開いたままにします。上部のプロジェクト名をクリックし、「BigQuery demo」のように任意の名前を付けます。Google は「ファイナルマイル」という名前を付けます。

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

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

下のボックスのコードをコピーして、コードエディタのすべてのコードを貼り付けます。

// 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 が必要であるため、アプリケーションコードに追加する必要があります。これを行うには、デベロッパーコンソールページのあるブラウザウィンドウまたはタブに戻ります。（開いたままにしておきます、覚えていますか？）
Google アカウントのアバターの左上にあるプルダウンメニューセレクタ（）をクリックして、[プロジェクト設定] を選択します。プロジェクト名、ID、番号が表示されます。プロジェクト ID をコピーし、bq-sheets-slides.js の先頭の PROJECT_ID 変数をデベロッパーコンソールから取得した値に設定します。注: メニューセレクタが固定され、動作しなくなった場合は、ページを再読み込みしてください。
if ステートメントは、プロジェクト ID を指定せずにアプリケーションが続行されないようにするものです。追加したらファイルを保存し、メニューバーの [Run > Run Function > runQuery] を選択して [Review Permissions] ダイアログをクリックします。このアプリは未確認です。以下は、次のステップを示すアニメーション GIF（別のアプリの場合）です。
権限の確認をリクエストすると、上記のような新しいダイアログが表示されます。スクリプトを実行する適切な Google アカウントを選択し、[詳細設定] を選択して下にスクロールし、[&<プロジェクト名>（安全ではない）に移動] をクリックして OAuth2 アプリケーションの認証画面を表示します。（この画面が表示された理由と、下の OAuth2 の認証ダイアログの詳細については、確認プロセスをご覧ください）。

注: アプリを承認した後には、実行するたびにこの手順を繰り返す必要はありません。このチュートリアルの後半のタスク 3 に戻って再びこのダイアログ画面が表示され、Google スライドのプレゼンテーションを作成、管理する権限をユーザーに求める必要があります。
OAuth2 ダイアログウィンドウで [許可] をクリックすると、スクリプトの実行が開始され、上部にパステルイエローのダイアログが表示されます。実行がかなり速いため、実行中や実行が完了したことに気づかない可能性があります。
このダイアログは完了すると消えるため、表示されない場合は終了している可能性があります。Google ドライブ（drive.google.com）に移動して、シェイクスピア全作品の中の最も一般的な単語や、「QUERY_NAME」変数に割り当てた名前のうち、最も一般的な単語を確認してください。
スプレッドシートを開くと、10 行の単語とその合計数が降順で表示されます。

タスク 1 の概要

何が起こったのかを認識する... シェイクスピアのすべての作品に対してクエリを実行するコードを実行しました（膨大な量ではありませんが、テキストは毎回すべての単語を調べ、その単語のカウントを管理し、出現の降順で並び替えるよりも簡単です）。ユーザーに代わって BigQuery にこれを行うよう依頼しただけでなく、Google スプレッドシートの Apps Script の組み込みサービスを使って、このデータを簡単に使用できるようにしました。

上で貼り付けた bq-sheets-slides.js（選択したファイル名）のコード（実際のプロジェクト ID を含む PROJECT_ID を除く）は、この Codelab の GitHub リポジトリ（github.com/googlecodelabs/bigquery-sheets-slides）にある step1 フォルダにも含まれています。このコードは、BigQuery の高度なサービスページでオリジナルの例から大きな変化を受け、10 文字以上の、シェイクスピアが最もよく使う単語をクエリするためのクエリでした。そのサンプルは GitHub リポジトリにもあります。

シェイクスピアの作品や公的なデータ表などにお試しいただけるその他のクエリについては、こちらのページとこちらのページをご覧ください。どのクエリを使用する場合でも、Apps Console でクエリを実行する前にいつでも 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() にスプレッドシートオブジェクトが必要なため、createColumnChart() に渡すことができるように、spreadsheet オブジェクトを返すように runQuery() を更新する必要があります。Google スプレッドシートの作成に成功したら、runQuery() の最後、ログ行
の直後に spreadsheet オブジェクトを返します。

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

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

createBigQueryPresentation() 関数の作成BigQuery とグラフ作成機能を論理的に分離することをおすすめします。次に、アプリを動作させるための createBigQueryPresentation() 関数を作成し、runQuery() と createColumnChart() の両方を呼び出します。追加するコードは次のようになります。

/**
 * 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 スプレッドシートが取得されますが、今回はグラフがデータの横に表示されます。

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

スライド資料を作成スライド資料のすべての作業が createSlidePresentation() で行われ、createColumnChart() の直後に bq-sheets-slides.js に追加されます。新しいスライド資料の作成から始めましょう。その後、すべての新しいプレゼンテーションに付属するデフォルトのタイトルスライドにタイトルとサブタイトルを追加します。

/**
 * 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() はすでに調整しましたが、グラフ（EmbeddedChart）オブジェクトを返すように、同様の変更を createColumnChart() に加える必要があります。アプリケーションに戻り、コードに戻り、createColumnChart() の最後に最後の行を追加します。

  // 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 スライドのプレゼンテーションを表示、管理するには、アプリからの権限の付与がもう 1 つ必要となります。この権限を許可すると、以前と同様に実行されます。
作成したスプレッドシートに加えて、以下のように 3 つのスライド（タイトル、データ表、データグラフ）で構成される新しいスライドプレゼンテーションも作成されます。

これで、そこで、一般公開データセットの 1 つに対してクエリを実行する Google BigQuery リクエストを実行し、結果を格納する新しい 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 では、「Apps Script マニフェストファイル appsscript.json」の 2 番目のファイルは再生しません。ブラウザの [コードエディタ] タブに移動して、上部のメニューから [ビューとマニフェストファイルを表示] を選択するとアクセスできます。内容は次のようになります。

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
ソースコードリポジトリ
デベロッパー動画（と）ブログ投稿
Google Cloud NEXT のセッション

ドキュメント

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 は Google ドライブと統合されています（link1、link2）
Google デベロッパーブログ
Google Cloud Platform ブログ
Google Cloud ビッグデータと機械学習のブログ
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] ドキュメントのアドオンと GCP Natural Language API
[Apps Script] Hangouts Chat bot フレームワーク
[REST API] カスタムレポートツール（スプレッドシート API）
[REST API] GitHub ライセンス BigQuery アナライザ用のカスタムスライド生成ツール（スライド + BigQuery API）

リファレンスアプリ

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

ここでは、さまざまな「コードに関する課題」をご紹介します。この Codelab で作成したサンプルをさらに強化または強化する方法を紹介します。このリストはすべてを網羅しているわけではありませんが、次の取り組みに役立つヒントをいくつか示しています。

アプリケーション。JavaScript や Apps Script による制限を受けたくない場合は、このアプリケーションを、Google BigQuery、Google スプレッドシート、Google スライドで REST API を使用するお好みのプログラミング言語に移植します。
BigQuery: シェイクスピアデータセットの別のクエリを試してみてください。興味のありそうなクエリが見つかるかもしれません。その他のサンプルクエリは、元の Apps Script BigQuery サンプルアプリにあります。
BigQuery: BigQuery のその他の一般公開データセットをいくつか試してみてください。自分にとってより適切なデータセットがあるかもしれません。
BigQuery: 先日、シェイクスピアの作品や公的なデータテーブルなどに対してお試しいただける他のクエリについても記載しており、こちらのウェブページとこちらのページを再度共有させていただきます。
スプレッドシート。他の種類のグラフを試す。
スプレッドシートと BigQuery。表を裏返します。スプレッドシートのどこかに大きなデータセットがあるかもしれません。2016 年、BigQuery チームは、デベロッパーがスプレッドシートとしてスプレッドシートを使用できるようにする機能を導入しました（詳しくは、ブログ投稿 1 と 2 を参照）。
スライド生成されたプレゼンテーションに、ビッグデータ分析に接続された画像やアセットなど、他のスライドを追加します。詳しくは、スライドの組み込みサービスに関するガイドをご覧ください。
G Suite。Apps Script など、他の G Suite または Google の組み込みサービス（Gmail、Google ドライブ、カレンダー、ドキュメント、マップ、アナリティクス、YouTube などの高度なサービス。詳細については、組み込みサービスと高度なサービスのリファレンスの概要をご覧ください。