Raspberry Piで取得したセンサー情報をGoogleスプレッドシートに保存する（Python）

 Raspberry PiにBME280と MH-Z19Cを繋げてデータを取得できたので、cronで定期的にGoogleスプレッドシートに投げる方法のメモ

（尚、センサー類の部分は省略）

Google 公式ドキュメントは以下の辺りを参照した

【 Google APIs　ご利用方法 】 

Google Cloudのアカウントを作成してプロジェクトを事前に作っておき、以下手順を実行

１：Google CloudAPIの有効化

【 Cloud Console API ライブラリ 】からプロジェクトを選択して、APIページで有効にするをクリックして有効化

２：サービスアカウントの作成とJSONキー取得

Google Cloud Platform内から【 APIとサービス 】＞ 【 認証情報 】を開く

画面右下の【 サービスアカウントを管理 】を開く

画面上部の【 +サービスアカウントを作成 】からアカウントを作成する

【 サービスアカウント名 】を入力、２項目の【 サービスアカウントID 】は自動入力されるので、そのまま作成する

作成したサービスアカウントを選び、移動した先で【 キー 】タブを開き【 鍵を追加 】のプルダウンメニューから【 新しい鍵を作成 】を選び、JSON形式の鍵を作成する

操作しているPCにJSON形式の鍵が保存されるので、Raspberry PiにはSCPコマンドなどで送信しておく

３：必要なAPIライブラリ有効化

Google Cloud Platform内から【 APIとサービス 】＞ 【 ライブラリ 】を開く

検索窓から【 Google Derive API 】と【 Google Sheet API  】の２つを有効化する

４：Googleスプレッドシート側での共有設定

データを保存したいスプレッドシートを作成し、画面右上の【 共有 】を開き、上記で作成したサービスアカウントを追加する

サービスアカウントの表記は以下の通り

【 　＜サービスアカウント名＞@＜サービスアカウントID＞.iam.gserviceaccount.com 】

５：Raspberry PiへGoogleスプレッドシートと認証用のライブラリインストール

$ sudo pip install gspread

$ sudo pip install oauth2cliengt

後はPythonでコードを書くだけ

import gspread

import json

import datetime 

from oauth2client.service_account import ServiceAccountCredentials

#Google スプレッドシートAPI認証

scope = ['https://spreadsheets.google.com/feeds','https://www.googleapis.com/auth/drive']

credentials = ServiceAccountCredentials.from_json_keyfile_name('<保存したJSONキーのパス>', scope)

gc = gspread.authorize(credentials)

SPREADSHEET_KEY = '<対象スプレッドシートキー>'

worksheet = gc.open_by_key(SPREADSHEET_KEY).worksheet('<シート名>')

#日時取得

today = datetime.datetime.now()

#Googleスプレッドシートへデータ転送

record = [ today.strftime('%Y/%m/%d %H:%M'), <B列の値>,<C列の値>,<D列の値>,<E列の値>]

worksheet.append_row(record)

<スプレッドシートキー>は該当スプレッドシートのURLに含まれている値を入力する

    https://docs.google.com/spreadsheets/d/<スプレッドシートキー>/edit#gid=0

シート名は日本語でも問題なく認識した

今回のコードではA列に日時を入れている、B〜E列には温度、湿度、気圧、CO2濃度の値をそれぞれ入れて、最終行に追記する形にしている

思いの他簡単に送信できたので、後はデータポータルでの可視化やGoogle Apps Scriptを用いた自動通知などと絡めれば十分使えると思う

端末が増えた場合は、デバイスID的な列を追加するか、スプレッドシートのシート自体を分けてしまえば管理もしやすそうな気がする

次はGCPへのデータ送信もやりたいが、Google IoT Coreからpub/Sub経由でFunctionを使うと課金されるので、試しに使うならBigQueryに直接投げる方法になるかな

GCPの無料枠で作成したCentOSのシステム言語とタイムゾーンの日本語化

GCP上で作成したVMは無料枠のためアメリカリージョンで言語やタイムゾーンを初期のまま放置していたので、そろそろ日本語化しておく。

VMはCent OS 7

■システム言語を日本語へ変更

$ sudo localectl set-locale LANG=ja_JP.UTF8

■タイムゾーンを日本へ変更

$ sudo timedatectl set-timezone Asia/Tokyo

■再起動して設定反映

$ sudo systemctl reboot

GCEのCent OSへログインするSSHポートを22番から変更する

Google Cloud Platform（以下GCP）のGoogle Compute Engine（以下GCE）で作成したVMインスタンスへアクセスする際のSSHポートを変更する。
今回は「22」から「20055」へ変更する。


GCEはVMインスタンスの上位にFWがあるため、設定変更箇所は下記の4箇所となる。

