5
4

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?

More than 3 years have passed since last update.

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

Last updated at Posted at 2021-12-13

この記事について

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 test@gmail.com 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);
5
4
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
5
4

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?