技術ブログ

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

2026-04-19 / 初心者向け

Google Apps ScriptでスプレッドシートのデータをBigQueryに自動転送しよう！

「Googleスプレッドシートに溜まったデータを、より高度な分析のためにBigQueryに移行したいけど、手作業は大変…」

そんな悩みを抱えていませんか？

実は、Google Apps Script (GAS) を使えば、この作業を自動化することが可能です！GASは、Google Workspaceの各サービスを連携させ、様々な自動化を実現できる強力なツールです。

本記事では、GASの知識が初めての方でも理解できるように、スプレッドシートのデータをBigQueryに自動転送する手順を、サンプルコードと共に詳しく解説していきます。

なぜGASでBigQueryに転送するのか？

• 自動化による効率化: 手作業でのデータ移行は時間と手間がかかります。GASで自動化することで、これらの作業から解放され、本来注力すべき業務に集中できます。
• リアルタイムに近いデータ連携: 定期的にスクリプトを実行することで、BigQueryのデータを常に最新の状態に保つことができます。
• 高度な分析基盤の構築: BigQueryは、大量のデータを高速に処理・分析するための強力なデータウェアハウスです。スプレッドシートのデータをBigQueryに集約することで、より高度なBIツールとの連携や機械学習への活用が可能になります。

事前準備

始める前に、以下の準備が必要です。

1. Googleアカウント: GoogleスプレッドシートとBigQueryを利用するためのアカウント。

2. Googleスプレッドシート: 転送したいデータが格納されているスプレッドシート。

3. BigQueryプロジェクト: データを格納するBigQueryのプロジェクト。

• まだBigQueryを使ったことがない方は、Google Cloud Platform (GCP) のコンソールからプロジェクトを作成し、BigQuery APIを有効にする必要があります。無料枠もありますので、まずは試してみるのがおすすめです。

4. BigQueryデータセットとテーブル: データを格納するためのデータセットとテーブルを作成しておきましょう。テーブルのスキーマ（列名とデータ型）は、スプレッドシートの列構成と一致させる必要があります。

GASスクリプトの作成手順

では、実際にGASスクリプトを作成していきましょう。

1. GASエディタを開く

対象のスプレッドシートを開き、「拡張機能」>「Apps Script」を選択します。

2. BigQueryサービスを有効にする

GASエディタの左側にある「+」ボタン（サービスを追加）をクリックし、「BigQuery」を選択して追加します。

3. サンプルコード

以下のコードをGASエディタに貼り付けてください。コメントを参考に、ご自身の環境に合わせてspreadsheetId、sheetName、datasetId、tableIdを編集してください。

function transferSpreadsheetToBigQuery() {
  // 1. 設定項目
  const spreadsheetId = 'ここにスプレッドシートIDを入力'; // 例: '123abcXYZ...'
  const sheetName = 'ここにシート名を入力';       // 例: 'Sheet1'
  const datasetId = 'ここにデータセットIDを入力';       // 例: 'my_dataset'
  const tableId = 'ここにテーブルIDを入力';         // 例: 'my_table'
  const writeDisposition = 'WRITE_APPEND'; // 'WRITE_TRUNCATE' (上書き) も可能

  // 2. スプレッドシートからデータを取得
  const ss = SpreadsheetApp.openById(spreadsheetId);
  const sheet = ss.getSheetByName(sheetName);
  const dataRange = sheet.getDataRange();
  const values = dataRange.getValues();

  // ヘッダー行を除外する場合 (最初の行がヘッダーの場合)
  if (values.length > 1) {
    const header = values[0];
    const rows = values.slice(1);

    // 3. BigQueryへデータを挿入
    const bq = BigQuery.newDataset().dataset(datasetId).table(tableId);

    const rowsToInsert = rows.map(row => {
      const obj = {};
      header.forEach((colName, index) => {
        // BigQueryのスキーマに合わせてデータ型を調整する必要がある場合があります
        // 例: 日付文字列をDateオブジェクトに変換など
        obj[colName] = row[index];
      });
      return obj;
    });

    const request = {
      rows: rowsToInsert.map(row => ({ values: Object.values(row) })),
      configuration: {
        load: {
          destinationTable: {
            projectId: ScriptApp.getProjectKey(), // 現在のプロジェクトID
            datasetId: datasetId,
            tableId: tableId
          },
          writeDisposition: writeDisposition,
          schema: {
            fields: header.map(colName => ({ name: colName, type: 'STRING' })) // ここでデータ型を指定 (STRINGは仮)
          }
        }
      }
    };

    // BigQueryへデータをロード
    BigQuery.Jobs.insert(request, ScriptApp.getProjectKey());
    Logger.log('データ転送が完了しました。');

  } else {
    Logger.log('転送するデータがありません。');
  }
}

