技術ブログ

GASでスプレッドシートをBigQueryに自動連携

/ 初心者向け

GASでスプレッドシートをBigQueryに自動連携

スプレッドシートのデータをBigQueryに自動転送！GASで実現する方法

皆さん、こんにちは！Google Apps Script (GAS) の専門ブロガーです。

「Google スプレッドシートに蓄積されたデータを、もっと高度な分析のためにBigQueryへ自動で移行したい…」

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

手作業でのデータ移行は、時間と手間がかかるだけでなく、ヒューマンエラーのリスクも伴います。しかし、Google Apps Script (GAS) を活用すれば、このプロセスを完全に自動化できるんです！

本記事では、GASを使ってスプレッドシートのデータをBigQueryに自動転送する方法を、初心者の方にも分かりやすく、具体的なコード例を交えながら解説していきます。

なぜスプレッドシートとBigQueryを連携させるのか？

スプレッドシートは、手軽にデータを入力・編集・共有できる非常に便利なツールです。しかし、データ量が膨大になったり、複雑な分析を行いたい場合には、いくつかの限界があります。

  • パフォーマンス: 大量のデータを扱うと、スプレッドシートの動作が遅くなることがあります。
  • 分析機能: 複雑な集計や高度な分析には、スプレッドシートの機能だけでは限界があります。
  • スケーラビリティ: データ増加への対応が困難になる場合があります。

一方、BigQueryはGoogle Cloud Platform (GCP) が提供する、フルマネージドでサーバーレスのデータウェアハウスです。その特徴は以下の通りです。

  • 圧倒的な処理速度: ペタバイト級のデータでも高速にクエリを実行できます。
  • 強力な分析機能: SQLベースで高度な分析が可能です。
  • スケーラビリティ: データ量が増えても自動的にスケールします。
  • コスト効率: 使った分だけ支払う従量課金制です。

この二つを連携させることで、スプレッドシートの「手軽さ」とBigQueryの「分析力・スケーラビリティ」という、それぞれの強みを最大限に活かすことができるのです。

###GASを使った自動転送の全体像

GASでスプレッドシートのデータをBigQueryに転送する基本的な流れは以下のようになります。

1. スプレッドシートのデータを取得: GASを使って、対象のスプレッドシートから必要なデータを読み込みます。

2. BigQueryへのデータ準備: 取得したデータを、BigQueryに挿入できる形式（通常はJSON形式）に変換します。

3. BigQueryへデータを挿入: BigQueryのAPIをGASから呼び出し、データを挿入します。

4. 定期実行の設定: GASのトリガー機能を使って、この処理を定期的に（例えば毎日、毎時など）実行するように設定します。

実践！GASスクリプトの作成

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

準備するもの

  • Google スプレッドシート（転送したいデータが入っているもの）
  • Google Cloud Platform (GCP) プロジェクト
  • BigQuery データセットとテーブル（事前に作成しておいてください）
  • BigQuery API の有効化

GCPプロジェクトとBigQueryの設定については、ここでは詳細を省略しますが、もし不明な点があれば、Google Cloudの公式ドキュメントを参照してください。

スクリプトのコード

以下のスクリプトは、特定のスプレッドシートのシートからデータを取得し、BigQueryのテーブルに挿入する例です。エラーハンドリングや詳細なデータ変換は必要に応じて追加してください。

