SpreadSheetでGoogleAppScriptを使ってみた(データロード)

やりたきこと

spread sheetにgasを書いて定期実行でBQにデータをロード

~背景説明~
spread sheetのデータを定期実行でBQに入れる時は、
スプレッドシートのデータをBigQueryにインポートしてみた
にあるようなやり方で簡単に入れることができます。
しかし
社内の規約でサービスアカウントからBQは見れるが,
spread sheetが見に行けない状態でした。。。
そのためgasを書くことに。。・

やり方

1. bigquery APIやspread sheet APIを色々許可する（詳しいところは忘れました）
2. spread sheetを開いて ツール>スクリプトエディタ の順でスクリプトエディタを開く
3. 以下のgas(Google Apps Script)を記入
4. 編集>現在のプロジェクトのトリガ を開く
5. 好きなタイミングを指定して保存

load.gs

//BQ側の設定
var projectNumber = 'PROJECT_NAME';
var datasetNumber = 'DATASET_NAME';
var tableId = 'TABLE_NAME';

//spread sheetをデータをとる
var ss = SpreadsheetApp.getActiveSpreadsheet();
var master_sheet = ss.getSheetByName('SHEET_NAME');
var rowNum = master_sheet.getLastRow();

//即時実行ボタンをメニューに追加
function onOpen() {
  var menus = [{name:'実行',functionName:'loadData'}];
  ss.addMenu('BigQuery',menus);
}

//対象テーブルにデータをロードするファンクション
function insertData(blob) {
  var job = {
    configuration: {
      load: {
        destinationTable: {
          projectId: projectNumber,
          datasetId: datasetNumber,
          tableId: tableId
        },
        skipLeadingRows: 0,
        allowJaggedRows: true,
        allowQuotedNewlines: true,
        writeDisposition: 'WRITE_TRUNCATE'
      }
    }
  };
  job = BigQuery.Jobs.insert(job, projectNumber, blob);
  Logger.log('Load job started. Check on the status of it here: ' +
      'https://bigquery.cloud.google.com/jobs/%s', projectNumber);
}

//配列をblobに変換するファンクション
function values_to_blob(values) {
  // 定数
  var contentType = "text/csv";
  var charSet = "UTF-8";
  var lineDelimiter = ",";
  var blob = Utilities.newBlob("", contentType, "");

  // 2次元配列になっているデータをcsvのstringに変換
  var csvStr = '';

  //underscoreGSモジュールを使用して配列ごとに処理を実行
  underscoreGS._map(
    values,
    function(row){
      for (var i = 0; i < row.length; i++) {
        //改行などが入っているとテーブルがおかしくなるので削除している
        row[i] = row[i].replace("\n","").replace(",","").replace("\r\n","").replace("\r","").replace("\n","")
      }
      csvStr += row.join(lineDelimiter) + '\n';
    }
  )
  return Utilities.newBlob(csvStr,contentType)
}


//main(getRange内の'C'は列数によって変化します、今回は３列)
function loadData() {
  var values = master_sheet.getRange("A1:C"+rowNum).getValues();
  var blob = values_to_blob(values)
  insertData(blob)
}

今回使わなかったコード

テーブル作成

create_table.gs

function createTable() {
  var table = {
    tableReference: {
      projectId: projectNumber,
      datasetId: datasetNumber,
      tableId: tableId
    },
    schema:{
      fields: [
        {name: 'COLUMN_NAME1', type: 'STRING'},
        {name: 'COLUMN_NAME2', type: 'STRING'},
        {name: 'COLUMN_NAME3', type: 'STRING'}
      ]
    }
  };
  table = BigQuery.Tables.insert(table, projectNumber, datasetNumber);
  Logger.log('Table created: %s', table.id);
}

クエリ実行

query.gs

function execQuery(query) {
  var resource = {
    query : sql,
    timeoutMs: 1000000
  };
  try {
    query_results = BigQuery.Jobs.query(resource, projectNumber);
  }
  catch (err) {
    Logger.log(err);
    Browser.msgBox(err);
    return;
  }
  while (query_results.getJobComplete() == false) {
    try {
      query_results = BigQuery.Jobs.getQueryResults(projectNumber, queryResults.getJobReference().getJobId());
      if (query_results.getJobComplete() == false) {
        Utilities.sleep(3000);
      }
    }
    catch (err) {
      Logger.log(err);
      Browser.msgBox(err);
      return;
    }
  }
}