Google SpreadsheetからBigQueryを呼び出すスクリプト

main.js

/**
 * Google Spreadsheet向けBigQuery取り込みスクリプト
 * http://toreta.blog.jp/archives/20649904.html
 * License: MIT 2014- Toreta, Inc.
 *
 * runAllQueries() をトリガーで毎日実行してください
 * Queries, Single row queries, Dataの三つのシートを作って下さい
 * Queries, Single row queriesのシートには実行するクエリを書きます
 * A列にクエリ名、B列にクエリです。
 * conuntなどの集約関数で1行しか返らないクエリは「Single row queries」、それ以外は「Queries」に書いて下さい
 */

/* 実行するProject ID */
var projectId = 'example-log';


/* 現在のシートのクエリを実行 */
function runCurrentSheetQuery() {
  var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  var current_sheet = spreadsheet.getActiveSheet();
  
  if(current_sheet.getName() == "Single row queries" || current_sheet.getName() == "Data") {
    runSingleRowQuery();
  }
  else {
    var queries_sheet = spreadsheet.getSheetByName("Queries");
    for (var i = 2; i <= queries_sheet.getLastRow(); i++) {
      if(queries_sheet.getRange(i, 1).getValue() == current_sheet.getName()) {
        runQuery(queries_sheet.getRange(i, 2).getValue(), current_sheet, queries_sheet.getRange(i, 3).getValue().toString());
      }
    }
  }
}

/* シートの全クエリを実行 */
function runAllQueries() {
  runSingleRowQuery();
  
  var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  var queries_sheet = spreadsheet.getSheetByName("Queries");
  
  for (var i = 2; i <= queries_sheet.getLastRow(); i++) {
    var sheet_name = queries_sheet.getRange(i, 1).getValue();
    var sheet = spreadsheet.getSheetByName(sheet_name);
    if(sheet == null) {
      sheet = spreadsheet.insertSheet();
      sheet.setName(sheet_name);
    }
    runQuery(queries_sheet.getRange(i, 2).getValue(), sheet, queries_sheet.getRange(i, 3).getValue().toString());
  }
}

/* クエリを実行 */
function runQuery(query, sheet, title) {
  var request = {
    query: query,
    useLegacySql: false // standard SQLを使うようにする
  };
  var queryResults = BigQuery.Jobs.query(request, projectId);
  var jobId = queryResults.jobReference.jobId;

  // Check on status of the Query Job.
  var sleepTimeMs = 500;
  while (!queryResults.jobComplete) {
    Utilities.sleep(sleepTimeMs);
    sleepTimeMs *= 2;
    queryResults = BigQuery.Jobs.getQueryResults(projectId, jobId);
  }

  // Get all the rows of results.
  var rows = queryResults.rows;
  while (queryResults.pageToken) {
    queryResults = BigQuery.Jobs.getQueryResults(projectId, jobId, {
      pageToken: queryResults.pageToken
    });
    rows = rows.concat(queryResults.rows);
  }

  if (rows) {
    // clear exists data and set title
    sheet.clear();
    sheet.appendRow([title]);
    
    // Append the headers.
    var headers = queryResults.schema.fields.map(function(field) {
      return field.name;
    });
    sheet.appendRow(headers);

    // Append the results.
    var data = new Array(rows.length);
    for (var i = 0; i < rows.length; i++) {
      var cols = rows[i].f;
      data[i] = new Array(cols.length);
      for (var j = 0; j < cols.length; j++) {
        data[i][j] = cols[j].v;
        if(headers[j].substr(-3)=="_at") {
          data[i][j] = cols[j].v
          if(data[i][j] < 10000000000000) {
            data[i][j] = new Date(data[i][j]);
          }
          else if(data[i][j] < 10000000000000*1000) {
            data[i][j] = new Date(data[i][j] / 1000);
          }
        }
      }
    }
    sheet.getRange(3, 1, rows.length, headers.length).setValues(data);
  } else {
    Logger.log('No rows returned.');
  }
};


/* 1行クエリを実行 */
function runSingleRowQuery() {
  // Replace this value with the project ID listed in the Google
  // Developers Console project.
  var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  var queries_sheet = spreadsheet.getSheetByName("Single row queries");
  var result_sheet = spreadsheet.getSheetByName("Data");
  
  for (var i = 2; i <= queries_sheet.getLastRow(); i++) {
    var request = {
      query: queries_sheet.getRange(i, 2).getValue()
    };
    var queryResults = BigQuery.Jobs.query(request, projectId);
    var jobId = queryResults.jobReference.jobId;
    
    // Check on status of the Query Job.
    var sleepTimeMs = 500;
    while (!queryResults.jobComplete) {
      Utilities.sleep(sleepTimeMs);
      sleepTimeMs *= 2;
      queryResults = BigQuery.Jobs.getQueryResults(projectId, jobId);
    }
    
    // Get all the rows of results.
    var rows = queryResults.rows;
    while (queryResults.pageToken) {
      queryResults = BigQuery.Jobs.getQueryResults(projectId, jobId, {
        pageToken: queryResults.pageToken
      });
      rows = rows.concat(queryResults.rows);
    }
    result_sheet.getRange(i, 2).setValue(rows[0].f[0].v);
    
    result_sheet.getRange(i, 1).setValue(queries_sheet.getRange(i, 1).getValue());
    result_sheet.getRange(i, 3).setValue(queries_sheet.getRange(i, 3).getValue());
  }
};

function onOpen() {
  var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  var entries = [{
    name : "このシートの内容を更新",
    functionName : "runCurrentSheetQuery"
  },
  {
    name : "全シートの内容を更新",
    functionName : "runAllQueries"
  }];
  spreadsheet.addMenu("BigQuery", entries);
};