function sendSheetDataToBigQuery() {
  // --- 設定項目 ---
  const SPREADSHEET_ID = 'YOUR_SPREADSHEET_ID'; // 対象のスプレッドシートID
  const SHEET_NAME = 'Sheet1'; // 対象のシート名
  const PROJECT_ID = 'YOUR_GCP_PROJECT_ID'; // GCPプロジェクトID
  const DATASET_ID = 'YOUR_DATASET_ID'; // BigQueryデータセットID
  const TABLE_ID = 'YOUR_TABLE_ID'; // BigQueryテーブルID
  // ----------------

  const ss = SpreadsheetApp.openById(SPREADSHEET_ID);
  const sheet = ss.getSheetByName(SHEET_NAME);
  const data = sheet.getDataRange().getValues(); // シートの全データを取得

  // ヘッダー行を除外（1行目）
  data.shift();

  if (data.length === 0) {
    Logger.log('転送するデータがありません。');
    return;
  }

  // BigQueryに挿入するためのJSON形式のデータを作成
  const rows = data.map(row => {
    // ここで、スプレッドシートの各列をBigQueryのテーブルスキーマに合わせて整形します。
    // 例: row[0] を string 型、row[1] を number 型など。
    // 簡単な例として、そのままオブジェクトにします。
    // 実際の運用では、データ型変換やnull処理などを厳密に行う必要があります。
    return {
      column1: row[0], // スプレッドシートの1列目
      column2: row[1]  // スプレッドシートの2列目
      // ... 必要に応じて追加
    };
  });

  try {
    const resource = {
      rows: rows.map(row => ({ json: row })) // rowsの各要素を { json: row } の形式に変換
    };

    const response = BigQuery.Jobs.insert({
      configuration: {
        load: {
          destinationTable: {
            projectId: PROJECT_ID,
            datasetId: DATASET_ID,
            tableId: TABLE_ID
          },
          sourceFormat: 'NEWLINE_DELIMITED_JSON',
          writeDisposition: 'WRITE_APPEND' // 既存のデータに追記
          // writeDisposition: 'WRITE_TRUNCATE' // テーブルをクリアして書き込む
        }
      }
    }, PROJECT_ID, resource);

    Logger.log('BigQueryへのデータ転送が完了しました。ジョブID: ' + response.jobReference.jobId);

  } catch (e) {
    Logger.log('BigQueryへのデータ転送中にエラーが発生しました: ' + e);
  }
}

コードの解説

  • SPREADSHEET_ID, SHEET_NAME, PROJECT_ID, DATASET_ID, TABLE_ID: ご自身の環境に合わせて必ず変更してください。
  • sheet.getDataRange().getValues(): スプレッドシートの全データを二次元配列で取得します。
  • data.shift(): ヘッダー行を削除します。もしヘッダー行もデータとして扱いたい場合は、この行は削除してください。
  • rows.map(...): 取得したデータをBigQueryのNEWLINE_DELIMITED_JSON形式に変換します。json: row の部分で、スプレッドシートの列をBigQueryのテーブルスキーマに合わせてマッピングしてください。ここにデータ型変換のロジックを実装するのが重要です。
  • BigQuery.Jobs.insert(): BigQueryのAPIを呼び出してデータを挿入します。writeDisposition でデータの書き込み方法（追記か上書きか）を選択できます。

BigQuery API の有効化

GASからBigQuery APIを利用するためには、GCPプロジェクトで「BigQuery API」を有効にする必要があります。

1. GCP Console にアクセスします。

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

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

4. 「BigQuery API」を検索し、有効化します。

定期実行の設定（トリガー）

GASスクリプトは、作成しただけでは実行されません。定期的に自動実行させるためには「トリガー」を設定します。

1. GASエディタを開き、「時計のアイコン」（トリガー）をクリックします。

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

3. 以下の設定を行います。

  • 実行する関数を選択: sendSheetDataToBigQuery を選択します。
  • 実行するデプロイを選択: 「ヘッド」を選択します。
  • イベントのソースを選択: 「時間主導型」を選択します。
  • 時刻の範囲を選択: 「日タイマー」、「週タイマー」、「月タイマー」など、実行したい頻度に合わせて選択します。
  • 時刻を選択: 実行したい時間帯を選択します。

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

これで、指定した時間に自動でスクリプトが実行されるようになります。

まとめ

いかがでしたでしょうか？

GASを使えば、スプレッドシートのデータをBigQueryへ自動で、かつ効率的に転送することができます。これにより、データ分析の幅が広がり、より迅速な意思決定が可能になります。

今回の記事が、皆さんのGAS活用の一助となれば幸いです。ぜひ、ご自身の環境で試してみてください！

次回も、GASの便利な活用法をお届けしますので、お楽しみに！

GAS自動化の導入相談

請求書PDF作成、Gmail自動送信、Slack通知、スプレッドシート連携などを業務に合わせて実装できます。

請求書自動生成ツールを見る / SNS自動投稿ツールを見る