Google Apps Scriptを使ってSpreadsheetのデータをBigQueryに連携する

2023.04.24

アライアンス統括部 サービスグループの しんや です。

DevelopersIOのブログ投稿データを収集・加工・可視化して社内に共有することをここまで実践してきていたのですが、その際に用いている環境にはAmazon Redshiftを活用していました。先日個人的に部署異動(2023年03月01日付けでアライアンス統括部に異動)したのもあり、またデータ連携と可視化の仕組みもよりスムーズに/便利に/広範に連携出来るようにしたいと思い、「社内投稿データ分析環境をBigQueryに載せ替える手順をそれぞれブログにまとめておこう」と思い立ちました。

当エントリではその中から「Google Apps Scriptを使ってスプレッドシートのデータをBigQueryに連携する」手順についてまとめておこうと思います。

目次

 

データ連携イメージ(構成図)

連携イメージは至ってシンプルです。予め連携されている/データが出力されているGoogleスプレッドシートの情報を、Google Apps Scriptを介してこちらも予め用意したGoogle BigQueryの任意のテーブルに投入する...という流れです。一通りの処理が終わったらその旨をSlackチャンネルに通知するという部分まで含めています。

 

作成・作業手順

ここからはこのデータ連携を実現するために行った内容や手順を紹介していきます。

まずはじめに、連携用のGoogle Apps Scriptを配置するプロジェクトを作成します。Google Apps Scriptコンソールに遷移し、「新しいプロジェクト」を選択。

プロジェクトの名前に任意の名称を記入します。

コード実行の際にはBigQueryのAPIを利用するので、予めその手順を済ませておきます。画面左の「サービス」を押下し、起動したウインドウにて「BigQuery API」を指定、[追加]を押下。

画面左メニュー[サービス]の下にBigQuery APIの情報が追加されていることを確認出来ていればOKです。

またコード実行の際には以下のような確認ダイアログが表示されることもあるかと思いますので、その際は許可設定を行っておいてください。

Google BigQueryのテーブルは予め枠として用意しておき、Google Apps Scriptでそのテーブルに対して全件DELETE→全件投入(INSERT)するという手法を今回は取る事にします。(件数もそこまで多くない、マスタデータ系のデータ投入を想定/下記キャプチャはGoogle BigQuery管理コンソールのイメージ。当エントリで紹介するコードとの一致はしていないです)

 

実行スクリプト

上記環境設定を踏まえ、TypeScriptコードを実装。サンプルコードは以下の内容となります。

スプレッドシートのデータをBigQueryに投入する際のサンプルコード.ts

/**
 * データをBigQuery環境にロード.
 */
function loadSpreadsheetDataToBigQuery() {

  console.log("loadMemberListXxxxxxxx() START.");
  loadXxxxxxList();
  console.log("loadMemberListXxxxxxxx END.");

}

/**
 * 任意のスプレッドシートにあるデータをBigQuery環境にロード.
 */
