定期的にネットワーク速度を計測してスプレッドシートにまとめる part2

できたもの

こんな感じで1時間ごとに自宅のネットワーク速度を計測してGoogleスプレッドシートにまとめてくれるようになりました。

ネットワーク計測とGASにPOSTをリクエストするスクリプトの作成とGASでウェブアプリの作成が主な作業内容です。

下記を参考に作成しました。

part1でネットワーク速度の計測までできたので、
スプレッドシートにまとめられるようにウェブアプリケーションを作成します。

GASでウェブアプリケーション作成

スプレッドシートをひらいて 拡張機能 -> App Script を選択します。
開いた先で、下記のコード記述します。

var prop = PropertiesService.getScriptProperties();
var verifyToken = prop.getProperty('VERIFY_TOKEN');
var sheetId = prop.getProperty('SHEET_ID');
var sheetName = prop.getProperty('SHEET_NAME');

function doPost(e) {
  // トークンの検証
  var params = JSON.parse(e.postData.getDataAsString());
  var token = params.token;
  if (verifyToken !== token) {
    throw new Error(`invalid token. token=${token}`);
  }

  // スプレッドシート設定
  var ss = SpreadsheetApp.openById(sheetId);
  var sh = ss.getSheetByName(sheetName);

  // 最終行を取得
  var lastrow = sh.getLastRow();
  var date = params.data.date;
  if (lastrow === 1) {
    sh.insertRowAfter(lastrow);
    lastrow++;
    sh.getRange(lastrow, 1).setValue(date);
  } else {
    // yyyy-MM-dd形式で最終行の日付を取得
    var lastdate = Utilities.formatDate(sh.getRange(lastrow, 1).getValue(), 'Asia/Tokyo', 'yyyy-MM-dd');
    // 取得した日付とPOSTの日付が異なる場合
    if (lastdate !== date) {
      // 最終行の1行下に新しく用意
      sh.insertRowAfter(lastrow);
      lastrow++;
      sh.getRange(lastrow, 1).setValue(date);
    }
  }

  // 時刻
  var hour = params.data.hour;
  // ネット速度
  var speed = params.data.speed;

  // 最終行と時刻に対応した列を指定して、速度の値を設定
  sh.getRange(lastrow, hour + 2).setNumberFormat('0.00').setValue(speed);

  return ContentService.createTextOutput('OK');
}

下記のページを参考にプロパティを設定します。

VERIFY_TOKEN：任意の文字列（あとでスクリプトに設定する）
SHEET_ID:スプレッドシートのID
スプレッドシートのURLから取得できます。
下記のようなURLの場合は
https://docs.google.com/spreadsheets/d/abc1234567/edit#gid=0
abc1234567がIDになります。
SHEET_NAME:シート名（スプレッドシートの名前ではなく、シート自体の名前）

ウェブアプリのデプロイ

AppScriptのページの右上の デプロイ ボタンからデプロイします。
新しいデプロイ から ウェブアプリ を選択して実行します。
表示されたURLはコピーしておきます。

スプレッドシートの準備

スプレッドシートに1列めの時間の行だけ入力が必要です。
できたものを参考に入力しておいてください。

スクリプトの修正

スクリプトを下記のように修正しました。
ログ出力とGASへのPOSTができるようになっています。

test.sh

#!/bin/bash
########################################
# スピードテスト用スクリプト
#
#
########################################

# ログ出力先ディレクトリ
OUTPUT_DIR=~/logs/
# ログ出力先ファイル
OUTPUT_NAME=speedtest.log
# GASで公開したスクリプトのURL
URL=**コピーしたURL**
# 認証用の文字列(GASのVERIFY_TOKENと合わせる)
TOKEN=**GASで設定したVERIFY_TOKEN**
# スピードテストコマンド
command="/opt/anaconda3/bin/speedtest --simple"

########################################

echo "Start speed test!"

NEXT_WAIT_TIME=0
until RET=`$command` || [ $NEXT_WAIT_TIME -eq 3 ]; do
  # リトライ回数×60秒後にリトライ
  sleep $(( (NEXT_WAIT_TIME++) * 60 ))
done

echo $RET

# ログ出力ディレクトリが存在しなければ作成
if [ ! -d $OUTPUT_DIR ]; then
  echo "create log directory."
  mkdir $OUTPUT_DIR
fi

# ログ出力ファイルが存在しなければ作成
if [ ! -f $OUTPUT_DIR$OUTPUT_NAME ] ;then
  echo "create log file."
  touch "$OUTPUT_DIR$OUTPUT_NAME"
fi

# 下記形式でログ出力
# 2021/03/22 00:39:35 Ping: 13.054 ms Download: 88.29 Mbit/s Upload: 63.94 Mbit/s
echo `date "+%Y/%m/%d %H:%M:%S"` $RET >> "$OUTPUT_DIR$OUTPUT_NAME"

# 日付取得
DATE=`date "+%Y-%m-%d"`
# 時刻取得
HOUR=`date "+%-H"`

# ダウンロード速度
DOWNLOAD_SPEED=`echo $RET | awk '{print $5}'`
# アップロード速度
UPLOAD_SPEED=`echo $RET | awk '{print $8}'`

# スプレッドシートに出力
curl "$URL" -v -d'{
  "token":"'$TOKEN'",
  "data" :{
    "date":"'$DATE'",
    "hour":'$HOUR',
    "speed":"'$DOWNLOAD_SPEED'"
  }
}' -H "Content-Type:application/json" -X POST

echo "End speed test!"

実行結果

スクリプトを実行してみると ~/logs/speedtest.log にログが出力され、
スプレッドシートには日付とネットワーク速度が出力されていると思います。

定期実行

crontabで1時間ごとに実行されるように設定します。
まずはcronファイルを作成します。
ここで設定しているスクリプトの場所は実際にスクリプトを保存している場所に変更してください。

speedtest_cron.conf

0 * * * * 「スクリプトのあるディレクトリ」/test.sh

下記を実行して0分になる度にスクリプトが実行されるようにします。

$ crontab speedtest_cron.conf 

下記のように設定されていれば完了です。

$ crontab -l
0 * * * * 「スクリプトのあるディレクトリ」/test.sh

最後に

1時間ごとに速度計測をしてスプレッドシートに出力できるようになりました。

参考