GASでスプレッドシートをBigQueryへ自動転送！
/ 初心者向け
Google Apps Script (GAS) でスプレッドシートのデータをBigQueryに自動転送する方法
「スプレッドシートに日々蓄積されるデータを、より高度な分析のためにBigQueryへ移したい…でも、手作業は大変だし、GASでできるのか不安…」
そんなあなたのために、この記事ではGoogle Apps Script (GAS) を使って、Google スプレッドシートのデータをBigQueryに自動で転送する方法を、初心者の方でも理解できるように丁寧に解説します。
なぜスプレッドシートからBigQueryへ？
Google スプレッドシートは、手軽にデータを管理・共有できる強力なツールです。しかし、データ量が膨大になると、検索や集計に時間がかかったり、分析機能に限界を感じたりすることがあります。
一方、BigQueryはGoogle Cloud Platform (GCP) が提供する、フルマネージドなデータウェアハウスです。大量のデータを高速に処理・分析する能力に長けており、より複雑で高度なデータ分析を可能にします。
- スプレッドシート: 日々の記録、小規模なデータ管理
- BigQuery: 大規模データ分析、機械学習、BIツール連携
この2つを連携させることで、スプレッドシートの気軽さとBigQueryの分析力を両立させることができるのです。
GASで自動化するメリット
GASを使うことで、以下のメリットが得られます。
- 自動化: 手作業による転送の手間が省け、ヒューマンエラーも削減できます。
- リアルタイム性: 定期的な実行設定により、常に最新のデータをBigQueryに反映させられます。
- コスト削減: 外部ツールやサービスに依存せず、無料で実現できます。
準備するもの
1. Google アカウント: Gmailなどで使用しているアカウントでOKです。
2. Google スプレッドシート: 転送したいデータが含まれているもの。
3. Google Cloud Platform (GCP) プロジェクト: BigQueryを使用するために必要です。まだ作成していない場合は、Google Cloud Console から作成してください。
4. BigQuery データセットとテーブル: データを格納するための場所をBigQuery上に作成します。テーブルのスキーマ（列名とデータ型）をスプレッドシートの列構成に合わせて定義しておきましょう。
GASスクリプトの作成手順
1. GASエディタを開く
転送したいスプレッドシートを開き、「拡張機能」→「Apps Script」を選択します。
2. BigQuery APIの有効化
GASエディタの画面左側にある「＋」アイコン（サービス）をクリックし、「BigQuery API」を選択して追加します。これにより、GASからBigQueryを操作できるようになります。
3. スクリプトの記述
以下のサンプルコードを参考に、GASエディタに貼り付けてください。
function exportSheetToBigQuery() {
// --- 設定項目 ---
var spreadsheetId = 'YOUR_SPREADSHEET_ID'; // 転送したいスプレッドシートのID
var sheetName = 'Sheet1'; // 転送したいシート名
var dataSetId = 'YOUR_DATASET_ID'; // BigQueryのデータセットID
var tableId = 'YOUR_TABLE_ID'; // BigQueryのテーブルID
// ----------------
var ss = SpreadsheetApp.openById(spreadsheetId);
var sheet = ss.getSheetByName(sheetName);
var data = sheet.getDataRange().getValues(); // シートの全データを取得
// ヘッダー行を除外する場合 (1行目がヘッダーと仮定)
data.shift();
// BigQueryにデータを挿入
try {
var result = Bigquery.Jobs.insert({
configuration: {
load: {
sourceUris: [], // CSV形式で一時的に保存して読み込ませる
destinationTable: {
projectId: Bigquery.Projects.getDefault().id,
datasetId: dataSetId,
tableId: tableId
},
sourceFormat: 'CSV',
writeDisposition: 'WRITE_APPEND', // 追記モード
autodetect: false // スキーマを定義している場合はfalse
}
}
});
// シートのデータをCSV形式で一時ファイルに保存
var csvData = data.map(row => row.map(cell => `"${cell.toString().replace(/"/g, '""')}"`).join(',')).join('\n');
var blob = Utilities.newBlob(csvData, MimeType.CSV, 'data.csv');
var file = DriveApp.createFile(blob);
// BigQueryのロードジョブを実行
Bigquery.Jobs.insert({
configuration: {
load: {
sourceUris: [file.getAs(MimeType.CSV).getBlob().copyBlob().getCloudStorageUri()], // 一時ファイルのURI
destinationTable: {
projectId: Bigquery.Projects.getDefault().id,
datasetId: dataSetId,
tableId: tableId
},
sourceFormat: 'CSV',
writeDisposition: 'WRITE_APPEND', // 追記モード
autodetect: false // スキーマを定義している場合はfalse
}
}
});
// 一時ファイルを削除
file.setTrashed(true);
Logger.log('データがBigQueryに正常に転送されました。');
} catch (e) {
Logger.log('データ転送中にエラーが発生しました: ' + e);
}
}コードの説明:
YOUR_SPREADSHEET_ID,YOUR_DATASET_ID,YOUR_TABLE_IDは、ご自身の環境に合わせて書き換えてください。SpreadsheetApp.openById()でスプレッドシートを開きます。getSheetByName()でシートを指定します。getDataRange().getValues()でシートの全データを配列として取得します。data.shift()は、1行目をヘッダーとして扱い、データとして転送しない場合にコメントアウトを外してください。- BigQueryのロードジョブを設定し、CSV形式で一時ファイルを作成して転送します。
writeDisposition: 'WRITE_APPEND'は、既存のデータに追記する場合の指定です。上書きしたい場合は'WRITE_TRUNCATE'に変更します。
4. スクリプトの実行と権限の承認
GASエディタのツールバーにある実行ボタン（▶︎）をクリックします。
初めて実行する際は、スクリプトがGoogleアカウントやBigQuery、Google Driveにアクセスするための権限を求められます。「権限を承認」をクリックし、画面の指示に従って承認してください。
5. トリガーの設定（自動実行）
GASエディタの左側にある時計アイコン（トリガー）をクリックします。
- 「トリガーを追加」ボタンをクリックします。
- 「実行する関数を選択」で
exportSheetToBigQueryを選択します。 - 「実行するデプロイを選択」で「ヘッド」を選択します。
- 「イベントのソースを選択」で、「時間主導型」を選択します。
- 「時間間隔を選択」で、実行したい頻度（例: 1時間ごと、毎日午前0時など）を設定します。
これで、指定した間隔で自動的にスプレッドシートのデータがBigQueryに転送されるようになります。
まとめ
Google Apps Script (GAS) を活用することで、スプレッドシートのデータをBigQueryに自動で転送し、より高度なデータ分析への道を切り拓くことができます。最初は少し難しく感じるかもしれませんが、この手順を一つずつ試していけば、きっと実現できるはずです。
ぜひ、GASを使ったデータ活用の第一歩を踏み出してみてください！
---
GAS自動化の導入相談
請求書PDF作成、Gmail自動送信、Slack通知、スプレッドシート連携などを業務に合わせて実装できます。