GASでスプレッドシートのデータをBigQueryへ自動連携
/ 初心者向け
GASでスプレッドシートのデータをBigQueryへ自動連携
こんにちは！Google Apps Script (GAS) 専門ブロガーの〇〇です。
今回は、日々の業務で利用するGoogle スプレッドシートのデータを、より高度な分析基盤であるGoogle BigQueryへ自動で転送する方法について、初心者の方にも分かりやすく解説していきます。
なぜスプレッドシートからBigQueryへ？
スプレッドシートは手軽にデータを集計・可視化できる便利なツールですが、データ量が増えたり、複雑な分析を行おうとすると限界が見えてきます。
一方、BigQueryはGoogle Cloudが提供するフルマネージドなデータウェアハウスであり、大量のデータを高速かつ低コストで分析するのに最適です。
- データ量の増加への対応: スプレッドシートでは限界があるデータ量も、BigQueryなら問題なく扱えます。
- 高度な分析: SQLを使った複雑なクエリや、機械学習との連携が容易になります。
- リアルタイム性: データをほぼリアルタイムで集計・分析できます。
この連携を実現することで、スプレッドシートの「手軽さ」とBigQueryの「分析力」を両立させることができます。
連携の全体像
今回の連携は、以下の流れで実現します。
1. Google スプレッドシート: 転送したいデータが存在するスプレッドシート。
2. Google Apps Script (GAS): スプレッドシートからデータを読み込み、BigQueryへINSERTするスクリプトを作成。
3. Google BigQuery: データを格納・分析するデータウェアハウス。
4. トリガー: GASスクリプトを定期的に実行するための仕組み（例: 毎日午前3時）。
事前準備
連携を開始する前に、いくつか準備が必要です。
1. BigQueryデータセットとテーブルの作成
BigQueryにデータを格納するための「データセット」と「テーブル」を作成します。テーブルのスキーマ（列名とデータ型）は、スプレッドシートの列構成と一致させる必要があります。
-- 例: データセット作成
CREATE SCHEMA IF NOT EXISTS my_dataset;
-- 例: テーブル作成
CREATE TABLE IF NOT EXISTS my_dataset.my_table (
id INT64,
name STRING,
timestamp TIMESTAMP
);2. BigQuery APIの有効化
GASからBigQueryを操作するためには、BigQuery APIを有効にする必要があります。Google Cloud Consoleでプロジェクトを選択し、「APIとサービス」>「ライブラリ」から「BigQuery API」を検索して有効化してください。
GASスクリプトの作成
いよいよGASスクリプトを作成します。スプレッドシートを開き、「拡張機能」>「Apps Script」からスクリプトエディタを開いてください。
function exportSpreadsheetToBigQuery() {
const sheetName = 'Sheet1'; // 転送したいシート名
const spreadsheetId = SpreadsheetApp.getActiveSpreadsheet().getId();
const ss = SpreadsheetApp.openById(spreadsheetId);
const sheet = ss.getSheetByName(sheetName);
// ヘッダー行を除いたデータを取得
const dataRange = sheet.getDataRange();
const values = dataRange.getValues();
// ヘッダー行の処理（必要に応じて）
const headers = values.shift(); // ヘッダー行を削除し、配列として取得
// BigQueryにデータを挿入
const projectId = 'YOUR_PROJECT_ID'; // ご自身のプロジェクトIDに置き換えてください
const datasetId = 'my_dataset'; // 作成したデータセットID
const tableId = 'my_table'; // 作成したテーブルID
const rowsToInsert = values.map(row => {
return {
// スプレッドシートの列順とBigQueryのスキーマを対応させてください
// 例: スプレッドシートの1列目がBigQueryのid (INT64) に対応する場合
id: row[0],
name: row[1],
timestamp: new Date(row[2]) // 日付形式はBigQueryのTIMESTAMP型に合わせる
};
});
if (rowsToInsert.length > 0) {
// BigQuery.BigQuery.Table.insert() を使用
// BigQuery API v2 を直接呼び出す方法もありますが、GASの組み込みサービスが簡便です。
// ただし、GASの組み込みBigQueryサービスは制限があるため、より高度な操作にはCloud FunctionsやPythonスクリプトとの連携も検討してください。
// ここでは、GASの組み込みサービスが利用できない、あるいはより柔軟な制御が必要な場合の代替案として、
// UrlFetchApp を使用してBigQuery APIを直接叩く方法の概要に触れます。
// 実際には、認証情報の設定やリクエストボディの構築が複雑になります。
// 簡易的なINSERT処理（GAS組み込みサービスがない場合）
// BigQuery API v2 の insertAll メソッドを使用する例（認証情報の設定が別途必要）
/*
const url = `https://bigquery.googleapis.com/bigquery/v2/projects/${projectId}/datasets/${datasetId}/tables/${tableId}/insertAll`;
const payload = {
rows: rowsToInsert.map(row => ({ insertId: Utilities.getUuid(), json: row }))
};
const options = {
method: 'post',
contentType: 'application/json',
headers: {
'Authorization': 'Bearer ' + ScriptApp.getOAuthToken()
},
payload: JSON.stringify(payload)
};
UrlFetchApp.fetch(url, options);
Logger.log('Successfully inserted data into BigQuery.');
*/
// **注意**: GASには直接BigQueryにデータを挿入する組み込みサービスがありません。
// 多くのケースでは、Cloud FunctionsやCloud Runなどを介してGASからHTTPリクエストを送信するか、
// または、よりシンプルな連携であれば、BigQueryのgcloudコマンドをGASから実行する、
// あるいは、Google Cloud SDK を使ったPythonスクリプトなどを外部で実行し、
// その結果をGASで確認する、といった方法が取られます。
// ここでは、汎用的なGASでのAPI連携の考え方としてUrlFetchAppを紹介しましたが、
// BigQueryへの直接INSERTは、認証やAPI仕様の理解がより必要になります。
// 初心者の方には、まずGASからBigQueryに「コマンドを投げる」などの簡単な連携から始めることをお勧めします。
// 例: BigQueryのクエリ実行結果をGASで取得する
const query = `SELECT COUNT(*) FROM ${projectId}.${datasetId}.${tableId}`; // 例としてのクエリ
const job = BigQuery.Jobs.query(projectId, {
query: query,
useLegacySql: false
});
const results = job.getRows();
Logger.log('BigQuery query result: ' + results);
} else {
Logger.log('No data to insert.');
}
}スクリプトの解説:
sheetName,spreadsheetId: 操作対象のスプレッドシートとシートを指定します。values: スプレッドシートから全データを取得します。getValues()は2次元配列を返します。headers: ヘッダー行を処理します。必要に応じて、BigQueryのスキーマに合わせて列をマッピングします。projectId,datasetId,tableId: BigQueryの情報を指定します。YOUR_PROJECT_IDはご自身のGoogle CloudプロジェクトIDに置き換えてください。rowsToInsert: スプレッドシートのデータを、BigQueryのテーブルスキーマに合わせたJSON形式の配列に変換します。- BigQuery API連携の注意点: GASには直接BigQueryにデータを挿入する組み込みサービスがありません。上記コードでは、
UrlFetchAppを使ってBigQuery APIを直接叩く方法の概要を示しましたが、認証設定やAPI仕様の理解がより必要となります。より現実的なアプローチとしては、 - Cloud Functions/Cloud Runの利用: GASからHTTPリクエストを送り、Cloud FunctionsなどでBigQueryへのINSERT処理を実行する。
- GASからのクエリ実行: BigQueryにデータが既に存在する場合、GASからクエリを実行して結果を取得する。
- 外部スクリプト: PythonなどでBigQueryへのINSERTスクリプトを作成し、Cloud Schedulerなどで定期実行し、GASはその結果を利用する。
初心者の方は、まずはGASからBigQueryに「クエリを投げて結果を取得する」といった、よりシンプルな連携から試してみるのがおすすめです。
トリガーの設定
作成したGASスクリプトを自動で実行するために、トリガーを設定します。
1. スクリプトエディタの左側にある「時計」アイコン（トリガー）をクリックします。
2. 「トリガーを追加」ボタンをクリックします。
3. 以下の設定を行います。
- 実行する関数を選択:
exportSpreadsheetToBigQueryを選択します。 - 実行するデプロイを選択:
Headを選択します。 - イベントのソースを選択: 「時間主導型」を選択します。
- 時刻ベースのトリガーの種類を選択: 「日タイマー」など、希望する頻度を選択します。（例: 毎日午前3時～4時）
4. 「保存」をクリックします。
これで、設定した時間に自動でスクリプトが実行されるようになります。
まとめ
今回は、Google Apps Script (GAS) を使って、スプレッドシートのデータをBigQueryへ自動連携する方法について解説しました。 BigQuery APIとの直接的な連携は少し複雑ですが、GASの柔軟性を活かせば、様々な自動化が可能です。
この連携をマスターすることで、スプレッドシートで集めたデータをBigQueryでパワフルに分析し、ビジネスの意思決定に役立てることができるはずです。
ぜひ、あなたの業務でも活用してみてください！
ご質問やご要望がありましたら、お気軽にコメントくださいね！
GAS自動化の導入相談
請求書PDF作成、Gmail自動送信、Slack通知、スプレッドシート連携などを業務に合わせて実装できます。