GASを使ってSpreadSheetとBigQuery間のデータの受け渡しをする

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

# SpreadSheetの値の取得
```javascript
// スプレッドシートを取得
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のプロジェクト管理、データの追加、クエリの実行ができます。

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2747426/6527f536-71c9-38a4-5489-edd8b8d8ee12.png)
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2747426/0696a3f9-140a-8916-f98c-8b1677127260.png)
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2747426/3cc148db-a2be-ab8d-fd9e-bc68d084979e.png)


公式ドキュメント

https://developers.google.com/apps-script/advanced/bigquery

## コード
```javascript:insert.gs
function insert(value1, value2) {
  var query = `INSERT INTO \`bq_project.bq_dataset.bq_table\`(column1, column2)VALUES(${value1}, ${value2})`;
  execute(query);
}
```
```javascript: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);
}
```

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

## パラメータが増えてコードが見づらくなった時の解決法
スプレッドシートから受け取ったパラメータで以下のようにINSERT文を構築すると、
受け取るパラメータが１０個や２０個と数が増えると以下のようにコードが見づらくなりました。
```javascript: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を作成するとコードがすっきりして見やすくなりました。
```javascript: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();
  }
}
```
```javascript:main.gs
function main() {
  // スプレッドシートを取得
  var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  // シート名を指定してシートを取得
  var sheet = spreadsheet.getSheetByName("sheet");  

  // パラメータ取得
  const parameters = new Parameters(sheet);
  // パラメータが増えてもParametersクラスをいじれば良いので以下のコードは変わらない
  insert(parameters);
  // パラメータを取り出す時は以下のようにすれば取り出せる
  // paranmeters.value1
}
```