「スプレッドシート」と「BigQuery」をクエリ内でJOINしてtableauで可視化したい

### スプレッドシートをtableauで見たい
Tableau Desktopから直接接続できます！ 詳細は[こちら](https://www.tableau.com/ja-jp/about/blog/2016/5/connect-directly-your-google-sheets-tableau-10-53954)

### スプレッドシートをBigQuery上に連携したい
BigQueryのインポートデータにスプレッドシートを指定することができます 詳細は[こちら](https://cloud.google.com/blog/products/gcp/bigquery-integrates-with-google-drive)

# 本題「スプレッドシートとBigQueryのテーブルをJOINしたクエリを作ってTableauで見る」

**結論：2019年3月26日時点では、未実装の機能だそうです。 （tableauのテクニカルサポートの方に問い合わせました）**
*推測ですが、BigQueryへの接続とスプレッドシートへの接続の認証が共存できないのかも。*

## なので、なんとかしてみる

### 回避手段1 「GoogleAppsScriptからスプレッドシートの中身読み込んでBigQueryに書き込む」

スプレッドシートのデータ

uuid | question_id | question_text | answer_id | answer_text
----- | ----- | ----- | ----- | -----
aaa | 123 | 質問１ | 10 | 回答１
bbb | 123 | 質問１ | 11 | 回答２
ccc | 456 | 質問２ | 20 | 回答A

サンプルコード このsyncBigQueryをスプレッドーシートの[メニューに追加したり](https://developers.google.com/apps-script/reference/spreadsheet/spreadsheet#addmenuname-submenus)、 [トリガーで定期実行](https://developers.google.com/apps-script/reference/script/clock-trigger-builder) も可能です。

```javascript

function syncBigQuery() {
  var projectId = 'test_project_desu'; //プロジェクトID
  var datasetId = 'test_set'; //データセット
  var tableId = "test_master";  //テーブル
  var sheet = SpreadsheetApp.openByUrl("https://スプレッドシートのURL");
  var columnCnt = 5; //BigQueryに格納するデータの範囲
  var table = {
    tableReference: {
      projectId: projectId,
      datasetId: datasetId,
      tableId: tableId
    },
    schema: {
      fields: [
        {name: 'uuid', type: 'STRING'},
        {name: 'question_id', type: 'INTEGER'},
        {name: 'question_text', type: 'STRING'},
        {name: 'answer_id', type: 'INTEGER'},
        {name: 'answer_text', type: 'STRING'}
      ]
    }
  };
  
  //今回は簡易的な感じで入れ替える運用したため、一旦削除しています。
  BigQuery.Tables.remove(projectId, datasetId, tableId);
  table = BigQuery.Tables.insert(table, projectId, datasetId);
  
  var range = sheet.getDataRange();

  //データを格納する FYI:https://developers.google.com/google-ads/scripts/docs/examples/google-bigquery
  var insertAllRequest = BigQuery.newTableDataInsertAllRequest();
  insertAllRequest.rows = convertValuesToRows(range.getValues(), columnCnt);
  var result = BigQuery.Tabledata.insertAll(insertAllRequest, projectId, datasetId, tableId);
  
  if (result.insertErrors != null) {
    var allErrors = [];

    for (var i = 0; i < result.insertErrors.length; i++) {
      var insertError = result.insertErrors[i];
      allErrors.push(Utilities.formatString('Error inserting item: %s',
          insertError.index));

      for (var j = 0; j < insertError.errors.length; j++) {
        var error = insertError.errors[j];
        allErrors.push(Utilities.formatString('- ' + error));
      }
    }
    Logger.log(allErrors.join('\n'));
  } else {
    Logger.log(Utilities.formatString('%s data rows inserted successfully.', insertAllRequest.rows.length));
  }
}

//すべて文字列扱いだと、取扱が面倒なので数値型に変換します。
function changeData(rowData) {
  var question_id = 1; //配列のindex
  var answer_id = 3; //配列のindex
  rowData[question_id] = Number(rowData[question_id]);
  rowData[answer_id] = Number(rowData[answer_id]);
  return rowData;
}

function convertValuesToRows(data, columnCnt) {
  var rows = [];  
  var headers = data[0].slice(0, columnCnt);

  for (var i = 1, numColumns = data.length; i < numColumns; i++) {
    var row = BigQuery.newTableDataInsertAllRequestRows();
    var rowData = changeData(data[i].slice(0, columnCnt));
    row.json = rowData.reduce(function(obj, value, index) {
      obj[headers[index]] = value;
      return obj;
    }, {});
    rows.push(row);
  }
  return rows;
}

```

Google Apps Script内でCSVファイルを生成してCSVファイルをインポートすることも可能ですが、改行コードが含まれていたり、カンマが含まれていたり、数値で扱いたかったり等々小回りが効きにくいです。 TableDataInsertAllを利用して格納するの方がデータが扱いやすくておすすめです。

### 回避手段2 「CSV化してBigQueryにインポートする」

1. スプレッドシートからCSVファイルを生成
<img width="762" alt="スクリーンショット 2019-03-27 13.02.47.png" src="https://qiita-image-store.s3.amazonaws.com/0/73861/fe6bbc77-062c-7537-0820-4d821b1eab17.png">

2.BigQueryのコンソールからCSVファイルを投入
<img width="778" alt="スクリーンショット 2019-03-27 13.07.06.png" src="https://qiita-image-store.s3.amazonaws.com/0/73861/ecc3c41e-fdd3-d125-8a18-3c8545d4de55.png">

この手段ならスクリプトが書けなくてもサクッとJOINしてtableauで可視化することが可能です。ただ、データの更新頻度が高いと面倒になります。 :cry: 



(最近は社内の [esa](https://esa.io/) にTipsを書いていたのですが、ちょっと嵌まる人が多そうだったのでこちらに記載しました)