function loadXxxxxxList() {

  var localdata = ""

  // (1).スプレッドシートの情報を取得.
  const SHEET_URL = "(スプレッドシートのURL)";
  const SHEET_NAME = "(スプレッドシート内の任意のシート名)"
  var spreadsheet = SpreadsheetApp.openByUrl(SHEET_URL).getSheetByName(SHEET_NAME);
  var xxxxxRecords = spreadsheet.getDataRange().getDisplayValues(); // 日付データもそのまま(YYYY-MM-DD)のフォーマットで持ってきたかったのでgetDisplayValues()で取得.
  console.log(xxxxxRecords.length)

  // (2).ヘッダ行も含む形でデータとして読み込み.
  for(let i=0;i < xxxxxRecords.length; i++) {
    var user_id = xxxxxRecords[i][0];
    var user_name = xxxxxRecords[i][1] + " " + xxxxxRecords[i][2];
    var birthday = xxxxxRecords[i][3];
    
    // 最後の1件だけ改行無しで格納.
    if (i < xxxxxRecords.length + 1) {
      localdata += 
        user_id + "," + user_name + "," + birthday + "\r\n";
    } else {
      localdata += 
        user_id + "," + user_name + "," + birthday;
    }
  }
  console.log(localdata);


  // (3).BigQuery対象テーブルの設定.
  var projectId = "(接続先のプロジェクトID)"; /** プロジェクトID */
  var datasetId = "(接続先プロジェクトID配下の任意のデータセットID)"; /** データセットID */
  var tableId = "(データ投入・連携を行いたいテーブルのテーブルID)"; /** テーブルID */

  var table = {
    tableReference: {
      projectId: projectId,
      datasetId: datasetId,
      tableId: tableId
    },
    schema: {
      fields: [
        {name: 'user_id', type: 'string'},
        {name: 'user_name', type: 'string'},
        {name: 'birthday', type: 'string'},
      ]
    }
  }

  // (4).データ投入前に全件削除(DELETE).
  try {
    console.log("xxxxx#delete start.");
    var deleteQuery = {
      query: "#standardSQL \n DELETE FROM `(接続先プロジェクトID).(スキーマID).(テーブルID)` WHERE true;"
    };
    var queryResultsDelete = BigQuery.Jobs.query(deleteQuery,projectId);
    console.log(queryResultsDelete);
    console.log("xxxxx#delete end.");
  } catch(e) {
    console.log(e);
  }
  blob = Utilities.newBlob(localdata).setContentType("application/octet-stream")
  var job = {
    configuration: {
      load: {
        destinationTable: {
          projectId: projectId,
          datasetId: datasetId,
          tableId: tableId
        },
        skipLeadingRows: 1
      }
    }
  };

  // (5).データ投入(INSERT).
  try {
    console.log("xxxxx#insert start.");
    var queryResultsInsert = BigQuery.Jobs.insert(job, projectId, blob);
    console.log(job);
    console.log(queryResultsInsert)
    console.log("xxxxx#insert end.");
  } catch(e) {
    console.log(e);
  }

  // (6).Slack通知.
  notification_messsage = "スプレッドシートからBigQueryへの連携処理 完了";
  const token = PropertiesService.getScriptProperties().getProperty("SLACK_BOT_TOKEN");
  const apiResponse = callSlackNotification(token, "chat.postMessage", {
    channel: "#(Slackチャンネル名)",
    text: notification_messsage,
  });

}

// (6).Slack通知用実行functionの作成.
function callSlackNotification(token, apiMethod, payload) {
  const response = UrlFetchApp.fetch(
    `https://www.slack.com/api/${apiMethod}`,
    {
      method: "post",
      contentType: "application/x-www-form-urlencoded",
      headers: { "Authorization": `Bearer ${token}` },
      payload: payload,
    }
  );
  console.log(`Web API (${apiMethod}) response: ${response}`)
  return response;
}

各種コードをどのような意図や目的で記載したのかについては下記をご参照ください。(ひとまず「動けば良いや」位の気持ちで書いたのでコードの品質については御容赦ください...m(_ _)m )

 

コードの実行

動作確認のための実行及びデバッグはApps Scriptコンソール上で行いました。

実行ログの確認も同様にコンソール上で行っています。

 

スケジュール設定

Google Apps Scriptのスケジュール設定を活用しました。「トリガー指定で処理を実行」「溜まった実行済みトリガーを削除する処理を実行」を行う形としています。

ScheduleManager.ts

function setTrigger() {
  var scheduledTime = new Date();
  scheduledTime.setDate(scheduledTime.getDate() + 1);
  scheduledTime.setHours(1);
  scheduledTime.setMinutes(15);
  console.log(scheduledTime);
  ScriptApp.newTrigger('loadSpreadsheetDataToBigQuery').timeBased().at(scheduledTime).create();
}


function deleteTriggers() {
  const triggers = ScriptApp.getProjectTriggers();
  for(const trigger of triggers) {
    if(trigger.getHandlerFunction() == 'loadSpreadsheetDataToBigQuery') {
      ScriptApp.deleteTrigger(trigger);
    }
  }
}

 

実行内容のSlack通知

ここで実行した処理はせっかくなので所定のSlackチャンネルに通知したいなと思い、関連設定と作業を行いました。主に以下の流れです。

処理実行がなされると、以下の様にSlackチャンネルにメッセージが投稿されるようになります。

 

まとめ

という訳で、Google Spreadsheetの情報をGoogle Apps Scriptを使ってGoogle BigQuery環境に連携する手順についての紹介でした。

今回のエントリを含めた手順については「既存実施していた処理の置き換え」な側面が強いですが、一通り関連する作業や処理の置き換えが完了したら「これらの処理をもっと便利にもっと効率良いものに置き換える」「これらの処理で生成された情報を別途活用する」というのをモダンデータスタック(MDS)でやってみたい...という中長期の目標というか野望もあったりします。状況が整い次第そちらにも着手したいと思います。