技術ブログ

GASでスプレッドシートデータをBigQueryへ自動転送！

2026-05-03 / 初心者向け

Google Apps ScriptでスプレッドシートデータをBigQueryへ自動転送

ビジネスの現場でスプレッドシート（Google Sheets）のデータ分析を行う際、より高度な分析や大量データの扱いのためにBigQueryへのデータ連携が必要になる場面は少なくありません。しかし、手作業でデータを転送するのは手間がかかり、ミスも発生しやすいため、自動化が理想的です。

そこで今回は、Google Apps Script（GAS）を活用して、スプレッドシートのデータをBigQueryへ自動転送する方法を、初心者の方にも分かりやすく解説していきます。

なぜGASでBigQuery連携をするのか？

GASはGoogle Workspaceのサービスを連携・自動化するための強力なツールです。スプレッドシートとBigQueryをGASで連携させることには、以下のようなメリットがあります。

• 自動化による効率化: 定期的なデータ転送や、特定イベント発生時のデータ転送を自動化できます。
• ヒューマンエラーの削減: 手作業による転記ミスやデータ投入漏れを防ぎます。
• リアルタイム性の向上: データの鮮度を保ちやすくなります。
• Google Workspace内での完結: 追加のツールやサービスを導入することなく、既存の環境で実現できます。

準備するもの

• Googleアカウント: GASの実行、スプレッドシート、BigQueryへのアクセスに必要です。
• Google スプレッドシート: 転送したいデータが格納されているシート。
• Google Cloud Platform (GCP) プロジェクト: BigQueryを使用するために必要です。まだ作成していない場合は、GCPのコンソールからプロジェクトを作成してください。
• BigQuery データセットとテーブル: データを格納するためのデータセットとテーブルをBigQuery上に作成しておきます。スキーマ（列名、データ型）をスプレッドシートの列に合わせて定義しておきましょう。

GASスクリプトの作成手順

1. BigQuery APIの有効化

GASからBigQueryを操作するには、BigQuery APIを有効にする必要があります。

1. Google Cloud Platform (GCP) コンソールにアクセスします。

2. 対象のプロジェクトを選択します。

3. ナビゲーションメニューから「APIとサービス」>「ライブラリ」を選択します。

4. 検索バーに「BigQuery API」と入力し、検索結果から「BigQuery API」を選択します。

5. 「有効にする」ボタンをクリックします。

2. GASプロジェクトの作成

1. 転送元となるGoogle スプレッドシートを開きます。

2. メニューバーの「拡張機能」>「Apps Script」を選択します。

3. GASエディタが開くので、プロジェクト名を分かりやすいものに変更します（例: SheetsToBigQueryTransfer）。

3. GASスクリプトの記述

GASエディタに以下のコードを記述します。コード内の YOUR_PROJECT_ID, YOUR_DATASET_ID, YOUR_TABLE_ID, YOUR_SHEET_NAME はご自身の環境に合わせて適宜変更してください。

/**
 * スプレッドシートのデータをBigQueryに転送する関数
 */
function transferSheetToBigQuery() {
  // --- 設定 ---
  const projectId = 'YOUR_PROJECT_ID'; // GCPプロジェクトID
  const datasetId = 'YOUR_DATASET_ID'; // BigQueryデータセットID
  const tableId = 'YOUR_TABLE_ID';   // BigQueryテーブルID
  const sheetName = 'Sheet1';        // 転送したいシート名
  // --------------

  const spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  const sheet = spreadsheet.getSheetByName(sheetName);

  // ヘッダー行を除いたデータを取得
  const range = sheet.getDataRange();
  const values = range.getValues();

  if (values.length <= 1) {
    Logger.log('スプレッドシートにデータがありません。');
    return;
  }

  // ヘッダー行を取得
  const headers = values[0];
  // データ部分（ヘッダー行を除く）
  const data = values.slice(1);

  // BigQueryに挿入するためのレコード配列を作成
  const records = [];
  for (let i = 0; i < data.length; i++) {
    const row = data[i];
    const record = {};
    for (let j = 0; j < headers.length; j++) {
      // スプレッドシートの列名（ヘッダー）をBigQueryの列名として使用
      // データ型はGAS側で自動推論されるが、必要に応じて変換処理を追加
      record[headers[j]] = row[j];
    }
    records.push(record);
  }

  // BigQueryへデータを挿入
  try {
    const job = BigQuery.Jobs.insert({
      configuration: {
        load: {
          destinationTable: {
            projectId: projectId,
            datasetId: datasetId,
            tableId: tableId
          },
          sourceData: {
            data: records,
            formattype: 'NEWLINE_DELIMITED_JSON'
          },
          writeDisposition: 'WRITE_APPEND' // 既存データに追記
        }
      }
    });
    Logger.log('BigQueryへのデータロードジョブが開始されました。Job ID: ' + job.jobReference.jobId);
  } catch (e) {
    Logger.log('BigQueryへのデータロード中にエラーが発生しました: ' + e.toString());
  }
}

コードの解説:

• projectId, datasetId, tableId, sheetName: 自身の環境に合わせて変更してください。
• getSheetByName(sheetName): 指定したシートを取得します。
• getDataRange().getValues(): シートの全データを二次元配列として取得します。ヘッダー行も含まれます。
• values.slice(1): ヘッダー行を除いたデータ部分を取得します。
• records 配列の作成: スプレッドシートの各行を、BigQueryのテーブルスキーマに合わせたJSONオブジェクト（レコード）に変換しています。スプレッドシートのヘッダー行がBigQueryの列名と一致している必要があります。
• BigQuery.Jobs.insert(): BigQueryへデータをロードするジョブを実行します。
• destinationTable: データをロードする先のBigQueryテーブルを指定します。
• sourceData: 転送するデータを指定します。ここでは newline_delimited_json 形式で渡しています。
• writeDisposition: 'WRITE_APPEND': BigQueryテーブルに既存のデータがあれば、その末尾に追記する設定です。WRITE_TRUNCATE にすると、テーブルがクリアされ、新しいデータのみが挿入されます。

4. BigQuery サービスへの権限付与

スクリプトを実行する際に、BigQuery APIにアクセスするための権限を付与する必要があります。初めにスクリプトを実行しようとすると、権限の確認画面が表示されるので、内容を確認して「許可」してください。

5. スクリプトの実行とテスト

GASエディタのツールバーにある実行ボタン（▶︎）をクリックして、transferSheetToBigQuery 関数を実行します。

実行後、BigQueryコンソールでデータが正しく転送されているか確認してください。

自動実行の設定（トリガー）

このスクリプトを定期的に実行させたい場合は、トリガーを設定します。

1. GASエディタの左側にある時計アイコン（トリガー）をクリックします。

2. 右下にある「トリガーを追加」ボタンをクリックします。

3. 以下の項目を設定します。

• 実行する関数を選択: transferSheetToBigQuery を選択します。
• 実行するデプロイを選択: Head を選択します。
• イベントのソースを選択: 「時間主導型」を選択します。
• 時間の間隔を選択: 「毎時」「毎日」「毎週」など、希望する実行頻度を選択します。
• エラー通知設定: 必要に応じて設定します。

4. 「保存」をクリックします。

これで、指定した間隔で自動的にスプレッドシートのデータがBigQueryに転送されるようになります。

まとめ

Google Apps Script を活用することで、スプレッドシートのデータをBigQueryへ自動で転送する方法をご紹介しました。この自動化により、データ分析の効率が格段に向上し、より迅速かつ正確な意思決定が可能になります。ぜひ、ご自身の業務で試してみてください！