2018年01月07日

UWSC HTTPリクエストでGoogle Apps Scriptと通信してGoogleスプレッドシートで管理

Spreadsheet.png

 クライアント企業のサーバやユーザーのPCなどの保守業務を行っています。
 この管理ですが、全部合わせるとかなりの数になります。

 ひとつひとつリモートで管理していては、大した内容ではなくても量としては膨大になります。
 クライアント企業によって管理の項目は違いますが、管理対象のツールから自動でデータが送られるようにしています。

 CPUの負荷やバックアップ処理・空き容量・リモートのログなどを、定期的に送信するツールを開発してGoogleのスプレッドシートにて一元管理できるようにしました。


 従来は、CGI・PHPなどを利用してWebサーバに送信するなどの方法を利用していました。しかし、Webサーバですとセキュリティ面で弱い部分があります。
 また、蓄積されたデータの確認やレポートの作成で、データをダウンロードしてExcelを利用して集計・解析を行うのも負担が大きくなります。

 そこで、Googleスプレッドシートに自動集計される仕組みを作りました。
 毎月のレポートもGoogleスプレッドシートで作成しているので、データもスプレッドシートにあれば作業の工数も少なくできます。


 Google Apps Scriptにて、GETメソッドで送られたデータをスプレッドシートに記録するスクリプトを作りました。



function doGet(e) {
//回答とタイムスタンプをスプレッドシートに書き込む
var timestamp = new Date(); //タイムスタンプ
var bar = e.parameter.bar
var dat = e.parameter.dat
var ss = SpreadsheetApp.openById("XXXXXXXXXXXXXX");
var sheet = ss.getSheetByName("シート名");

//formデータを取り出して、配列に格納
var array = [timestamp,bar,dat];

//一発で最終行に書込みする
sheet.appendRow(array);

var result = 'OK';
return ContentService.createTextOutput(result);
}



 この、Google Apps Scriptを「ウェブアプリケーションとして導入」で設定します。
 設定が済むと、URLが発行されるので、このURLにパラメータを付けてアクセスします。

URL https://script.google.com/macros/s/XXXXXXXX/exec

 パラメータは、スクリプトで設定した、barとdatです。

https://script.google.com/macros/s/XXXXXXXX/exec?bar=値1&dat=値2

 このように、URLにつなげて送ります。

 ツール側ですが、CGIやPHPの場合ではHTTPリクエストのGetで送ります。



xmlhttp = CreateOLEObj("Microsoft.XMLHTTP")
COM_ERR_IGN
xmlhttp.open("GET", URL , "False");
xmlhttp.setRequestHeader("Pragma", "no-cache");
xmlhttp.setRequestHeader("Cache-Control", "no-cache");
xmlhttp.setRequestHeader("If-Modified-Since", "Thu, 01 Jun 1970 00:00:00 GMT");
xmlhttp.send(null);
repeat
wks = TRIM( xmlhttp.responseText )
sleep(0.1)
until xmlhttp.readyState = 4 and xmlhttp.status = 200
COM_ERR_RET




 こんな感じですね。
 CGI・PHPならこれで全く問題ないのですが、Google Apps Scriptだとレスポンステキストが空になるのと、ステータスコード「200:サーバからのデータ受信が正常に完了」が返ってきません。
 いろいろ調べてみたのですが、Google Apps ScriptでJSONやJSONPにてレスポンスを返すといいという情報があったのですがうまく行きませんでした。

 そこで、URLDownloadToFile APIを利用してみたところうまくいきました。
 URLDownloadToFile APIは、TEXTや画像などのファイルをダウンロードするためのものです。

 Google Apps Scriptのreturnで指定した内容をTEXTとして取得することができます。




ダウンロード(DownLordFilePath , SaveFileName)


//////////////////////////////////////////////////////////////////////////////////////////
procedure ダウンロード(DownLordFilePath , SaveFileName)
Repeat
Sleep(0.2)
結果 = URLDownloadToFileA(0, DownLordFilePath, SaveFileName, 0, 0)
Until 結果 = 0        // 式が False の間実行
fend

//////////////////////////////////////////////////////////////////////////////////////////
// 初期設定 (呼び出しが必要の無い宣言のみ列挙)
procedure Initialization()
DEF_DLL URLDownloadToFileA(Long,string,string,Long,Long):Long:urlmon
fend




 DownLordFilePathにパラメータを付けたURLを指定します。
 SaveFileNameにスクリプトが返す内容を格納するテキストファイル名を指定します。

 これを実行すると、テキストファイルにはサンプルで返している「OK」が入ります。


 スプレッドシートには、タイムスタンプとパラメータで指定したbarとdatの値が入ります。

 ツール側もGoogle Apps Script側もスッキリとしたスクリプトになりました。

 パラメータの値は、全角を含む文字列をそのまま送るとスプレッドシートで文字化けすることがあります。
 文字コードをUTF-8にして、URLエンコードして送ると文字化けしません。


 Google Apps Script側を工夫すると、パラメーターでキーワードを送って、スプレッドシートで検索した結果を返すということもできます。

 前の記事、「シンプルな動態管理システムをAndroidアプリとGoogleサービスで開発」でもこの仕組を使っています。
 Androidアプリでの目的地の緯度経度取得、PCツールでの作業状態取得に使用しています。

 今回の記事は、PCからクラウド上のスプレッドシートに情報を送るという形ですが逆も可能です。
 例えばスプレッドシートに、実施時間と行わせたい処理を入力すれば、PCで実行させるという使い方もできます。これなら、予め再起動やウイルス対策ソフトのフルスキャンなどの処理をスケジューリングできます。もちろん処理結果もスプレッドシートに反映できます。


 スプレッドシートに情報の蓄積ができれば、別のGoogle Apps Scriptで一定時間ごとに解析処理を走らせるなんてことも可能になります。
 この仕組があれば、Google Driveのスプレッドシートを確認するだけで問題が発生していないか、メンテナンスが必要な端末がないかなどが容易に確認できます。
 もしエラーや早急に対応する必要がある場合は、メールで通知することも可能です。

 トラブルが起きてから対応するのではなく、事前に問題が起こらないように対処ができますし、万が一のときにもすぐに気付けるので素早い対応が可能です。


 何もしなくても、ログやデータの解析が行われているので、月末のレポート作成も数分で完成します。

 従来の方法では、データの取得・解析を行ってレポートを作るまで少なくとも2時間はかかっていたので、おおよそ1/20になりました。

※開発やこんなことができないかなというご相談があればお気軽にどうぞ。
posted by Outliner 伊藤 崇 at 17:28| 効率化・仕組み