Google Apps ScriptでSpreadsheetの内容をBigQueryのテーブルに流し込む

Google Apps ScriptのBigQuery Serviceを使うと、Apps ScriptからAPI経由でBigQueryを操作できる。

BigQueryからSpreadsheetのデータを参照する方法はいくつかある。手っ取り早いのはGoogle Drive external tableの機能を使うことだが、この方法はWorkspace側のセキュリティ設定によってサービスアカウントからのアクセスが制限されてる場合に何かと不便だ。代替案の一つとして、BigQuery Serviceを使ってSpreadsheet上のデータをBigQueryのテーブルに書き出す方法がある。

実験用にBigQueryのテーブルを用意した。カラムはname(文字列)とage(整数)の二つ。このテーブルにBigQuery ServiceのJobs.insertを使ってデータを入れてみよう。

Google Apps ScriptのBigQuery Serviceのメソッドの引数の調べ方

上のポストに書いた通り、BigQuery ServiceはBigQuery公開APIのラッパーになっているため、BigQuery Serviceとしてのドキュメントが存在しない。Jobs.insertの使い方も公開APIのリファレンスを読んで、自分でApps Script上での使い方を翻訳する必要がある。

Jobs.insertは「最初のパラメータにJobオブジェクト、次のパラメータにProject ID、三つ目のパラメータにアップロードするデータのBlob」を指定するような呼び出し方になる。Jobに関してはJobオブジェクトのJSON表現を参照し、データの読み込み(load)のジョブの設定を書く。Google Apps ScriptのUtilities.newBlobメソッドを使うと任意のデータからBlobを作ることができるので、例えばこんな感じでCSVの文字列からBlobを作ってテーブルにデータをInsertすることができる。

const csvString = "John,39\nPeter,25\n";
const blobData = Utilities.newBlob(csvString)

// Create the data upload job.
const job = {
  configuration: {
    load: {
      destinationTable: {
        projectId: projectId,
        datasetId: datasetId,
        tableId: tableId
      }
    }
  }
};

const jobResult = BigQuery.Jobs.insert(job, projectId, blobData);

データが入ったことを確認。

CSV形式の代わりに改行区切りJSON(Newline delimited JSON)でデータを指定することもできる。改行区切りJSONでデータを読み込ませる場合は、Jobのconfiguration.loadの中でsourceFormatにNEWLINE_DELIMITED_JSONを指定する。

  const entities = [
    {
      "name": "James",
      "age": 38
    },
    {
      "name": "Nick",
      "age": 24
    }
  ]
  const nlDelimitedJsonString = entities.map(entity => JSON.stringify(entity)).join("\n");
  const data = Utilities.newBlob(nlDelimitedJsonString)

  // Create the data upload job.
  const job = {
    configuration: {
      load: {
        destinationTable: {
          projectId: projectId,
          datasetId: datasetId,
          tableId: tableId
        },
        sourceFormat: "NEWLINE_DELIMITED_JSON"
      }
    }
  };

...以下略...

スプレッドシートの内容をもとにデータを読み込ませたい場合は、entitiesの部分をスプレッドシートからとってきた内容に変更すれば良い。

Google Apps Scriptでスプレッドシートの内容をオブジェクトの配列として取得する

シート全体がテーブルのフォーマットに合わせてある場合は、先日投稿したこちらのライブラリを使って、Blob生成部分を下記のように変更してやれば良い。

const entities = SheetValues.getRowsAsObjects(SpreadsheetApp.getActiveSpreadsheet().getSheetByName("TestSheet"))
const nlDelimitedJsonString = entities.map(entity => JSON.stringify(entity)).join("\n");
const data = Utilities.newBlob(nlDelimitedJsonString)

Pocket