【IoT】スプレッドシートで、爆速で室内環境を可視化する

# 初めに
こんにちは！
日本でSEとして働いている台湾人@rueyjyeです。

学生時代はIoTの研究をやっていました。
IoT機材を使って、センサーで測った数値をターミナルやコンソールには表示できますが、なんかそれだともやもや感があります。
毎回ターミナル入らないと見れないし、 :worried: 
文字と数字だけで分かりづらいし、 :hushed:
かといって取った数値をDBに保存して、いい感じのWEBアプリを作成するのは手間がかかります。 :sob:

個人的にいい感じの対応方法を見つけたので紹介します！

# TL;DR
これらを組み合わせてIoTデータを可視化しました！
• GAS（Google Apps Script）
• スプレッドシート
• IoTデバイス

爆速で実装できるし、リアルタイムでデータの表示ができます。
スプレッドシートなので、いい感じにグラフ作成もできる！

# 作り方説明
今回はraspberry piと温湿度センサーを使って、実際に温湿度を表示するウェブサービスを作ります！

## 1. スプレッドシート
先ずはスプレッドシートを用意しましょう。
<img width="600" alt="スクリーンショット 2018-12-26 9.43.21.png" src="https://qiita-image-store.s3.amazonaws.com/0/189885/283a5f09-056a-6b67-a97e-1939357fdcb9.png">
ただ表を作るだけです！

## 2. GAS
スプレッドシートの「スクリプト エディタ」からGASを作成します。
ここではdoPost関数を使って、外部から受け取るデータの処理を行います。

```js

function doPost(e) {
  var jsonString = e.postData.getDataAsString();
  var data = JSON.parse(jsonString);
  
  var now = new Date();
  var time = Utilities.formatDate(now, "JST", "yyyy/MM/dd HH:mm:ss");

  var device_name = data.device_name;
  var temperature = data.temperature;
  var humidity = data.humidity;

  // シート取得
  var ss = SpreadsheetApp.openById('XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX');
  var sheet = ss.getSheetByName("Dashboard");
  sheet.getRange(3, 2).setValue(device_name);
  sheet.getRange(3, 3).setValue(temperature);
  sheet.getRange(3, 4).setValue(humidity);
  sheet.getRange(3, 5).setValue(time);
}

```
コードが出来たら、次はGASの「公開」から「ウェブアプリケーションとして導入」の機能を使います。
注意点：
下記の設定しないと外部からPOSTが出来ないです。
1.「次のユーザーとしてアプリケーションを実行」は「自分」に設定
2.「アプリケーションにアクセスできるユーザー」は「全員(匿名ユーザーを含む)」に設定


<img width="600" alt="スクリーンショット 2018-12-26 9.49.11.png" src="https://qiita-image-store.s3.amazonaws.com/0/189885/fd27b707-0b70-bf75-fcec-95b59f747df5.png">
<img width="600" alt="スクリーンショット 2018-12-26 9.49.11.png" src="https://qiita-image-store.s3.amazonaws.com/0/189885/6cf150fd-cc03-1eb8-6f1a-72ef9264ea1c.png">
設定ができたら、外部から受け取ったJSON形式のデータの表示ができるようになるので、
試しにcurlで送ってみます。

```bash
curl -X POST -H "Accept: application/json" -H "Content-type: application/json" -d '{"device_name":"raspberry_pi","temperature": 20.5, "humidity": 30}'　https://script.google.com/macros/s/XXXXXXXXXXX/exec
```
反映されましたね！
<img width="600" alt="スクリーンショット 2018-12-27 9.23.37.png" src="https://qiita-image-store.s3.amazonaws.com/0/189885/9af5e423-53c9-c7e1-dcff-b7a82f641504.png">


## 3. IoTデバイス
raspberry piなので、pythonスクリプトを使ってやります。
データを取って、curlで送ります。