・GCPのファイアウォールルールに追加してVMに適用
・Cent OSのSELinuxにポリシー追加
・Cent OSのfirewalldに設定追加
・sshdにポート番号追加

その後疎通確認してから22番ポートを閉じていく。



■GCPのファイアウォールルールに追加する。

メニューの「VPCネットワーク」　-　「ファイアウォールルール」から「ファイアウォールルールを作成」から新規ルール作成画面に移る。

設定するのは下記の項目。

「名前」はわかりやすいように「allow-ssh20055」に設定

「ターゲットタグ」を名称と同じにして、VMインスタンスへ適用する際にわかりやすくしてみた。任意なので自分がわかりやすければOK。

「ソースIPの範囲」は「0.0.0.0/0」

「プロトコルとポート」は「指定したプロトコルとポート」から「tcp」にチェックを付けて「20055」を入力して保存。


VMインスタンスの編集から設定画面を開いて「ネットワークタグ」に先程作成したルールの「ターゲットタグ」を入力する。

これでGCP上のファイアウォールルールでTCPの20055番ポートが空いた。




■SELinuxにポリシー追加

SELinuxの設定を変更するためVMにSSHでログインしてroot権限に移行する
$ sudo su -

SELinuxの状態を確認する。
# getenforce

「Enforcing」と出れば有効化されているので作業を続ける。
設定がややこしいからと言って無効化するような無粋なことはしない。

SELinuxの設定変更には「semanage」コマンドを利用するが、今回立てたVMインスタンスのCent OSではすでにインストール済みだった為このまま続ける。

まずはSELinuxが許可しているSSHのポートを確認する。
# semanage port --list | grep ssh

tcpの22番ポートが許可されているので、これに今回追加する20055を追加する。
「--add」オプションでポリシーを追加する。
# semanage port --add --type ssh_port_t --proto tcp 20055

設定が追加されているかを確認する。
# semanage port --list | grep ssh

これでSELinuxのポリシーに20055番ポートの設定が追加された。
22番ポートが残っているが他の設定があるから一旦このままで。




■firewalldに設定追加

次にfirewalldの設定を変更する。
firewalldの稼働状況の確認から。
# systemctl status firewalld

Active:の値がactive(running)となっているのでファイアウォールは有効になっている。
eth0のところにWARNINGが出ているが今回は無視しよう。

現状の確認としてファイアウォールが外に向けて公開しているサービスを確認する。
# firewall-cmd --list-services --zone=public --permanent

開放するポートを設定するが、この設定が入っているファイル「/usr/lib/firewalld/services/ssh.xml 」ではなく、
「/etc/firewalld/services/」に「ssh.xml」を配置して設定を記述する必要がある。

既存のファイルを/etc/firewalld/services/にコピーする
# cp /usr/lib/firewalld/services/ssh.xml /etc/firewalld/services/


コピーした先のファイルに2005番ポートの設定を追加する。
# vi /etc/firewalld/services/ssh.xml

設定が完了したらfirewalldの設定を再読込させる。
# firewall-cmd --reload

■sshdにポート番号を追加

最後にsshdにポート番号を追加する。
設定ファイルは「/etc/ssh/sshd_config」に記述されているのでこちらを編集する。

# vi /etc/ssh/sshd_config
上の方に#でコメントアウトしてある「#Port22」を下記に修正
Port 22
Port 20055

vimだとコメントアウトした文字色が背景色に近く見辛かったので今回はviで編集。
またその内vimの設定イジるか。

これで22番と20055番でsshへアクセスできるようになったはずなので、設定を再読込する。
# systemctl restart sshd


クライアントPC側の「.ssh」フォルダにある「config」ファイルのポート番号も忘れずに新しいポート番号に書き換えて、新しいポート番号でアクセスしてみる。

ここまでで新しく設定した20055番ポートでSSH接続できるようになった。
あとは残っている22番ポートの設定を無効化していけばいい。




■GCPのファイアウォールルールに追加

冒頭で行った新しいポート番号を登録する要領で22番ポートを拒否するルールを作成して、VMインスタンスへ適用する。

「名前」はわかりやすいように「deny-ssh22」に設定

「一致したときのアクション」を「拒否」に設定

「ターゲットタグ」はこれも名前と同じモノに

「ソースIPの範囲」は「0.0.0.0/0」

「プロトコルとポート」は「指定したプロトコルとポート」から「tcp」にチェックを付けて「20055」を入力して保存。

同様にVMインスタンスの編集から設定画面を開いて「ネットワークタグ」に先程作成したルールの「ターゲットタグ」を入力する。


また、ブラウザからのSSH接続はこのままでは出来ないが、

