ネットワーク経由でiPadの台数点検
GIGAスクール構想で、私の勤務校にはiPadが304台配備されました。
たくさんのiPadが入ってきて、大変嬉しい限りですが、教育委員会より月に1回台数の点検を行うようにという指示も同時に受けました。（中々面倒です）
クラスにお願いすればいいと思われるかもしれませんが、機器が増えたら仕事も増えたと思われたくありません。
何とか最小限の労力でかつ正確に台数点検できないかと考え、思いついたのがネットワーク経由での台数点検です。
① 同じネットワークにつながっている機器をすべて検出する。＝MACアドレスも検出される
※MACアドレス：ネットワークにつないで使用する機器には全て割り振られている、重複しない固有のアドレス。
② 検出結果をcsvで保存する。
③ 教育委員会からもらったiPadの一覧表と照らし合わせる。（一覧表にはiPadのMACアドレスが記載されている）
※照らし合わせる部分をGoogleスプレッドシートにGASを使って自動化
動作イメージ編（実際の点検の様子）
システム作成編（スプレッドシートで照合作業を行う部分を作成）
GASのソースコード
// メニューの追加
function onOpen() {
SpreadsheetApp.getUi()
.createMenu('追加メニュー')
.addItem('CSVアップロード', 'csvDialog')
.addItem('照合作業開始','assColumn')
.addItem('照合結果保存','saveCSVmain')
.addToUi();
}
// csvファイルアップロードダイアログを表示
function csvDialog() {
// 各シートのデータをクリアする
const ss = SpreadsheetApp.getActiveSpreadsheet();
sheet1 = ss.getSheetByName('検出端末');
sheet2 = ss.getSheetByName('登録端末');
sheet3 = ss.getSheetByName('操作シート');
// 各シート最終行を取得
const lastRow = sheet1.getLastRow();
const lastColumn = sheet1.getLastColumn();
const lastRow2 = sheet2.getLastRow();
const lastRow3 = sheet3.getLastRow();
if(lastRow != 0 ){
sheet1.getRange(1,1,lastRow,lastColumn).clear();
}
sheet2.getRange(5,6,lastRow2,6).clear();
sheet3.getRange(15,1,lastRow3,5).clear();
sheet3.getRange(13,2).clear();
sheet3.getRange(13,4).clear();
// csvファイルアップロードダイアログを呼び出す
var html = HtmlService.createHtmlOutputFromFile('index');
SpreadsheetApp.getUi().showModalDialog(html, 'CSVアップロード');
}
// アップロード処理
function uploadProcess(formObject) {
var formBlob = formObject.myFile;
var csvText = formBlob.getDataAsString('shift-jis');
var values = Utilities.parseCsv(csvText);
const upCsv = SpreadsheetApp.getActiveSpreadsheet();
recSheet = upCsv.getSheetByName('検出端末');
recSheet.getRange(1, 1, values.length, values[0].length).setValues(values);
Browser.msgBox('検出端末タブを開いて、MACアドレスが何列目にあるか数えてから、\\n［② 照合作業開始］ボタンをクリックしてください。');
}
// アップロードcsvファイルで、Macアドレスの列番号を指定するダイアログを表示
function assColumn(){
var columnStr = Browser.inputBox("検出端末タブのMACアドレスが入っている列番号を入力してください。\\n 例えば、M列であれば13です。");
if(columnStr == "cancel"){
return;
} else {
// 入力された列番号を数値に変換し、Macアドレス小文字変換へ！
var columnNum = Number(columnStr);
Browser.msgBox('照合作業を開始します。\\n 時間がかかります。OKをクリックして続行してください。');
lowRewrite(columnNum);
}
}
// Macアドレス小文字変換
function lowRewrite(columnNum){
const ss = SpreadsheetApp.getActiveSpreadsheet();
csvSheet = ss.getSheetByName('検出端末');
const lastRow = csvSheet.getLastRow();
for(var k = 2; k <= lastRow; k++){
console.log(columnNum);
var csvCell = csvSheet.getRange(k, columnNum).getValue();
var lowerStr = csvCell.toLowerCase();
csvSheet.getRange(k, columnNum+1).setValue(lowerStr);
}
// そのまま照合作業へ！
macAddressMatching(columnNum);
}
// 照合
function macAddressMatching(columnNum){
const ss = SpreadsheetApp.getActiveSpreadsheet();
sheet1 = ss.getSheetByName('検出端末');
sheet2 = ss.getSheetByName('登録端末');
sheet3 = ss.getSheetByName('操作シート');
// 最終行を取得
const lastRow = sheet1.getLastRow();
const lastRow2 = sheet2.getLastRow();
var l = 15; // 見つからなかった端末リストを作る際の開始行番号
// 今日の日付
var date = new Date();
sheet3.getRange(13,2).setValue(Utilities.formatDate( date, 'Asia/Tokyo', 'yyyy/MM/dd HH:mm:ss'));
// 照合作業
for (var j = 5; j <= lastRow2; j++){
var s2Cell = sheet2.getRange(j,4); // 登録端末のMacアドレス
var s2Val = s2Cell.getValue();
for (var i = 2; i <= lastRow; i++){
var s1Cell = sheet1.getRange(i,columnNum+1); // 検出端末のMacアドレス
var s1Val = s1Cell.getValue();
var s1State = sheet1.getRange(i,1);
var s1State = s1State.getValue();
// 検出端末のMACアドレスと登録端末のMACアドレスが一致したら
if ( s1Val == s2Val && s1State == 'On'){
sheet2.getRange(j,6).setValue('OK');
break;
}
}
// 検出端末の中に登録端末が無かったら
if ( sheet2.getRange(j,6).isBlank()){
// 操作シートにリスト化していく
var s2No = sheet2.getRange(j,1).getValue();
var s2Name = sheet2.getRange(j,2).getValue();
var s2Sirial = sheet2.getRange(j,3).getValue();
var s2Location = sheet2.getRange(j,5).getValue();
sheet3.getRange(l,1).setValue(s2No);
sheet3.getRange(l,2).setValue(s2Name);
sheet3.getRange(l,3).setValue(s2Sirial);
sheet3.getRange(l,4).setValue(s2Val);
sheet3.getRange(l,5).setValue(s2Location);
l++;
}
}
sheet3.getRange(13,4).setValue(l-15); // 見つからなかった台数
Browser.msgBox('照合作業が終わりました。');
}
// 照合結果保存
function saveCSVmain() {
// dialog.html をもとにHTMLファイルを生成
// evaluate() は dialog.html 内の GAS を実行するため（ <?= => の箇所）
var html = HtmlService.createTemplateFromFile("csvdialog").evaluate();
// 上記HTMLファイルをダイアログ出力
SpreadsheetApp.getUi().showModalDialog(html, "CSVファイルダウンロード");
}
function saveCSV() {
// スプレッドシート上の値を二次元配列の形で取得
const ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet3 = ss.getSheetByName('操作シート');
var lastRow = sheet3.getLastRow();
var values = sheet3.getRange(13,1, lastRow,5).getValues();
// 二次元配列をCSV形式のテキストデータに変換
var dataArray = [];
for (var i = 0; i < values.length; i++) {
dataArray.push(values[i].join(","));
}
return dataArray.join("\r\n"); // 改行コードは windows を想定
}
// CSVファイル名の設定はここで！！
function getFileName() {
var now = new Date();
var datetime = Utilities.formatDate( now, 'Asia/Tokyo', 'yyyy/MM/dd/HH:mm:ss');
// アクティブシート名+現在日時
return 'iPad台数照合結果' + '(' + datetime + ')' + '.csv';
}
index.html
<!DOCTYPE html>
<html>
<head>
<base target="_top">
<script>
// アップロードボタンが押された時の処理
function csvUpload(formObject) {
google.script.run.withSuccessHandler(closeDialog).uploadProcess(formObject);
document.getElementById('myForm').style.display = 'none'; // フォームを非表示
document.getElementById('message').innerHTML = '処理中...'; // 処理中の文字を表示
}
// 処理が終わった時にダイアログを閉じる
function closeDialog() {
google.script.host.close();
}
</script>
</head>
<body>
<form id='myForm'>
<input name="myFile" type="file" />
<button onclick="csvUpload(this.parentNode);">アップロード</button>
</form>
<div id='message'></div>
</body>
</html>
csvdialog.html
<!DOCTYPE html>
<html>
<head>
<base target="_top">
<script type='text/javascript'>
function handleDownload() {
var content = <?= saveCSV(); ?>; // 出力データを GAS から取得する
var blob = new Blob([ content ], { "type" : "text/csv"});
document.getElementById("download").href = window.URL.createObjectURL(blob);
}
</script>
</head>
<body>
<a id="download" href="#" download=<?= getFileName(); ?> onclick="handleDownload()">ダウンロード</a>
</body>
</html>