はじめに

GASを使って、SpreadSheetに入力した値を取得しBigQueryに登録したり
BigQueryから取得したデータをSpreadSheetに書き出したり
SpreadSheetとBigQuery間のデータの受け渡しをする方法をまとめました。

SpreadSheetの値の取得

// スプレッドシートを取得
var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
// シート名を指定してシートを取得
var sheet = spreadsheet.getSheetByName("sheet");
// 特定のセルの値を取得
var value = sheet.getRange("A1").getValue();

GASからBigQuery のクエリを実行する

「BigQuery API」を有効にする

BigQueryサービスを有効にすると、Google App ScriptからBigQuery APIが利用できるようになります。
API経由で、BigQueryのプロジェクト管理、データの追加、クエリの実行ができます。

公式ドキュメント

コード

insert.gs

function insert(value1, value2) {
  var query = `INSERT INTO \`bq_project.bq_dataset.bq_table\`(column1, column2)VALUES(${value1}, ${value2})`;
  execute(query);
}

select.gs

function select() {
  var query = "SELECT column1, column2 FROM `bq_project.bq_dataset.bq_table`";
  const result = execute(query);
  
  // 結果の件数は以下のように記述できる
  if (result.totalRows == '0') {
    Browser.msgBox(`取得結果0件。`);
    return;
  }
  
  let rows = []; // selectしたデータをループで格納する用の配列
  for (let i = 0; i < result.rows.length; i++) {
    let resultRow = result.rows[i];
    rows.push(
      [
        resultRow.f[0].v, // selectした1つ目のカラムを取得
        resultRow.f[1].v // selectした2つ目のカラムを取得
      ]
    );
  }
  
  // スプレッドシートを取得
  var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  // シート名を指定してシートを取得
  var sheet = spreadsheet.getSheetByName("sheet");
  // 以下のように記述するとselectした値をスプレッドシートに書き込める
  // 例：6行目1列目にselect取得結果を入力
  // getRange(row, column, numRows, numColumns)
  // setValues: 値を入力
  sheet.getRange(6, 1, rows.length, rows[0].length).setValues(rows);
}

execute_query.gs

function execute(query) {
  const project_id = "bq_project"; // クエリを実行するBQプロジェクト
  return BigQuery.Jobs.query(
    {
      useLegacySql: false,
      query: query
    },
    prject_id
  )  
}

パラメータが増えてコードが見づらくなった時の解決法

スプレッドシートから受け取ったパラメータで以下のようにINSERT文を構築すると、
受け取るパラメータが１０個や２０個と数が増えると以下のようにコードが見づらくなりました。

main.gs

function main() {
  // スプレッドシートを取得
  var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  // シート名を指定してシートを取得
  var sheet = spreadsheet.getSheetByName("sheet");
  
  var value1 = sheet.getRange("A1").getValue();
  var value2 = sheet.getRange("B1").getValue();
  var value3 = sheet.getRange("C1").getValue();
  var value4 = sheet.getRange("D1").getValue();
  var value5 = sheet.getRange("E1").getValue();
  var value6 = sheet.getRange("F1").getValue();
  var value7 = sheet.getRange("G1").getValue();
  var value8 = sheet.getRange("H1").getValue();
  var value9 = sheet.getRange("I1").getValue();
  var value10 = sheet.getRange("J1").getValue();

  // メソッドにパラメータ渡す
  // パラメータの数が増えるとどんどん横に長くなる
  insert(value1, value2, value3, value4, value5, value6, value7, value8, value9, value9, value10);

}

以下のようにパラメータを取得するClassを作成するとコードがすっきりして見やすくなりました。

parameters.gs

Class Parameters {
  constructor(sheet, value1, value2, value3, value4, value5, value6, value7, value8,value9, value10) {
    this.value1 = sheet.getRange("A1").getValue();
    this.value2 = sheet.getRange("B1").getValue();
    this.value3 = sheet.getRange("C1").getValue();
    this.value4 = sheet.getRange("D1").getValue();
    this.value5 = sheet.getRange("E1").getValue();
    this.value6 = sheet.getRange("F1").getValue();
    this.value7 = sheet.getRange("G1").getValue();
    this.value8 = sheet.getRange("H1").getValue();
    this.value9 = sheet.getRange("I1").getValue();
    this.value10 = sheet.getRange("J1").getValue();
  }
}