VMインスタンスのSSHの右にあるプルダウンメニューから「ブラウザウィンドウでカスタムポートを開く」を実行して、新しく設定した20055を入力すれば問題なく接続できた。

実際の挙動としてはこれをやるだけで外部からの22番ポートでのSSH接続はできなくなる。

だからといって他の設定を置いておくのも気持ち悪いので22番ポートの設定を無効化していく。

■sshdから22番ポートを無効化

上で編集した「/etc/ssh/sshd_config」を編集する。

# vi /etc/ssh/sshd_config
22番ポートの情報をコメントアウト
#Port 22

設定を再読込する。
# systemctl restart sshd




■firewalldから22番ポートを無効化

上で編集したファイルから22番ポートの記述を削除する。
# vi /etc/firewalld/services/ssh.xml

firewalldの設定を再読込させる。
# firewall-cmd --reload




■SELinuxのポリシーから削除（出来なかった）

削除する場合は「semanage」コマンドで「--delete」オプションを利用する。
# semanage port --delete --type ssh_port_t --proto tcp 22

とやってみたものの、
「ポート tcp/22 はポリシーに定義されているため、削除できません」
との事で削除出来なかった。


まぁこれで22番ポートのアクセスが出来ないことは確認できたので良しとしようか。

Windows10のPowerShellからGCPへのSSH接続

TwitterでWindows10 バージョン1803からOpenSSHが実装されてるらしいとの情報を見たので少し確認。

「アプリと機能」から「オプション機能の管理」を確認すると確かに「OpenSSHクライアント」がインストールされている。

せっかくなのでGoogle Cloud Platform（以下GCP）に無料枠でVMを作成してSSHで繋げて見ようかと。


「GCP」に移動して自分のプロダクトへ移動して
「Compute Engine」 - 「VMインスタンス」 から新規作成。

スペックはGCPの無料枠である「Always Free」の範囲内に収めるために以下の構成で。

リージョン：us-west1
ゾーン：us-west1-b
マシンタイプ：f1-micro（CPU：0.6GHz、メモリ：0.6GB）
OS：Cent OS 7　30GBの標準の永続ディスク

ついでに外部公開用の固定IPもVMに紐付ける1個だけなら無料らしいので一緒に作成する。

作成したVMの右側にあるSSHを選べばブラウザからSSHでのアクセスは可能。

でもまぁコレじゃ面白くないのでWindowsから接続できるように準備する。
一応「OpenSSH」のマニュアルページを参照。


PowerShellを起動して以下コマンド実行
> ssh-keygen -t rsa -b 4096

-t で暗号方式を RSA に指定
-b で鍵の長さを 4096 に指定（デフォルト値は2048）

これでユーザフォルダ直下に「.ssh」フォルダが作成され、公開鍵と秘密鍵が生成された。

以下コマンドを実行して公開鍵の内容を表示する。

> cat ~/.ssh/id_rsa.pub

ssh-rsa　** ～　** == (ユーザ名）の様な表記で表示されるので全てコピペする。

もちろん該当ファイルをテキストエディタなどで開いても同様の物が見れる。

SSHで繋げたいVMを選んで編集画面に入ると「SSH認証鍵」の項目があるので、こちらの空欄に先程の文字列をそのままペーストする。

自動的に空欄の左側にSSHでのログイン用ユーザ名が表示されるが、

このVM内にも同じユーザ名で自動的にユーザを作成してくれる。

便利だ。

内容を確認したら保存ボタンで設定内容を更新。

PowerShellから下記コマンドでアクセスできるかを確認する。

> ssh （ユーザ名）@（IPアドレス） -i ~/.ssh/id_rsa

これでPowerShellからGCPのVMへSSHでログインできるようになったものの、毎回コレを入力するのは面倒なので「config」ファイルを作成する。

先程のログインで「known_hosts」ファイルが作成されていることも念の為確認。

こちらは接続したサーバ側のSSHサーバ証明書が格納されている。

「.ssh」フォルダの直下に「config」ファイルを作成してテキストエディタで下記内容を入力

Host （任意の名称）

HostName （IPアドレス or ドメイン名） 

User （ユーザ名）

Port 22（接続ポート）

IdentityFile ~/.ssh/id_rsa（秘密鍵のパス）

今回はHost名をmail-srvに設定した。その内メールサーバにしたい。

これでPowerShellから

> ssh mail-srv（設定したHostの名称）でアクセスして問題なければ大丈夫かな。

GCPの無料枠は中国やオーストラリアからのVM宛の通信は無料枠対象外だから、気が向いたらSSHのポートを初期の22から別に変更しよう。

～ブログ更新～

設定してみた。

「GCEのCent OSへログインするSSHポートを22番から変更する」