GASでSpreadSheetのデータをBigQueryに入れる

この記事について

CyberAgent PTA Advent Calendar 2021
13日目の投稿です。

やること

GAS(Google Apps Script)からBigQueryのテーブルを作成して、データを挿入する。
Connected Sheetsはめちゃ便利だが、有料プランなので今回は使わない。

BigQueryのテーブルを作成する

テーブル定義を決める

var projectId = 'XXX';
var datasetId = 'XXX';
var tableId = 'XXX';
var table = {
  tableReference: {
    projectId: projectId,
    datasetId: datasetId,
    tableId: tableId
  },
  schema: {
    fields: [
        {name: 'Time', type: 'string'},
        {name: 'Name', type: 'string'},
        {name: 'Email', type: 'string'}
    ]
  },
  timePartitioning: {
    'type': 'DAY',
    'expirationMs': 31557600000 // 1year
  }
};

Time, Name, Emailのフィールドで、データ追加した日付でテーブルパーティショニング。1年経ったら消す。

分割テーブルにすることで、データ量が多くなったときに全データをスキャンする必要がなくなるので、コスト削減になる。

テーブルを作成

BigQuery.Tables.insert(table, projectId, datasetId);

GASにはBigQueryのライブラリが用意されているので、Tables.insertで作成。

SpreadSheetのデータをBigQueryに入れる

下記のようなデータを想定

Time	Email	Name
2021-10-06T23:57:36+0900	[e-mail]	test

function insertToTable() {
  var range = sheet.getDataRange();
  var blob = Utilities.newBlob(convCsv(range)).setContentType('application/octet-stream');
  var job = {
    configuration: {
      load: {
        destinationTable: {
          projectId: projectId,
          datasetId: datasetId,
          tableId: tableId
        },
        skipLeadingRows: 1 // ヘッダ行は無視
      }
    }
  };
  job = BigQuery.Jobs.insert(job, projectId, blob);
}

function convCsv(range) {
  try {
    var data = range.getValues();
    var ret = "";
    if (data.length > 1) {
      var csv = "";
      for (var i = 0; i < data.length; i++) {
        for (var j = 0; j < data[i].length; j++) {
          if (data[i][j].toString().indexOf(",") != -1) {
            data[i][j] = "\"" + data[i][j] + "\"";
          }
        }
        if (i < data.length-1) {
          csv += data[i].join(",") + "\r\n";
        } else {
          csv += data[i];
        }
      }
      ret = csv;
    }
    return ret;
  }
  catch(e) {
    Logger.log(e);
  }
}

sheet.getDataRange()でスプレッドシートのデータを取得。
Jobs.insertでデータを追加する。

BigQueryのテーブルを削除する

BigQuery.Tables.remove(projectId, datasetId, tableId);