Google Apps Scriptを使ってSpreadsheetのデータをBigQueryに連携する
アライアンス統括部 サービスグループの しんや です。
DevelopersIOのブログ投稿データを収集・加工・可視化して社内に共有することをここまで実践してきていたのですが、その際に用いている環境にはAmazon Redshiftを活用していました。先日個人的に部署異動(2023年03月01日付けでアライアンス統括部に異動)したのもあり、またデータ連携と可視化の仕組みもよりスムーズに/便利に/広範に連携出来るようにしたいと思い、「社内投稿データ分析環境をBigQueryに載せ替える手順をそれぞれブログにまとめておこう」と思い立ちました。
当エントリではその中から「Google Apps Scriptを使ってスプレッドシートのデータをBigQueryに連携する」手順についてまとめておこうと思います。
目次
データ連携イメージ(構成図)
連携イメージは至ってシンプルです。予め連携されている/データが出力されているGoogleスプレッドシートの情報を、Google Apps Scriptを介してこちらも予め用意したGoogle BigQueryの任意のテーブルに投入する...という流れです。一通りの処理が終わったらその旨をSlackチャンネルに通知するという部分まで含めています。
作成・作業手順
ここからはこのデータ連携を実現するために行った内容や手順を紹介していきます。
まずはじめに、連携用のGoogle Apps Scriptを配置するプロジェクトを作成します。Google Apps Scriptコンソールに遷移し、「新しいプロジェクト」を選択。
プロジェクトの名前に任意の名称を記入します。
コード実行の際にはBigQueryのAPIを利用するので、予めその手順を済ませておきます。画面左の「サービス」を押下し、起動したウインドウにて「BigQuery API」を指定、[追加]を押下。
画面左メニュー[サービス]の下にBigQuery APIの情報が追加されていることを確認出来ていればOKです。
またコード実行の際には以下のような確認ダイアログが表示されることもあるかと思いますので、その際は許可設定を行っておいてください。
Google BigQueryのテーブルは予め枠として用意しておき、Google Apps Scriptでそのテーブルに対して全件DELETE→全件投入(INSERT)するという手法を今回は取る事にします。(件数もそこまで多くない、マスタデータ系のデータ投入を想定/下記キャプチャはGoogle BigQuery管理コンソールのイメージ。当エントリで紹介するコードとの一致はしていないです)
実行スクリプト
上記環境設定を踏まえ、TypeScriptコードを実装。サンプルコードは以下の内容となります。
/**
* データをBigQuery環境にロード.
*/
function loadSpreadsheetDataToBigQuery() {
console.log("loadMemberListXxxxxxxx() START.");
loadXxxxxxList();
console.log("loadMemberListXxxxxxxx END.");
}
/**
* 任意のスプレッドシートにあるデータをBigQuery環境にロード.
*/
function loadXxxxxxList() {
var localdata = ""
// (1).スプレッドシートの情報を取得.
const SHEET_URL = "(スプレッドシートのURL)";
const SHEET_NAME = "(スプレッドシート内の任意のシート名)"
var spreadsheet = SpreadsheetApp.openByUrl(SHEET_URL).getSheetByName(SHEET_NAME);
var xxxxxRecords = spreadsheet.getDataRange().getDisplayValues(); // 日付データもそのまま(YYYY-MM-DD)のフォーマットで持ってきたかったのでgetDisplayValues()で取得.
console.log(xxxxxRecords.length)
// (2).ヘッダ行も含む形でデータとして読み込み.
for(let i=0;i < xxxxxRecords.length; i++) {
var user_id = xxxxxRecords[i][0];
var user_name = xxxxxRecords[i][1] + " " + xxxxxRecords[i][2];
var birthday = xxxxxRecords[i][3];
// 最後の1件だけ改行無しで格納.
if (i < xxxxxRecords.length + 1) {
localdata +=
user_id + "," + user_name + "," + birthday + "\r\n";
} else {
localdata +=
user_id + "," + user_name + "," + birthday;
}
}
console.log(localdata);
// (3).BigQuery対象テーブルの設定.
var projectId = "(接続先のプロジェクトID)"; /** プロジェクトID */
var datasetId = "(接続先プロジェクトID配下の任意のデータセットID)"; /** データセットID */
var tableId = "(データ投入・連携を行いたいテーブルのテーブルID)"; /** テーブルID */
var table = {
tableReference: {
projectId: projectId,
datasetId: datasetId,
tableId: tableId
},
schema: {
fields: [
{name: 'user_id', type: 'string'},
{name: 'user_name', type: 'string'},
{name: 'birthday', type: 'string'},
]
}
}
// (4).データ投入前に全件削除(DELETE).
try {
console.log("xxxxx#delete start.");
var deleteQuery = {
query: "#standardSQL \n DELETE FROM `(接続先プロジェクトID).(スキーマID).(テーブルID)` WHERE true;"
};
var queryResultsDelete = BigQuery.Jobs.query(deleteQuery,projectId);
console.log(queryResultsDelete);
console.log("xxxxx#delete end.");
} catch(e) {
console.log(e);
}
blob = Utilities.newBlob(localdata).setContentType("application/octet-stream")
var job = {
configuration: {
load: {
destinationTable: {
projectId: projectId,
datasetId: datasetId,
tableId: tableId
},
skipLeadingRows: 1
}
}
};
// (5).データ投入(INSERT).
try {
console.log("xxxxx#insert start.");
var queryResultsInsert = BigQuery.Jobs.insert(job, projectId, blob);
console.log(job);
console.log(queryResultsInsert)
console.log("xxxxx#insert end.");
} catch(e) {
console.log(e);
}
// (6).Slack通知.
notification_messsage = "スプレッドシートからBigQueryへの連携処理 完了";
const token = PropertiesService.getScriptProperties().getProperty("SLACK_BOT_TOKEN");
const apiResponse = callSlackNotification(token, "chat.postMessage", {
channel: "#(Slackチャンネル名)",
text: notification_messsage,
});
}
// (6).Slack通知用実行functionの作成.
function callSlackNotification(token, apiMethod, payload) {
const response = UrlFetchApp.fetch(
`https://www.slack.com/api/${apiMethod}`,
{
method: "post",
contentType: "application/x-www-form-urlencoded",
headers: { "Authorization": `Bearer ${token}` },
payload: payload,
}
);
console.log(`Web API (${apiMethod}) response: ${response}`)
return response;
}
各種コードをどのような意図や目的で記載したのかについては下記をご参照ください。(ひとまず「動けば良いや」位の気持ちで書いたのでコードの品質については御容赦ください...m(_ _)m )
- (1).スプレッドシートの情報を取得.
- 取得先のスプレッドシートをURLとシート名で指定。スプレッドシートオブジェクトからシートの情報を得られるのだが、日付データが良い感じで取れてなかったのでそれ用のメソッド(getDisplayValues())を使いました。
- スプレッドシートのセルの値をGASで取得する方法！Google APP Script｜Programmer Life
- GASによるSpreadsheetの基本操作まとめ - Qiita
- 【GAS】スプレッドシートの入力日付をそのまま文字列で取得するgetDisplayValueメソッド｜もりさんのプログラミング手帳
- (2).ヘッダ行も含む形でデータとして読み込み.
- CSVの形式でデータを得るためにそれ用の加工、文字列連携処理を実行。改行文字を含む必要があったので行位置を判定した上で改行文字も追記しています。
- (3).BigQuery対象テーブルの設定.
- お作法的な設定。対象テーブルの定義に合わせたスキーマ及びテーブルの指定を行っています。
- GASでSpreadSheetのデータをBigQueryに入れてみた｜テクニカルブログ｜日本情報通信株式会社
- (4).データ投入前に全件削除(DELETE).
- マスタデータ的なDELETE文を実行する必要があったのでその内容を踏まえたDELETE文を記載。
- 全件削除したかったのでWHERE句もそれを踏まえたものにしました。
- Delete all rows in BigQuery and other DML statements to add, modify and delete data | Google Cloud Blog
- google apps script - BigQuery errs with DELETE from AppsScript? - Stack Overflow
- GoogleSQL のデータ操作言語（DML）ステートメント | BigQuery | Google Cloud
- (5).データ投入(INSERT).
- ここはお作法に則った形で実行。
- Method: jobs.insert | BigQuery | Google Cloud
- (6).Slack通知
- 「実行内容のSlack通知」にて後述。
コードの実行
動作確認のための実行及びデバッグはApps Scriptコンソール上で行いました。
実行ログの確認も同様にコンソール上で行っています。
スケジュール設定
Google Apps Scriptのスケジュール設定を活用しました。「トリガー指定で処理を実行」「溜まった実行済みトリガーを削除する処理を実行」を行う形としています。
function setTrigger() {
var scheduledTime = new Date();
scheduledTime.setDate(scheduledTime.getDate() + 1);
scheduledTime.setHours(1);
scheduledTime.setMinutes(15);
console.log(scheduledTime);
ScriptApp.newTrigger('loadSpreadsheetDataToBigQuery').timeBased().at(scheduledTime).create();
}
function deleteTriggers() {
const triggers = ScriptApp.getProjectTriggers();
for(const trigger of triggers) {
if(trigger.getHandlerFunction() == 'loadSpreadsheetDataToBigQuery') {
ScriptApp.deleteTrigger(trigger);
}
}
}
実行内容のSlack通知
ここで実行した処理はせっかくなので所定のSlackチャンネルに通知したいなと思い、関連設定と作業を行いました。主に以下の流れです。
- 任意のSlackチャンネル(パブリック)をワークスペース内に作成
- Slack Appの作成とワークスペース管理者へのインストール依頼
- 参考：Slack API を使用してメッセージを投稿する
- スコープはいずれも
chat:writeを指定 - 設定が完了したらインストールをワークスペース管理者に依頼(Request to Install)
- 管理者からの承認が得られ次第、Appインストールを実施
- Appで利用するトークンをGoogle Apps Scriptにプロパティとして設定
- Google Apps Scriptの実装と動作確認
処理実行がなされると、以下の様にSlackチャンネルにメッセージが投稿されるようになります。
まとめ
という訳で、Google Spreadsheetの情報をGoogle Apps Scriptを使ってGoogle BigQuery環境に連携する手順についての紹介でした。
今回のエントリを含めた手順については「既存実施していた処理の置き換え」な側面が強いですが、一通り関連する作業や処理の置き換えが完了したら「これらの処理をもっと便利にもっと効率良いものに置き換える」「これらの処理で生成された情報を別途活用する」というのをモダンデータスタック(MDS)でやってみたい...という中長期の目標というか野望もあったりします。状況が整い次第そちらにも着手したいと思います。
