ラズパイで取得したセンサー値をGoogleSpreadSheetsでグラフ化する【グラフ化編】(4/4)

[ラズパイで取得したセンサー値をGoogleSpreadSheetsでグラフ化する](https://qiita.com/tnagao3000/private/2059938038a685b2ec8f)のグラフ化編です。

前回まででラズパイのセンサー値はBigQueryに蓄積されています。
今回は蓄積されたセンサー値を抽出してSpreadSheetsでグラフ化します。

次の通り。

![image.png](https://qiita-image-store.s3.amazonaws.com/0/26808/e0d373c5-7f13-ce1f-e179-8a3336b3337d.png)

今回は次の順に設定を行います。

- [下準備する](#%E4%B8%8B%E6%BA%96%E5%82%99%E3%81%99%E3%82%8B)
    - スプレッドシートを用意する
    - gsファイルを用意する
    - BigQueryへのアクセス許可を取得する
- [AppsScriptでセンサー値を抽出しグラフを描く](#appsscript%E3%81%A7%E3%82%BB%E3%83%B3%E3%82%B5%E3%83%BC%E5%80%A4%E3%82%92%E6%8A%BD%E5%87%BA%E3%81%97%E3%82%B0%E3%83%A9%E3%83%95%E3%82%92%E6%8F%8F%E3%81%8F)
    - コードを書く
    - コードを実行する
    - 実行結果を見てみる

## 下準備する

AppsScriptを書く前に下記を用意します。

- [スプレッドシートを用意する](#%E3%82%B9%E3%83%97%E3%83%AC%E3%83%83%E3%83%89%E3%82%B7%E3%83%BC%E3%83%88%E3%82%92%E7%94%A8%E6%84%8F%E3%81%99%E3%82%8B)
- [gsファイルを用意する](#gs%E3%83%95%E3%82%A1%E3%82%A4%E3%83%AB%E3%82%92%E7%94%A8%E6%84%8F%E3%81%99%E3%82%8B)
- [BigQueryへのアクセス許可を取得する](#bigquery%E3%81%B8%E3%81%AE%E3%82%A2%E3%82%AF%E3%82%BB%E3%82%B9%E8%A8%B1%E5%8F%AF%E3%82%92%E5%8F%96%E5%BE%97%E3%81%99%E3%82%8B)

また記事内では次の通り設定に名前を付けました。

|項目|名前|
|:--|:--|
|スプレッドシート名|ラズパイグラフ|
|グラフ用シート名|graph_sheet|
|センサー値用シート名|sensor_value_sheet|

### スプレッドシートを用意する

まず [ここから](https://www.google.com/intl/ja_jp/sheets/about/) ログインして新しいスプレッドシートを作成して
グラフ用とセンサー値用のシートを作成します。

![image.png](https://qiita-image-store.s3.amazonaws.com/0/26808/a1f7b65c-20ca-d39c-03ad-3265ba5a07a6.png)

新しいシートが出来たら名前を付けます。

![image.png](https://qiita-image-store.s3.amazonaws.com/0/26808/a6425c6b-d813-879a-b75a-dc42ae8d600e.png)

### gsファイルを用意する

これはAppsScriptのソースファイルです。

シートのヘッダ部のツール → スクリプトエディタからコード
編集画面を開き、そのままプロジェクト名を指定して保存します。

![image.png](https://qiita-image-store.s3.amazonaws.com/0/26808/6b0f08da-0044-59ed-ca3a-50b66f4276a0.png)

エディタが開いたら、プロジェクトに適当な名前を付けて保存します。

![image.png](https://qiita-image-store.s3.amazonaws.com/0/26808/adc5656b-130e-05eb-0685-0bc44d6c7f99.png)

### BigQueryへのアクセス許可を取得する

[Enabling advanced services](https://developers.google.com/apps-script/guides/services/advanced#enabling_advanced_services) を参考にAppsScriptから
BigQueryを利用できるようにします。

![image.png](https://qiita-image-store.s3.amazonaws.com/0/26808/0dc16984-d3aa-4ddd-4762-fad7e17c0f5b.png)
<br>
小窓が出てくるので次の通りBigQueryを有効にします。
![image.png](https://qiita-image-store.s3.amazonaws.com/0/26808/58816c66-4097-20ce-1aa4-80c6c4fd1052.png)

以上で下準備が完了です。

## AppsScriptでセンサー値を抽出しグラフを描く

[AppsScript](https://developers.google.com/apps-script/overview) はJavaScriptベースのスクリプト言語でG Suiteや
GCPのいくつかと連携できます。

今回はBigQueryからセンサー値を抽出してチャートを描画します。
これらの正確なところは次のリファレンスを参照してください。

- [BigQuery](https://developers.google.com/apps-script/advanced/bigquery)
- [Spreadsheet](https://developers.google.com/apps-script/reference/spreadsheet)

### コードを書く

次の通りです。
mainLogic() がエントリポイントです。
処理内容はソースのコメントの通りです。

```javascript:コード.gs
PROJECT_ID = "raspi-comfort-sensor";                                  // BigQueryのPROJECT ID

SPREADSHEET_ID = "xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx";      // スプレッドシートのID
SENSOR_VALUE_SHEET = "sensor_value_sheet"                             // センサー値 貼付シート
GRAPH_SHEET = "graph_sheet"                                           // グラフ 貼付シート

// センサー値のカラム名
HEADER = ["time(JST)", "temperature(℃)", "pressure(hPa)", "humidity(%)", "illuminance(lx)", "co2(ppm)"];

// エントリポイント
function mainLogic() {
  
  // シートを取得
  var valueSheet = SpreadsheetApp.openById(SPREADSHEET_ID).getSheetByName(SENSOR_VALUE_SHEET);
  var graphSheet = SpreadsheetApp.openById(SPREADSHEET_ID).getSheetByName(GRAPH_SHEET);

  // センサー値を抽出・シート貼付
  getSensorValues(valueSheet);
  
  // チャートを作成・シート貼付
  createChart(valueSheet.getDataRange(), graphSheet);
}

// 直近12時間のセンサー値を抽出して貼付
function getSensorValues(valueSheet) {

  // 抽出
  var request = {
    useLegacySql: false,
    query:  ' SELECT FORMAT_TIMESTAMP("%F %X", time, "Asia/Tokyo") as jst,       ' +
            '        temperature,                                                ' +
            '        pressure,                                                   ' +
            '        humidity,                                                   ' +
            '        illuminance,                                                ' +
            '        co2                                                         ' +
            ' FROM sensor_dataset.sensor_table                                   ' +
            ' WHERE device = "raspi-is"                                          ' +
            ' AND time >= TIMESTAMP_ADD(CURRENT_TIMESTAMP(), INTERVAL -12 HOUR)  ' +
            ' ORDER BY jst                                                       '  };
  
  var queryResults = BigQuery.Jobs.query(request, PROJECT_ID);
  var jobId = queryResults.jobReference.jobId;

  var sleepTimeMs = 500;
  while (!queryResults.jobComplete) {

    Utilities.sleep(sleepTimeMs);
    sleepTimeMs *= 2;
    queryResults = BigQuery.Jobs.getQueryResults(PROJECT_ID, jobId);
  }
  
  var records = queryResults.rows;
  while (queryResults.pageToken) {
    queryResults = BigQuery.Jobs.getQueryResults(PROJECT_ID, jobId, {
      pageToken: queryResults.pageToken
    });
    records = records.concat(queryResults.rows);
  }

  // シートを初期化
  valueSheet.clear();

  // ヘッダを貼付
  valueSheet.getRange(1, 1, 1, HEADER.length).setValues([HEADER]);
  
  // センサー値を貼付
  var data = new Array(records.length);
  for (var i = 0; i < records.length; i++) {

    var cols = records[i].f;
    data[i] = new Array(cols.length);
    for (var j = 0; j < cols.length; j++) {
      data[i][j] = cols[j].v;
    }
  }

  valueSheet.getRange(2, 1, records.length, HEADER.length).setValues(data);
}

// チャートを作成してシートに貼付
function createChart(recordRange, graphSheet) {

  // 既存チャートがあれば削除
  var charts = graphSheet.getCharts();
  for (var i in charts) {
    graphSheet.removeChart(charts[i]);
  }

  // チャートを作成
  var chart = graphSheet.newChart()
      .setChartType(Charts.ChartType.LINE)
      .addRange(recordRange)
      .setOption('width', 800)
      .setOption('height', 500)
      .setPosition(1, 1, 0, 0)
      .build()

  graphSheet.insertChart(chart);
}
```

※ 定数 SPREADSHEET_ID の値は [リファレンス](https://developers.google.com/apps-script/reference/spreadsheet/spreadsheet-app#openById(String)) を参考に取得します。


### コードを実行する

スクリプトエディタで mainLogic() を選択して実行します。
次の通りです。

![image.png](https://qiita-image-store.s3.amazonaws.com/0/26808/eba5aa91-8b98-179c-b774-c29a7607530b.png)

上記のような手動実行のほかにタイマーでも実行できます。
※ これはシートを閉じていても発火するので要注意です。

![image.png](https://qiita-image-store.s3.amazonaws.com/0/26808/3c31209c-d711-d050-1ec7-79b2e0f910b9.png)

実行対象と間隔を指定してトリガーを追加します。

![image.png](https://qiita-image-store.s3.amazonaws.com/0/26808/d341c011-d057-1d13-684b-c291b36c2816.png)

因みに [シートを開く操作](https://developers.google.com/apps-script/guides/triggers/) もトリガーになるのですが、これは
利用可能なAPIが制限されており、今回は使用できませんでした。

### 実行結果を見てみる

いよいよ最終的な実行結果を見てみます。

正常に動作すると sensor_value_sheet と graph_sheet に
それぞれセンサー値とグラフが出てきます。
次の通りです。

#### 実行結果：sensor_value_sheet
![image.png](https://qiita-image-store.s3.amazonaws.com/0/26808/8cf3d202-8d5c-e138-3ab4-e509482ff0ba.png)
<br>
#### 実行結果：graph_sheet
![image.png](https://qiita-image-store.s3.amazonaws.com/0/26808/a2b51ef5-0e95-f96f-7744-2cf93179616e.png)

・
・
・
・
![image.png](https://qiita-image-store.s3.amazonaws.com/0/26808/9c5f9115-16ad-71cd-1973-e02b83ab3d61.png)

以上でおしまいです。