コードの解説:

• 設定項目: 転送元スプレッドシートの情報と、転送先BigQueryのデータセット・テーブルIDを指定します。writeDispositionは、既存のテーブルにデータを追記する（WRITE_APPEND）か、上書きする（WRITE_TRUNCATE）かを指定します。
• スプレッドシートからデータを取得: SpreadsheetApp.openById()でスプレッドシートを開き、getSheetByName()でシートを指定します。getDataRange().getValues()でシート全体のデータを二次元配列として取得します。
• ヘッダー行の処理: 一般的に、スプレッドシートの1行目はヘッダーとして扱われます。このコードでは、slice(1)でヘッダー行を除外して、実際のデータ行のみをBigQueryに転送しています。
• BigQueryへデータを挿入: rows.map()を使って、スプレッドシートのデータをBigQueryが受け入れられる形式（キーが列名、値がセルの値のオブジェクト）に変換します。ここで、BigQueryのテーブルスキーマに合わせて、データ型を正しく指定することが非常に重要です。 サンプルコードでは、全てSTRING型としていますが、必要に応じてINTEGER、FLOAT、BOOLEAN、TIMESTAMPなどに変更してください。
• BigQueryへデータをロード: BigQuery.Jobs.insert()メソッドを使って、BigQueryへデータをロードするジョブを作成・実行します。schema.fieldsで、BigQueryテーブルのスキーマを定義します。

4. スクリプトの実行と権限付与

GASエディタの上部にある実行ボタン（▶️）をクリックして、transferSpreadsheetToBigQuery関数を実行します。

初回実行時には、Googleアカウントへのアクセス許可を求められます。「権限を確認」をクリックし、必要な権限を許可してください。

5. トリガーの設定

このスクリプトを自動実行させるためには、トリガーを設定します。GASエディタの左側にある時計のアイコン（トリガー）をクリックし、「トリガーを追加」ボタンをクリックします。

• 実行する関数を選択: transferSpreadsheetToBigQuery を選択します。
• 実行するデプロイを選択: Head を選択します。
• イベントのソースを選択: 「時間主導型」を選択します。
• 時刻ベースのトリガーの種類を選択: 「時間間隔タイマー」などを選択し、実行したい頻度（例: 毎日、毎週、毎時など）を設定します。

設定したら、「保存」をクリックします。

注意点と応用

• データ型: BigQueryのテーブルスキーマとスプレッドシートのデータ型を一致させることが重要です。数値はINTEGERやFLOAT、日付はTIMESTAMPなど、適切な型を指定してください。
• エラーハンドリング: 実際の運用では、ネットワークエラーやデータ形式のエラーなど、様々な問題が発生する可能性があります。try...catchブロックを使ってエラーハンドリングを実装することをおすすめします。
• 大量データ: 非常に大量のデータを転送する場合、GASの実行時間制限（最大6分）に引っかかる可能性があります。その場合は、データを分割して転送する、BigQueryのStreaming APIを利用するなど、別の手法を検討する必要があります。
• テーブルスキーマの自動作成: 初回転送時にテーブルが存在しない場合、テーブルスキーマを自動で作成する処理を追加することも可能です。

まとめ

Google Apps Scriptを使えば、GoogleスプレッドシートのデータをBigQueryに自動で転送することが可能です。本記事で紹介した手順とサンプルコードを参考に、ぜひご自身の業務に活用してみてください。データ分析の幅が大きく広がるはずです！

GASとBigQueryを組み合わせることで、あなたのデータ活用はさらに進化します！