GSP240
概要
データ サイエンティストがビッグデータを分析するためのツールは各種あります。しかし、分析結果について経営層や利害関係者に説明し、その価値を示すことが可能なツールはどれだけあるでしょうか。文書にまとめたかデータベースに収集した生の数値データを、そのまま資料として見せてもほとんど役に立ちません。この Google Apps Script ラボでは、Workspace と Google Cloud という 2 つの Google デベロッパー プラットフォームを活用して、データを効果的に提示できるようにします。
Google Cloud デベロッパー ツールを使用すれば、データを収集して処理した結果から、スライドやスプレッドシートによるプレゼンテーションを生成できるので、経営陣や利害関係者が感銘を受けるほどの深い分析と洞察力に優れた見解を提示できます。
このラボでは、Google Cloud の BigQuery API（Apps Script の拡張サービス）と、Google スプレッドシートおよび Google スライド向けの組み込み Apps Script サービスについて説明します。
このラボでは現実に即したシナリオを設定しており、使用されるアプリケーションでは、Google Cloud 全体からさまざまな機能や API を紹介しています。その目的は、皆様が自社や顧客の困難な問題を解決するにあたり Google Cloud と Workspace をどのように活用できるかを示すことにあります。
学習内容
- 複数の Google サービスで Google Apps Script を使用する方法
- BigQuery を使用してビッグデータ分析を行う方法
- Google スプレッドシートを作成してデータを入力する方法、スプレッドシート データを使用してグラフを作成する方法
- スプレッドシートのグラフとデータを Google スライド プレゼンテーションのスライドに移行させる方法
設定
[ラボを開始] ボタンをクリックする前に
こちらの手順をお読みください。ラボの時間は記録されており、一時停止することはできません。[ラボを開始] をクリックするとスタートするタイマーは、Google Cloud のリソースを利用できる時間を示しています。
このハンズオンラボでは、シミュレーションやデモ環境ではなく、実際のクラウド環境を使ってご自身でラボのアクティビティを行うことができます。そのため、ラボの受講中に Google Cloud にログインおよびアクセスするための、新しい一時的な認証情報が提供されます。
このラボを完了するためには、下記が必要です。
- 標準的なインターネット ブラウザ（Chrome を推奨）
注: このラボの実行には、シークレット モードまたはシークレット ブラウジング ウィンドウを使用してください。これにより、個人アカウントと受講者アカウント間の競合を防ぎ、個人アカウントに追加料金が発生することを防ぎます。
- ラボを完了するために十分な時間を確保してください。ラボをいったん開始すると一時停止することはできません。
注: すでに個人の Google Cloud アカウントやプロジェクトをお持ちの場合でも、このラボでは使用しないでください。アカウントへの追加料金が発生する可能性があります。
はじめに
Google Apps Script と BigQuery
Google Apps Script は、Google REST API よりも高レベルの機能を持った Workspace 開発プラットフォームです。サーバーレスな開発環境およびアプリケーション ホスティング環境であり、多様なスキルレベルのデベロッパーが使用できます。Apps Script とは「Workspace の自動化、拡張、統合を可能にするサーバーレスの JavaScript ランタイム」であると言えます。
Apps Script は Node.js に類似したサーバーサイド JavaScript ですが、高速な非同期のイベント駆動型アプリケーション ホスティングに比べ、Workspace やその他の Google サービスとの緊密な統合が重視されています。また、従来とはまったく異なる開発環境を特徴としており、次のことが可能になります。
- ブラウザベースのコードエディタでの開発に加え、Apps Script 用のコマンドライン デプロイツールである clasp を使用すれば、ローカルでの開発も可能です。
- Workspace やその他の Google サービスまたは外部サービスに（Apps Script の URLfetch サービスや Jdbc サービス経由で）アクセスできるようカスタマイズされた特殊な JavaScript でコーディングを行うことができます。
- Apps Script で自動的に処理されるため、認証コードを作成する必要がありません。
- アプリは常にクラウドの Google サーバーで実行されるため、アプリのホスティングが不要です。
注: Apps Script の詳細については、公式ドキュメントをご覧ください。公式ドキュメントには、概要とクイックスタート、チュートリアル、動画が用意されています。
Apps Script は次の 2 つの方法で、他の Google テクノロジーと連携できます。
組み込みサービスは、Workspace または Google サービスのデータ、あるいはその他の有用なユーティリティ メソッドへのアクセスに使用できる高レベルの方法です。拡張サービスは単に Workspace または Google REST API のシンラッパーです。拡張サービスでは REST API をフルに利用して、組み込みサービスよりも高度な機能を開発できますが、それだけコードが複雑になります（ただし REST API 自体よりも簡単に使用できます）。
拡張サービスは、使用する前にスクリプト プロジェクトに対して有効にしておく必要もあります。可能な場合は組み込みサービスの使用をおすすめします。組み込みサービスは使いやすく、手間のかかる作業を拡張サービスよりも簡単に行うことができます。ただし組み込みサービスが用意されていない Google API の場合は、拡張サービスが唯一の方法になります。その一例としては、BigQuery が挙げられます。BigQuery では、組み込みサービスは用意されていませんが、BigQuery 拡張サービスが用意されているため、拡張サービスを使用することになります。
注: BigQuery とは、テラバイト単位の大規模なデータコーパスに対して、単純なクエリから複雑なクエリまでを実行できる Google Cloud サービスです。結果は数秒で得られ、何日も何時間もかかることはありません。
Apps Script から Google スプレッドシートやスライドへのアクセス
BigQuery は Apps Script の拡張サービスとしてのみ利用できます。ただし Google スプレッドシートと Google スライドではどちらにも Apps Script 組み込みサービスと拡張サービスが用意されており、API でしか使用できない機能にもアクセスできます。可能な場合は、拡張サービスではなく同等の組み込みサービスを使用することをおすすめします。組み込みサービスでは、さらに高レベルのコンストラクトと効率的な呼び出しが可能で、開発が容易になります。
注: コードを使用する前に詳細を確認するには、スプレッドシート サービスとスライド サービスをご覧ください。
タスク 1. BigQuery に対してクエリを実行して、結果をスプレッドシートにロギングする
この最初のタスクは、このラボの大きな部分を占めています。このセクションを終了すれば、ラボ全体の半分が終了したことになります。
このセクションでは次のことを行います。
- Google Apps Script プロジェクトを新たに開始する
- BigQuery 拡張サービスへのアクセスを有効にする
- 開発エディタに移動してアプリケーション ソースコードを入力する
- アプリ承認プロセス（OAuth2）を実行する
- BigQuery にリクエストを送信するアプリケーションを実行する
- BigQuery の結果から新規に作成した Google スプレッドシートを表示する
新しい Apps Script プロジェクトを作成する
-
script.google.com に移動して Apps Script プロジェクトを新しく作成します。このラボでは [Apps Script を作成] リンクをクリックします。
![[Apps Script を作成] リンク](https://cdn.qwiklabs.com/Q516KFaFVWvItcwdOEbC47bQECi0ITf9cgVUMcxPqu0%3D)
-
Apps Script コードエディタが開きます。
-
上部にあるプロジェクト名（上の図の [無題のプロジェクト]）をクリックして、プロジェクトに名前を付けます。
-
[プロジェクトの名前を変更] ダイアログで、選択に応じてプロジェクトに名前を付け（たとえば、「BigQuery」、「Sheets」、「Slides demo」など）、[名前を変更] をクリックします。
BigQuery 拡張サービスを有効にする
新しいプロジェクトで BigQuery の拡張サービスを有効にし、BigQuery API を有効にします。
- [サービス] の横にある「サービスを追加」アイコンをクリックします。
- [サービスを追加] ダイアログで、該当するサービスと API を選択します。
![[AdSense Management API] や [Admin SDK API] などのオプションが表示されている [サービスを追加] ダイアログ ボックス。](https://cdn.qwiklabs.com/Hqsge6iZVm9ThCukUYtRQ3z4HGr9UZKdkHjx6AZIAOE%3D)
- Cloud コンソールに移動し、ナビゲーション メニュー > [API とサービス] > [ライブラリ] を選択します。
![[ダッシュボード]、[ライブラリ]、[認証情報] などのオプションが表示されている [API とサービス] メニュー。](https://cdn.qwiklabs.com/62fKtxklJZlaaVMSq2K4RYAXp50HNHmNppQXaNUBWdI%3D)
- 検索ボックスに「BigQuery API」と入力するか貼り付けて、[BigQuery API] を選択します。
- 必要に応じて、[有効にする] をクリックして BigQuery API を有効にします。
![[API ライブラリ] ページ内でハイライト表示されている [有効にする] ボタン。](https://cdn.qwiklabs.com/ZCyzcGlrcjlNmeY9%2FAV0ipZh6%2F0ECwAuzMB1ISIMg1Y%3D)
-
プロジェクトに戻ります。[サービスを追加] ダイアログがまだ開いているはずです。
-
[BigQuery API] を選択し、[追加] をクリックして閉じます。
![[BigQuery API] オプションと [追加] ボタンがハイライト表示されている [サービスを追加] ダイアログ ボックス。](https://cdn.qwiklabs.com/N3MjuIlNG4e9sl2gtTFxTHHnjfKVbe%2FpAyIlxt9DO8s%3D)
アプリケーション コードを入力して実行する
これで、アプリケーション コードを入力し、承認プロセスを経て、実行に取り掛かることができます。
- 下のボックス内のコードをコピーして、コードエディタのすべての内容を置き換えます。
/**
* Copyright 2018 Google LLC
*
* バージョン 2.0 の Apache ライセンス（「ライセンス」）による使用許諾。
* ライセンスに準拠している場合を除き、このファイルは使用できません。
* ライセンスのコピーは apache.org/licenses/LICENSE-2.0 で取得できます。
*
適用される法律で定められている場合、または書面での合意がある場合を除き、
* ライセンスに従って配布されるソフトウェアは、明示か黙示かを問わず、
* あらゆる保証および条件なしに「現状有姿」で提供されます。
* ライセンスの下、権限と制限事項を規定する
* 特定の文言については、ライセンスを参照してください。
*/
// データ結果のファイル名
var QUERY_NAME = "Most common words in all of Shakespeare's works";
// この値を実際の Google Cloud API プロジェクトの ID に置き換えます
var PROJECT_ID = '<YOUR_PROJECT_ID>';
if (!PROJECT_ID) throw Error('Project ID is required in setup');
/**
* BigQuery クエリを実行して、結果をスプレッドシートに追加します。このコードを実行する前に
* BigQuery 拡張サービスを有効にする必要があります。
* @see http://developers.google.com/apps-script/advanced/bigquery#run_query
* @see http://github.com/gsuitedevs/apps-script-samples/blob/master/advanced/bigquery.gs
*
* @returns {Spreadsheet} BigQuery の結果を含むスプレッドシートを返します
* @see http://developers.google.com/apps-script/reference/spreadsheet/spreadsheet
*/
function runQuery() {
// サンプルを実際の BigQuery クエリに置き換えます。
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;
// BQ ジョブが完了するまで待ちます（指数バックオフを使用）。
var sleepTimeMs = 500;
while (!queryResults.jobComplete) {
Utilities.sleep(sleepTimeMs);
sleepTimeMs *= 2;
queryResults = BigQuery.Jobs.getQueryResults(PROJECT_ID, jobId);
}
// BigQuery からすべての結果を取得します。
var rows = queryResults.rows;
while (queryResults.pageToken) {
queryResults = BigQuery.Jobs.getQueryResults(PROJECT_ID, jobId, {
pageToken: queryResults.pageToken
});
rows = rows.concat(queryResults.rows);
}
// データが返されなかった場合は null を返します。
if (!rows) {
return Logger.log('No rows returned');
}
// 結果のスプレッドシートを新規作成します。
var spreadsheet = SpreadsheetApp.create(QUERY_NAME);
var sheet = spreadsheet.getActiveSheet();
// スプレッドシートにヘッダーを追加します。
var headers = queryResults.schema.fields.map(function(field) {
return field.name.toUpperCase();
});
sheet.appendRow(headers);
// 結果を追加します。
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;
}
}
// 行 2、列 1 のデータから格納を開始します
var START_ROW = 2; // ヘッダー行をスキップします
var START_COL = 1;
sheet.getRange(START_ROW, START_COL, rows.length, headers.length).setValues(data);
Logger.log('Results spreadsheet: %s', spreadsheet.getUrl());
}
-
メニューバーの「プロジェクトを保存」アイコンをクリックするか、Ctrl+S を押して、作成したファイルを保存します。
-
ファイルの名前を変更します。ファイル名の横にあるその他アイコンをクリックして、[名前を変更] をクリックします。
![ハイライト表示された [名前を変更] オプション](https://cdn.qwiklabs.com/qLlqIO0tJn0Vnbxmvkm0EFj62k3Z0LfktkEhn6rpKLk%3D)
- ファイル名を bq-sheets-slides.gs に変更し、Enter キーを押します。
このコードで何を行っているのか見ていきましょう。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 がいかに便利であるかがわかるのではないでしょうか。
続いて、bq-sheets-slides.gs の先頭にある PROJECT_ID 変数を設定するには、有効なプロジェクト ID が必要です。
-
<YOUR_PROJECT_ID> を実際のプロジェクト ID に置き換えます。プロジェクト ID は左側のパネルで確認できます。
以下に、サンプルのプロジェクト ID を使用したコード例を示します。実際の PROJECT_ID の値はこれとは異なります。
コードの例:
// データ結果のファイル名
var QUERY_NAME = "Most common words in all of Shakespeare's works";
// この値を実際の Google Cloud API プロジェクトの ID に置き換えます
var PROJECT_ID = '<YOUR_PROJECT_ID>';
if (!PROJECT_ID) throw Error('Project ID is required in setup');
注: このコード スニペットでは、if ステートメントを使って、プロジェクト ID が指定されていない場合にはアプリケーションがこれ以上実行されないようにしています。
注: メニュー セレクタが動かなくなった場合は、ページを再読み込みしてください。
-
ファイルを保存し、メニューバーの [実行] オプションをクリックしてコードを実行します。
-
次に、[権限を確認] をクリックします。
注:「Exception: BigQuery API サービスが有効になっていません」というエラーが発生した場合は、BigQuery API サービスを削除してもう一度追加してください。
- [qwiklabs.net のアカウントを選択] ダイアログで、使用するユーザー名をクリックし、[許可] をクリックします。
注: 一度承認すれば、アプリを実行するたびにこのプロセスを繰り返す必要はありません。このダイアログは、このラボの「結果データをスライドに挿入する」セクションに進んだときに、Google スライド プレゼンテーションを作成および管理するユーザー権限を求めるダイアログとして再度表示されます。
- 関数が実行されると、上部に小さなメッセージ ボックスが開きます。
このメッセージ ボックスは関数の実行が終了すると閉じるため、ボックスが表示されていなければ、関数の実行が終了したと考えられます。
-
Google ドライブに移動すると、「Most common words in all of Shakespeare's works」、または
QUERY_NAME 変数に割り当てた名前の新しい Google スプレッドシートがあります。
![[マイドライブ] ウィンドウ](https://cdn.qwiklabs.com/YKNHv6M8wxKNU27e3w%2BkWWhO9qB2GD0U2174Ilb5KTk%3D)
- そのスプレッドシートを開くと、10 行にわたって、単語と合計出現数が降順で並べ替えられて表示されます。
[進行状況を確認] をクリックして、目標に沿って進んでいることを確認します。
BigQuery に対してクエリを実行して、結果をスプレッドシートにロギングする
まとめ
ここでは、シェークスピアの全作品に対してクエリを実行するコードを実行しました。これは膨大なデータではないとはいえ、自分で全作品をスキャンして全単語を調べ、単語数を管理し、出現数の降順で並べ替えることができる量よりは確実に多い量の処理になります。これを自分の代わりに BigQuery に実行させるだけでなく、Apps Script のGoogle スプレッドシート用組み込みサービスを使用すれば、データを利用しやすいように整理できます。
Apps Script でクエリを実行する際には、事前に BigQuery コンソールでクエリをテストできます。また、開発者は BigQuery のユーザー インターフェースを使用できます。
- Cloud コンソールに移動し、ナビゲーション メニューから [BigQuery] を選択します。
- Cloud コンソールの [BigQuery へようこそ] ダイアログで [完了] をクリックします。
BigQuery コンソールが開きます。
- クエリエディタにコードを入力し、[実行] をクリックします。
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
![[実行] ボタンがハイライト表示されているクエリエディタ](https://cdn.qwiklabs.com/YbgBLrccmvdayPUxW4rYtNRrNtrHEolWazWs52gQ1mE%3D)
タスク 2. Google スプレッドシートでグラフを作成する
スクリプト エディタに戻ります。ここまでで、シェークスピア作品についてクエリを実行するアプリのコードを書き、並べ替えた結果をスプレッドシートで表示しました。このコードでは runQuery() 関数が BigQuery と通信し、結果をスプレッドシートに送信しました。ここからは、データをグラフ化するコードを追加していきます。このセクションでは、createColumnChart() という新しい関数を作成してスプレッドシートの newChart() メソッドを呼び出し、データをグラフ化します。
createColumnChart() 関数はデータを含むスプレッドシートを取得し、すべてのデータが反映された棒グラフをリクエストします。最初の行はデータではなく列ヘッダーであるため、データ範囲はセル A2 から開始します。
- グラフを作成する:
bq-sheets-slides.gs で runQuery() の直後（コードの最終行の後）に createColumnChart() 関数を追加します。
/**
* スプレッドシートのデータを使用して棒グラフを作成します。
* @param {Spreadsheet} 結果データを含むスプレッドシート
* @returns {EmbeddedChart} 結果を可視化
* @see http://developers.google.com/apps-script/reference/spreadsheet/embedded-chart
*/
function createColumnChart(spreadsheet) {
// 入力済みのスプレッドシート（最初で唯一のもの）を取得します。
var sheet = spreadsheet.getSheets()[0];
// スプレッドシートのデータ範囲はセル A2 から B11 までです
var START_CELL = 'A2'; // ヘッダー行をスキップします
var END_CELL = 'B11';
// スプレッドシートのセル E5 を始点としてグラフを配置します。
var START_ROW = 5; // 行 5
var START_COL = 5; // 列 E
var OFFSET = 0;
// 上のパラメータを使用して、グラフを作成し、スプレッドシートに配置します。
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() に渡せるようにアプリを調整しています。Google スプレッドシートが作成されたことをロギングしたら、runQuery() の最後でこのオブジェクトを返します。
-
最終行（Logger.log で始まる行）を次のように置き換えます。
Logger.log('Results spreadsheet created: %s', spreadsheet.getUrl());
// 後で使用できるようにスプレッドシート オブジェクトを返します。
return spreadsheet;
}
-
createBigQueryPresentation() 関数を記述する: BigQuery とグラフ作成機能を論理的に分離するのは有効な手段です。処理の開始ポイントとなる createBigQueryPresentation() 関数を作成し、そこから runQuery() と createColumnChart() を呼び出します。追加するコードは次のようになります。
/**
* BigQuery クエリを実行して、データとグラフをスプレッドシートに追加します。
*/
function createBigQueryPresentation() {
var spreadsheet = runQuery();
createColumnChart(spreadsheet);
}
- 次のコードブロックの直後に
createBigQueryPresentation() 関数を追加します。
// データ結果のファイル名
var QUERY_NAME = "Most common words in all of Shakespeare's works";
// この値を実際の Google Cloud API プロジェクトの ID に置き換えます
var PROJECT_ID = '<YOUR_PROJECT_ID>';
if (!PROJECT_ID) throw Error('Project ID is required in setup');
- コードの再利用を容易にする: スプレッドシート オブジェクトを返し、処理開始用の関数を作成するという、2 つの重要な手順を踏みました。ここで、
runQuery() を再利用したいものの、URL のロギングは望まない同僚がいたとしたらどうでしょうか。
runQuery() を一般的な用途で使いやすくするには、ログ行を移動します。移動先はどこになるでしょうか。正解は createBigQueryPresentation() です。
ログ行を移動させた後は次のようになります。
/**
* BigQuery クエリを実行して、データとグラフをスプレッドシートに追加します。
*/
function createBigQueryPresentation() {
var spreadsheet = runQuery();
Logger.log('Results spreadsheet created: %s', spreadsheet.getUrl());
createColumnChart(spreadsheet);
}
上記のように変更すると、bq-sheets-slides.js は次のようになります（PROJECT_ID は異なります）。
/**
* Copyright 2018 Google LLC
*
* バージョン 2.0 の Apache ライセンス（「ライセンス」）による使用許諾。
* ライセンスに準拠している場合を除き、このファイルは使用できません。
* ライセンスのコピーは apache.org/licenses/LICENSE-2.0 で取得できます。
*
適用される法律で定められている場合、または書面での合意がある場合を除き、
* ライセンスに従って配布されるソフトウェアは、明示か黙示かを問わず、
* あらゆる保証および条件なしに「現状有姿」で提供されます。
* ライセンスの下、権限と制限事項を規定する
* 特定の文言については、ライセンスを参照してください。
*/
// データ結果のファイル名
var QUERY_NAME = "Most common words in all of Shakespeare's works";
// この値を実際の Google Cloud API プロジェクトの ID に置き換えます
var PROJECT_ID = '<YOUR_PROJECT_ID>';
if (!PROJECT_ID) throw Error('Project ID is required in setup');
/**
* BigQuery クエリを実行して、データとグラフをスプレッドシートに追加します。
*/
function createBigQueryPresentation() {
var spreadsheet = runQuery();
Logger.log('Results spreadsheet created: %s', spreadsheet.getUrl());
createColumnChart(spreadsheet);
}
/**
* BigQuery クエリを実行して、結果をスプレッドシートに追加します。このコードを実行する前に
* BigQuery 拡張サービスを有効にする必要があります。
* @see http://developers.google.com/apps-script/advanced/bigquery#run_query
* @see http://github.com/gsuitedevs/apps-script-samples/blob/master/advanced/bigquery.gs
*
* @returns {Spreadsheet} BigQuery の結果を含むスプレッドシートを返します
* @see http://developers.google.com/apps-script/reference/spreadsheet/spreadsheet
*/
function runQuery() {
// サンプルを実際の BigQuery クエリに置き換えます。
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;
// BQ ジョブが完了するまで待ちます（指数バックオフを使用）。
var sleepTimeMs = 500;
while (!queryResults.jobComplete) {
Utilities.sleep(sleepTimeMs);
sleepTimeMs *= 2;
queryResults = BigQuery.Jobs.getQueryResults(PROJECT_ID, jobId);
}
// BigQuery からすべての結果を取得します。
var rows = queryResults.rows;
while (queryResults.pageToken) {
queryResults = BigQuery.Jobs.getQueryResults(PROJECT_ID, jobId, {
pageToken: queryResults.pageToken
});
rows = rows.concat(queryResults.rows);
}
// データが返されなかった場合は null を返します。
if (!rows) {
return Logger.log('No rows returned');
}
// 結果のスプレッドシートを新規作成します。
var spreadsheet = SpreadsheetApp.create(QUERY_NAME);
var sheet = spreadsheet.getActiveSheet();
// スプレッドシートにヘッダーを追加します。
var headers = queryResults.schema.fields.map(function(field) {
return field.name.toUpperCase();
});
sheet.appendRow(headers);
// 結果を追加します。
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;
}
}
// 行 2、列 1 のデータから格納を開始します
var START_ROW = 2; // ヘッダー行をスキップします
var START_COL = 1;
sheet.getRange(START_ROW, START_COL, rows.length, headers.length).setValues(data);
Logger.log('Results spreadsheet created: %s', spreadsheet.getUrl());
// 後で使用できるようにスプレッドシート オブジェクトを返します。
return spreadsheet;
}
/**
* スプレッドシートのデータを使用して棒グラフを作成します。
* @param {Spreadsheet} 結果データを含むスプレッドシート
* @returns {EmbeddedChart} 結果を可視化
* @see http://developers.google.com/apps-script/reference/spreadsheet/embedded-chart
*/
function createColumnChart(spreadsheet) {
// 入力済みのスプレッドシート（最初で唯一のもの）を取得します。
var sheet = spreadsheet.getSheets()[0];
// スプレッドシートのデータ範囲はセル A2 から B11 までです
var START_CELL = 'A2'; // ヘッダー行をスキップします
var END_CELL = 'B11';
// スプレッドシートのセル E5 を始点としてグラフを配置します。
var START_ROW = 5; // 行 5
var START_COL = 5; // 列 E
var OFFSET = 0;
// 上のパラメータを使用して、グラフを作成し、スプレッドシートに配置します。
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);
}
-
ファイルを保存します。
-
メニューバーで [runQuery] をクリックし、プルダウンから [createBigQueryPresentation] を選択します。
-
[実行] をクリックします。
実行すると、Google ドライブに別の Google スプレッドシートが作成されますが、今度はスプレッドシート内のデータの横にグラフが表示されます。
タスク 3. 結果データをスライドに挿入する
ラボの最後として、ここでは新しい Google スライド プレゼンテーションを作成します。タイトル スライドにはタイトルとサブタイトルを入力します。2 つの新しいスライドを追加して、1 つ目のスライドには各データセルを入力し、2 つ目のスライドにはグラフを入力します。
- スライドを作成する: 最初に新しいスライドを作成し、タイトルとサブタイトルを入力します。このタイトル スライドは、プレゼンテーションを新規作成する際のデフォルトとなります。スライドに対する処理はすべて
createSlidePresentation() 関数で行います。bq-sheets-slides.gs の createColumnChart() 関数コードの直後に追加します。
/**
* スプレッドシートのデータとグラフを使用してプレゼンテーションを作成します
* @param {Spreadsheet} 結果データを含むスプレッドシート
* @param {EmbeddedChart} スライドに埋め込むスプレッドシートのグラフ
* @returns {Presentation} 結果を含むスライド
*/
function createSlidePresentation(spreadsheet, chart) {
// 新しいプレゼンテーションを作成します。
var deck = SlidesApp.create(QUERY_NAME);
// タイトル スライドを入力します。
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 スプレッドシートから新しいスライドにセルデータをインポートします。このコード スニペットを createSlidePresentation() 関数に追加します。
// コピーするデータ範囲はセル A1 から B11 までです
var START_CELL = 'A1'; // ヘッダー行を含めます
var END_CELL = 'B11';
// テーブルのスライドを追加して、データ範囲と同じサイズの
// 空のテーブルを挿入します。スプレッドシートが空の場合は失敗します。
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);
// テーブルにスプレッドシートのデータを入力します。
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 オブジェクトを返します。この最後のスニペットを関数に追加します。
// グラフのスライドを追加してグラフを挿入します。
var chartSlide = deck.appendSlide(SlidesApp.PredefinedLayout.BLANK);
chartSlide.insertSheetsChart(chart);
// 後で使用できるようにプレゼンテーション オブジェクトを返します。
return deck;
}
- グラフを返す: 最後の関数が完成したところで、振り返ってみましょう。
createSlidePresentation() ではスプレッドシート オブジェクトとグラフ オブジェクトの両方が必要です。すでに Spreadsheet オブジェクトを返すように runQuery() を調整しましたが、今度は createColumnChart() に同様の変更を加えて、グラフ（EmbeddedChart）オブジェクトを返すようにします。それには、アプリケーションに戻って createColumnChart() の最後にもう 1 行追加します。
// 後で使用できるようにグラフ オブジェクトを返します
return chart;
}
-
createBigQueryPresentation() を更新する: createColumnChart() からグラフが返されるので、そのグラフを変数に保存し、スプレッドシートとグラフの両方を createSlidePresentation() に渡します。新規に作成されたスプレッドシートの URL をロギングするのと同様に、新しいスライド プレゼンテーションの URL もロギングできます。これを行うには、以下のようにします。
/**
* BigQuery クエリを実行して、データとグラフをスプレッドシートに追加します。
*/
function createBigQueryPresentation() {
var spreadsheet = runQuery();
Logger.log('Results spreadsheet created: %s', spreadsheet.getUrl());
createColumnChart(spreadsheet);
}
上記のコードブロックを次のコードブロックに置き換えます。
/**
* BigQuery クエリを実行して、データとグラフをスプレッドシートに追加します。
* さらに、データとグラフを新しいスライド プレゼンテーションに追加します。
*/
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());
}
すべての更新が完了すると、bq-sheets-slides.gs は次のようになります（PROJECT_ID を除く）。
bq-sheets-slides.gs - final version
// データ結果のファイル名
var QUERY_NAME = "Most common words in all of Shakespeare's works";
// この値を実際の Google Cloud API プロジェクトの ID に置き換えます
var PROJECT_ID = '<YOUR_PROJECT_ID>';
if (!PROJECT_ID) throw Error('Project ID is required in setup');
/**
* BigQuery クエリを実行して、結果をスプレッドシートに追加します。このコードを実行する前に
* BigQuery 拡張サービスを有効にする必要があります。
* @see http://developers.google.com/apps-script/advanced/bigquery#run_query
* @see http://github.com/gsuitedevs/apps-script-samples/blob/master/advanced/bigquery.gs
*
* @returns {Spreadsheet} BigQuery の結果を含むスプレッドシートを返します
* @see http://developers.google.com/apps-script/reference/spreadsheet/spreadsheet
*/
function runQuery() {
// サンプルを実際の BigQuery クエリに置き換えます。
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;
// BQ ジョブが完了するまで待ちます（指数バックオフを使用）。
var sleepTimeMs = 500;
while (!queryResults.jobComplete) {
Utilities.sleep(sleepTimeMs);
sleepTimeMs *= 2;
queryResults = BigQuery.Jobs.getQueryResults(PROJECT_ID, jobId);
}
// BigQuery からすべての結果を取得します。
var rows = queryResults.rows;
while (queryResults.pageToken) {
queryResults = BigQuery.Jobs.getQueryResults(PROJECT_ID, jobId, {
pageToken: queryResults.pageToken
});
rows = rows.concat(queryResults.rows);
}
// データが返されなかった場合は null を返します。
if (!rows) {
return Logger.log('No rows returned');
}
// 結果のスプレッドシートを新規作成します。
var spreadsheet = SpreadsheetApp.create(QUERY_NAME);
var sheet = spreadsheet.getActiveSheet();
// スプレッドシートにヘッダーを追加します。
var headers = queryResults.schema.fields.map(function(field) {
return field.name.toUpperCase();
});
sheet.appendRow(headers);
// 結果を追加します。
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;
}
}
// 行 2、列 1 のデータから格納を開始します
var START_ROW = 2; // ヘッダー行をスキップします
var START_COL = 1;
sheet.getRange(START_ROW, START_COL, rows.length, headers.length).setValues(data);
// 後で使用できるようにスプレッドシート オブジェクトを返します。
return spreadsheet;
}
/**
* スプレッドシートのデータを使用して棒グラフを作成します。
* @param {Spreadsheet} 結果データを含むスプレッドシート
* @returns {EmbeddedChart} 結果を可視化
* @see http://developers.google.com/apps-script/reference/spreadsheet/embedded-chart
*/
function createColumnChart(spreadsheet) {
// 入力済みのスプレッドシート（最初で唯一のもの）を取得します。
var sheet = spreadsheet.getSheets()[0];
// スプレッドシートのデータ範囲はセル A2 から B11 までです
var START_CELL = 'A2'; // ヘッダー行をスキップします
var END_CELL = 'B11';
// スプレッドシートのセル E5 を始点としてグラフを配置します。
var START_ROW = 5; // 行 5
var START_COL = 5; // 列 E
var OFFSET = 0;
// 上のパラメータを使用して、グラフを作成し、スプレッドシートに配置します。
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 chart;
}
/**
* スプレッドシートのデータとグラフを使用してプレゼンテーションを作成します
* @param {Spreadsheet} 結果データを含むスプレッドシート
* @param {EmbeddedChart} スライドに埋め込むスプレッドシートのグラフ
* @returns {Presentation} 結果を含むスライドを返します
* @see http://developers.google.com/apps-script/reference/slides/presentation
*/
function createSlidePresentation(spreadsheet, chart) {
// 新しいプレゼンテーションを作成します。
var deck = SlidesApp.create(QUERY_NAME);
// タイトル スライドを入力します。
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');
// コピーするデータ範囲はセル A1 から B11 までです
var START_CELL = 'A1'; // ヘッダー行を含めます
var END_CELL = 'B11';
// テーブルのスライドを追加して、データ範囲と同じサイズの
// 空のテーブルを挿入します。スプレッドシートが空の場合は失敗します。
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);
// テーブルにスプレッドシートのデータを入力します。
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]));
}
}
// グラフのスライドを追加してグラフを挿入します。
var chartSlide = deck.appendSlide(SlidesApp.PredefinedLayout.BLANK);
chartSlide.insertSheetsChart(chart);
// 後で使用できるようにプレゼンテーション オブジェクトを返します。
return deck;
}
/**
* BigQuery クエリを実行して、データとグラフをスプレッドシートに追加します。
* さらに、データとグラフを新しいスライド プレゼンテーションに追加します。
*/
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());
}
- ファイルを保存して、
createBigQueryPresentation() を再度実行します。実行する前に、Google スライド プレゼンテーションの表示と管理に必要な権限を要求するプロンプトが表示されます。
- マイドライブに移動し、作成したスプレッドシートに加えて、次の 3 枚のスライド（タイトル、データテーブル、データグラフ）を含む新しいスライド プレゼンテーションが追加されていることを確認します。
お疲れさまでした
このラボでは、Google Cloud の両面を活用するアプリケーションを作成しました。具体的には、BigQuery リクエストを実行することで一般公開データセットの一つに対してクエリを実行し、取得した結果を新しい Google スプレッドシートを作成して保存し、その結果データに基づいてグラフを追加し、最終的に結果とグラフが入力されたスプレッドシートを含む Google スライド プレゼンテーションを作成しました。
技術的な側面からみれば、上述のとおりですが、大まかにいえば、ビッグデータ分析から関係者に提示できる資料の作成までを、コードを使って自動的に行ったことになります。次は、このラボの内容をカスタマイズして、現実のプロジェクトに応用してみてください。
クエストを完了する
このセルフペース ラボは、「Workspace: Integrations for Data」クエストと「BigQuery Basics for Data Analysts」クエストの一部です。クエストとは学習プログラムを構成する一連のラボのことで、完了すると成果が認められてバッジが贈られます。バッジは公開して、オンライン レジュメやソーシャル メディア アカウントにリンクできます。このラボの修了後、このラボが含まれるクエストに登録すれば、すぐにクレジットを受け取ることができます。受講可能なすべてのクエストについては、Google Cloud Skills Boost カタログをご覧ください。
ハンズオン チャレンジラボを受講して、BigQuery に関するスキルを証明し、知識を確認することもできます。このクエストを完了したら、チャレンジラボを受講して、Google Cloud の限定デジタルバッジを取得しましょう。
詳細
このラボで紹介しているコードは、GitHub でも入手できます。このラボの内容は、リポジトリのコードと同じ内容になるようにしています。以下に、このラボの内容をさらに掘り下げたり、Google デベロッパー ツールにプログラムでアクセスする他の方法を探索したりするために役立つ追加資料を示します。
ドキュメント
関連動画と一般動画
- Another Google (Apps) secret -（Apps Script 入門、動画）
- Accessing Google Maps from a spreadsheet -（動画）
- Google Apps Script のその他の動画 - 動画ライブラリ
- Launchpad Online - 動画シリーズ
- The Google Workspace Developer Show - 動画シリーズ
関連ニュース、一般ニュース、最新情報
マニュアルの最終更新日: 2023 年 3 月 6 日
ラボの最終テスト日: 2023 年 3 月 6 日
Copyright 2024 Google LLC All rights reserved. Google および Google のロゴは Google LLC の商標です。その他すべての企業名および商品名はそれぞれ各社の商標または登録商標です。