センサーはDHT11を使って、このライブラリでデータを取得します。
DHT11 Python library：https://github.com/szazo/DHT11_Python


```python

import RPi.GPIO as GPIO
from DHT11_Python import dht11
import time
import datetime
import subprocess

# initialize GPIO
GPIO.setwarnings(False)
GPIO.setmode(GPIO.BCM)
GPIO.cleanup()

# read data using pin 14
instance = dht11.DHT11(pin=14)

gas_url = 'https://script.google.com/macros/s/XXXXXXXXXX/exec'

while True:
  result = instance.read()
  if result.is_valid():
      print("Last valid input: " + str(datetime.datetime.now()))
      print("Temperature: %d C" % result.temperature)
      print("Humidity: %d %%" % result.humidity)
      retcode = subprocess.check_output(
          'curl -H "Accept: application/json" ' \
          '-H "Content-type: application/json" ' \
          '-X POST -d \'' \
          '{\"device_name\": ' + '\"raspberry_pi\"' + \
          ', \"temperature\": ' + str(result.temperature) + \
          ', \"humidity\": ' + str(result.humidity) + \
          '}\' ' + gas_url,
          shell=True
      )
  time.sleep(10)
```

最後はcronで、起動したら自動実行に設定します。

```bash
@reboot python path_to_file/IoT_device_01.py
```
# 結果
raspberry piの再起動をしスプレッドシートを確認すると、定期的に更新されているのがわかります！
![01.gif](https://qiita-image-store.s3.amazonaws.com/0/189885/f17a114e-9fe6-a6b3-b8da-dacf5eb1428a.gif)

複数のデバイスで同時に表示するのも楽しそう！！！！

# おまけ
温度をスプレッドシートシートの機能を使っていい感じに仕上ましょう。
1.間取り図を使って部屋のイメージで表示します。
<img width="600" alt="スクリーンショット 2018-12-27 19.21.37.png" src="https://qiita-image-store.s3.amazonaws.com/0/189885/9f8d76a5-44ff-55e0-f3ac-161294b49c49.png">

2.カラースケールを使うと、温度が分かりやすくなります。
「条件付き書式設定ルール」の「カラースケール」を使います。
<img width="285" alt="スクリーンショット 2018-12-28 9.18.57.png" src="https://qiita-image-store.s3.amazonaws.com/0/189885/4ef4b08c-bda8-92c7-c067-50a6d215c5df.png">
<img width="600" alt="スクリーンショット 2018-12-28 9.19.44.png" src="https://qiita-image-store.s3.amazonaws.com/0/189885/ef083782-ff32-896a-e16c-546ed18e20c8.png">

3.スプレッドシートの時間関数を使って、off表示もできます。

```
# 更新日1日前と3分前のデータが「off」を表示させる
# E3はupdate_time、C3はtemperature

=IF(DATEVALUE(E3) < DATEVALUE(NOW()), "Off", IF(TIMEVALUE(E3+TIME(0,3,0)) > (TIMEVALUE(NOW())), C3 ,"Off") )
```
「off」も「条件付き書式設定ルール」で灰色にし変更
<img width="600" alt="スクリーンショット 2018-12-28 9.41.06.png" src="https://qiita-image-store.s3.amazonaws.com/0/189885/ec6ce593-5be1-d7a3-176f-49f6422395ca.png">


完成！！！！！！
ゥォー!ヽ(ﾟдﾟヽ)(ﾉﾟдﾟ)ﾉ ウォー!
![giphy.gif](https://qiita-image-store.s3.amazonaws.com/0/189885/30c73a49-9338-8240-dbdc-c4d5abc78d35.gif)


# 最後に
データ保存の部分はまだ改善のところがあると思いますが、可視化できるところは価値が高いと思います。
GASとスプレッドシートはすごい有能なので、いろんなこともできるし、IoTの相性もいいと思います！
皆さんもぜひ試してみてください。
良いお年を！