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

ラズパイで取得したセンサー値をGoogleSpreadSheetsでグラフ化するのグラフ化編です。

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

次の通り。

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

下準備する
- スプレッドシートを用意する
- gsファイルを用意する
- BigQueryへのアクセス許可を取得する
AppsScriptでセンサー値を抽出しグラフを描く
- コードを書く
- コードを実行する
- 実行結果を見てみる

下準備する

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

スプレッドシートを用意する
gsファイルを用意する
BigQueryへのアクセス許可を取得する

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

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

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

まずここからログインして新しいスプレッドシートを作成して
グラフ用とセンサー値用のシートを作成します。

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

gsファイルを用意する

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

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

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

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

Enabling advanced services を参考にAppsScriptから
BigQueryを利用できるようにします。

小窓が出てくるので次の通りBigQueryを有効にします。

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

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

AppsScript はJavaScriptベースのスクリプト言語でG Suiteや
GCPのいくつかと連携できます。

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

コードを書く

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

コード.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);
}