AppSheetで安否確認アプリを作ろう【GAS】
小さい組織などで地震の際の安否確認をしたいけれど、その為のツールを持っていない、もしくは支払う余裕がないからスプシのみで運用といったようなケースがあります。しかし、手動で管理するのは大変ですし、仮にも全国に社員が散らばってる場合、誰に直接的な影響が出ているのか？といったようなBCP対策面でも決して良いとは思えません。
そこで、オープンなAPIとして公開されてる地震情報を取得したら、自動的にユーザに通知して、安否確認を入れてもらい、震源や震度、誰に直接的に影響が及んでいるかを一意に確認できるAppSheetのアプリを作ってみようと思います。
このツールはGASで制御が必要です。
目次
今回利用するファイル等
- 安否確認 - AppSheetサンプル
- 安否確認 - Google Spreadsheet
- 動作サンプル - 書き込みは出来ません
- 安否確認用のスクリプト
- P2P地震情報API
今回利用するAPIは特にAPIキーなども取得が不要で、商用利用が可能なAPIとなっています。これを5分毎に監視し、震度5弱以上の地震があった場合には、自動で登録社員分の安否確認データを生成し、通知し入力してもらいます。14日で震源や震度データは自動削除される仕組みも導入しています。
ユーザは自分の画面には自分用の安否確認入力データが出てくるので、選択して送信するだけの簡単仕様です。
図：安否登録の画面
図：震源地と震度ポイント
セットアップ
本アプリはサンプルのダミーデータが入っているので、スプシから全部消去してから使うと良いと思います。テンプレをコピーするとAppSheetのアプリ本体とスプレッドシートはコピーされますが、スクリプトファイルはコピーされないので以下の手順で作成が必要です。以下は、テンプレからコピーした直後からの手順になります。
最後にアプリをデプロイして終了になります。
アプリのドメイン内共有
この段階ではまだ自分以外誰もこのプロジェクトにアクセスすることが出来ません。そこで以下の2パターンについてアプリを共有しておきます。
-
他の開発者を編集者として追加
-
他のメンバーや管理部門の人は、ドメイン全体でユーザとして追加
共有設定の追加方法は以下のとおりです。
-
開発画面上部の右側にある「Share」というボタンをクリック
-
ダイアログが出てきたら以下の2パターンを追加する
-
自社のドメイン（hogehoge.comなど）を入力する（これでドメイン全体にユーザとして共有される：use appでOK）
-
他の開発者のアドレスを入れて、Edit Difinitionを選択。
-
これらは同時に追加が出来ないので、個別に追加する。
-
-
私はロボットではありませんというreCaptureのチェックを入れて突破する
-
Shareをクリックする
-
再度共有画面を出して、ダイアログ右下のCopy Shareing linksをクリックする
-
Browser LinkのURLをコピーしておく（これは後の工程で利用します）
これで対象者にそれぞれの権限で共有がなされました。他の開発者の場合は通知も送られてると思います。
図：組織内全員で共有する
図：Browser Linkは後で使います
単体スクリプトを作成する
まずはAppSheetで利用するスタンドアローンGASを準備してコードを記述しておきます。
- Google Driveを開いて、左上の新規をクリック
- その他 => Google Apps Scriptをクリック
- スクリプトエディタが直接開いて警告文が出るのでそのまま作成を続行する
- ドライブにはスタンドアローンファイルが生成される
見つからない場合には、Chrome Webstoreから拡張機能インストールしましょう（Google Apps Scriptで検索すると出てくる）。
図：新規から作成することが出来る
コードを入力する
main.gs
スタンドアローンスクリプトが生成できたら、以下のコードをコピペし、一度、check関数を実行して承認をしておきましょう。コードの中身の解説については後述の作り方の中で記述します。
//書き込み先スプシのID
var ssid = "書き込み先スプシのIDを入力する";
//バックアップ先フォルダID
var folderid = "バックアップ先のフォルダのIDを入力する";
//APIエンドポイント
var endpoint = "https://api.p2pquake.net/v2/jma/quake";
//応答最低震度
var minScale = 45 //震度5弱以上
//一斉通知先アドレス
var notifmail = "一斉通知先のグループアドレスもしくはSlackチャンネルのメアドを入れる"
//AppsheetアプリのURLを入れる
var urlscheme = "Browser LinkのアプリのURLを入れる";
//通知メッセージ
var msg = "【安否確認】<br>"
+ "大きな地震がありました。安否をお知らせください。非該当地域でも送信をお願いいたします。<br><br>"
+ "以下のリンク先より、安否情報を送ってください。<br><br>"
+ "<div><a href='" + urlscheme + "' target='_blank' class='button'>安否確認アプリを起動</a></div>"
+ "<br><br>"
//P２P地震速報APIを取りに行く
function getEarthQuakePoint() {
//最終地震速報IDを取得しておく
let prop = PropertiesService.getScriptProperties();
let quakeid = prop.getProperty("quakeid");
let earthtemp = parseInt(prop.getProperty("earthtemp"));
let uuid = parseInt(prop.getProperty("uuid"));
//nullチェック
if(earthtemp == null || earthtemp == undefined || earthtemp == ""){
earthtemp = 1
prop.setProperty("earthtemp",earthtemp);
}
if(uuid == null || uuid == undefined || uuid == ""){
uuid = 1
}
//スプシを取得
let master = SpreadsheetApp.openById(ssid);
//一覧を取得する
let users = master.getSheetByName("user").getRange("A2:G").getValues();
let anpi = master.getSheetByName("anpi");
//地震情報を取得する（最新10件）
let quake = getQuakeData()
//地震情報を日時で降順にする
quake.sort(function(a, b) { return new Date(b.time) - new Date(a.time); });
//地震情報書き込み用配列
let array = [];
let array2 = [];
let array3 = [];
let array4 = [];
let temptime = 0;
//地震情報を処理する
let tempquake;
for (let i = 0; i < quake.length; i++) {
//1個目のレコードを取得
let rec = quake[i];
tempquake = quake[i];
//最終地震IDと比較して同じならばスルーする
if(rec.id == quakeid){
break;
}else{
//おかしなデータをフィルタする
//マグニチュードが-1のものはスルーする
if(rec.earthquake.hypocenter.magnitude == -1){
continue;
}
//震度が5以上の場合だけ処理する
if(rec.earthquake.maxScale >= minScale && rec.earthquake.hypocenter.magnitude !== -1 && temptime !== rec.earthquake.time){
//緯度経度情報を取得する
let latlon = rec.earthquake.hypocenter.latitude + "," + rec.earthquake.hypocenter.longitude
//書き込み一時配列に追加する
temparr = [
rec.id,
rec.earthquake.time,
rec.earthquake.hypocenter.name,
changeScale(rec.earthquake.maxScale),
rec.earthquake.hypocenter.magnitude,
rec.earthquake.hypocenter.depth,
latlon
];
//ユーザシートへ追加する
let tempuser = [
"'" + earthtemp,
"震源地",
"",
rec.earthquake.hypocenter.name,
latlon,
"地震",
rec.earthquake.time
]
//earthtempのカウントアップ
earthtemp = parseInt(earthtemp) + 1
//訂正対応の為、日付を取得しておく
temptime = rec.earthquake.time;
//震度ポイントをマップする
let points = rec.points;
for(z = 0;z<points.length;z++){
//ポイントデータを取得する
let point = points[z];
//震度5以上を対象とする
if(point.scale == "" || point.scale == null){
//処理しないでスルーする
}else{
if(point.scale >= minScale){
//一時配列を構成する
let temppoint = [
"'" + earthtemp,
changeScale(point.scale), //表記を震度にする
"",
point.addr,
getlatlon(point.addr),
"ポイント",
rec.earthquake.time
]
//書き込み用配列に追加する
array4.push(temppoint)
//earthtempをカウントアップ
earthtemp = parseInt(earthtemp) + 1
}
}
}
//安否確認データを生成する
for(let j = 0;j<users.length;j++){
//レコードを取得
let urec = users[j];
//タイプがユーザかどうか？
if(urec[5] == "ユーザ"){
//安否確認データを生成
let tempuserarr = [
"'" + uuid,
rec.id,
rec.earthquake.time,
rec.earthquake.hypocenter.name,
urec[2],
"",
""
]
//書き込み用配列に追加
array3.push(tempuserarr);
//uuidを更新
uuid = parseInt(uuid) + 1;
prop.setProperty("uuid",uuid)
}
}
//書き込み用配列に追加する
array.push(temparr);
array2.push(tempuser);
//処理を終了する
break;
}
}
}
//地震IDを格納する
let lastquake = quake[0].id
prop.setProperty("quakeid",lastquake);
//配列の中身が空っぽならば書き込みはスルー
if(array.length == 0){
return false;
}else{
//スプシに書き込む
let ss = master.getSheetByName("earthquake");
//シートのラストに一括書き出し
let endrow = Number(ss.getLastRow()) + 1;
let lastColumn = array[0].length; //カラムの数を取得する
let lastRow = array.length; //行の数を取得する
ss.getRange(endrow,1,lastRow,lastColumn).setValues(array);
//point追加する
let ss2 = master.getSheetByName("point");
endrow = Number(ss2.getLastRow()) + 1;
lastColumn = array2[0].length; //カラムの数を取得する
lastRow = array2.length; //行の数を取得する
ss2.getRange(endrow,1,lastRow,lastColumn).setValues(array2);
//震度ポイントも追加する
let ss5 = master.getSheetByName("point");
endrow = Number(ss5.getLastRow()) + 1;
lastColumn = array4[0].length; //カラムの数を取得する
lastRow = array4.length; //行の数を取得する
ss5.getRange(endrow,1,lastRow,lastColumn).setValues(array4);
//anpiシートに追加する
let ss3 = master.getSheetByName("anpi");
if(array3.length == 0){
//何もしないでスルーする
}else{
endrow = Number(ss3.getLastRow()) + 1;
lastColumn = array3[0].length; //カラムの数を取得する
lastRow = array3.length; //行の数を取得する
ss3.getRange(endrow,1,lastRow,lastColumn).setValues(array3);
}
//earthtempにも格納する
let earthnext = Number(earthtemp) + 1;
prop.setProperty("earthtemp",earthnext);
//通知を送る
//HTMLサービスでCSSを取得
let css = HtmlService.createHtmlOutputFromFile('css')
.setSandboxMode(HtmlService.SandboxMode.IFRAME).getContent();
//ヘッダー部分
let formbody = "";
let csshead = "<head><style>";
let cssfoot = "</style></head>";
//冒頭の文章を追加する
let body = msg;
//ヘッダーを追加
body = body + "<body><table class='type08'><thead><tr>"
+ "<th>項目名</th>"
+ "<th>値</th></tr></thead><tbody>";
//項目を構築
body = body + "<tr><th scope='row'><b>発生日時</b></th><td>" + tempquake.earthquake.time + "</td></tr>"
body = body + "<tr><th scope='row'><b>震源地</b></th><td>" + tempquake.earthquake.hypocenter.name + "</td></tr>"
body = body + "<tr><th scope='row'><b>マグニチュード</b></th><td>" + tempquake.earthquake.hypocenter.magnitude + "</td></tr>"
body = body + "<tr><th scope='row'><b>震度</b></th><td>" + changeScale(tempquake.earthquake.maxScale) + "</td></tr>"
//body = body + "<tr><th scope='row'><b>震源の深さ</b></th><td>" + tempquake.earthquake.hypocenter.depth + "</td></tr>"
//テーブルを閉じる
body = body + "</tbody></table><body>";
//CSSでBodyを装飾
formbody = csshead + css + cssfoot + body;
//メールを送信する
MailApp.sendEmail({
to: notifmail,
subject: '緊急地震速報',
htmlBody: formbody,
noReply: true
});
}
//処理完了
return true;
}
//緯度経度を取得する
function getlatlon(spotname) {
//ジオコーディングする
let res = Maps.newGeocoder().setLanguage('ja').geocode(spotname);
let result = res.results[0];
//結果から緯度経度を取得する
let ret = result.geometry.location.lat + "," + result.geometry.location.lng;
//緯度経度情報を返す
return ret
}
//スケールを震度に変換
function changeScale(scale){
//46という変な数値のときがあるので調整する
if(scale == 46){
scale = 45
}
let scaledata = {
10: "震度1",
20: "震度2",
30: "震度3",
40: "震度4",
45: "震度5弱",
50: "震度5強",
55: "震度6弱",
60: "震度6強",
70: "震度7"
}
//一致するスケールを見つける
let tempscale = scaledata[scale];
//スケール情報を返す
return tempscale;
}
//地震情報を取得する本体の関数
function getQuakeData(){
//最新20件を取得する
var response = UrlFetchApp.fetch(endpoint + '?limit=20');
var ret = JSON.parse(response.getContentText());
return ret;
}
//デバッグ用
function check(){
let quake = getQuakeData()
quake.sort(function(a, b) { return new Date(b.time) - new Date(a.time); });
for (let i = 0; i < quake.length; i++) {
//1個目のレコードを取得
let rec = quake[i];
//最終地震IDと比較して同じならばスルーする
//震度が5以上の場合だけ処理する
if(rec.earthquake.maxScale >= minScale && rec.earthquake.hypocenter.magnitude !== -1){
console.log(rec)
}
}
}
//対象のユーザの住所を緯度経度に変換する
function convertUserAddress(id,address){
//スプシを取得する
let ss = SpreadsheetApp.openById(ssid).getSheetByName("user");
let data = ss.getRange("A2:G").getValues();
//ユーザのIDと一致する場所を特定
for(let i = 0;i<data.length;i++){
//レコードを一個取り出す
let rec = data[i];
//IDが一致するか？
if(rec[0] == id){
//ユーザの住所を緯度経度に変換
let latlon = getlatlon(address);
//書き込み先
let edit = i + 2;
let target = "E" + edit;
//書き込みする
ss.getRange(target).setValue(latlon);
//処理終了で抜ける
break;
}
}
//終了処理
return true;
}
//日付が1週間を超えてる地震情報は削除する
function deleteQuakeInfo(){
//スプシを取得する
let ss = SpreadsheetApp.openById(ssid).getSheetByName("point");
let sheet = ss.getRange("A2:G").getValues();
//最終行を取得する
let finalrow = ss.getLastRow();
//条件に一致するデータを削除する
for (let i = finalrow; i >= 2; i--) {
//レコードを取得する
let rec = sheet[i - 2];
//地震データかどうか？
//日付を取得する
let tempday = rec[6];
//日付の差を取る
let date1 = new Date(tempday);
let date2 = new Date();
let daydiff = (date2 - date1) / 86400000;
//14日以上だったら削除する
if(daydiff >= 14){
//空行を追加する
let array = ["","","","","","",""];
ss.appendRow(array)
//行を削除する
ss.deleteRow(i);
}
}
}
admin.gs
あとから追加実装した管理者向けの機能用のコードです。訓練送信やバックアップ、手動送信用のコードが入っています。
//スポット実行切り分け
function spotFunction(param){
//応答用変数
let ret;
//paramで実行する関数を切り分け
switch(param){
case "バックアップ":
//バックアップ実行
ret = backupanpi();
break;
case "訓練発信":
//訓練発信を行う
ret = trainingSend();
break;
}
//応答を返す
return ret;
}
//anpiシートを新規スプシにバックアップ
function backupanpi() {
//エラートラップ
try{
//バックアップ先を取得
let target = DriveApp.getFolderById(folderid);
//コピーするファイルを取得
let copyfile = DriveApp.getFileById(ssid);
//ファイル名を生成する
let filename = copyfile.getName() + "-" + makeDate();
//コピー実行
copyfile.makeCopy(filename, target);
//anpiシートのデータをクリアする
let sheet = SpreadsheetApp.openById(ssid).getSheetByName("anpi");
let ss = sheet.getRange("A2:G");
ss.clearContent();
//処理終了
return true;
}catch(e){
return false;
}
}
//ファイル名用の日付を生成する
function makeDate(){
//今日の日付を取得
let now = new Date();
//年月日時刻までを分解して取得
let fullyear = now.getFullYear();
let monthman = paddingZero(now.getMonth() + 1);
let dateman = paddingZero(now.getDate());
let hourman = paddingZero(now.getHours())
let miniteman = paddingZero(now.getMinutes());
let secondman = paddingZero(now.getSeconds());
//文字列結合する
let tempdate = String(fullyear) + String(monthman) + String(dateman) + String(hourman) + String(miniteman) + String(secondman);
console.log(tempdate)
//値を返す
return tempdate;
}
function makeDate2(earthtime){
//今日の日付を取得
let now = new Date(earthtime);
//年月日時刻までを分解して取得
let fullyear = now.getFullYear();
let monthman = paddingZero(now.getMonth() + 1);
let dateman = paddingZero(now.getDate());
let hourman = paddingZero(now.getHours())
let miniteman = paddingZero(now.getMinutes());
let secondman = paddingZero(now.getSeconds());
//文字列結合する
let tempdate = String(fullyear) + "/" + String(monthman) + "/" + String(dateman) + " " + String(hourman) + ":" + String(miniteman) + ":" + String(secondman);
console.log(tempdate)
//値を返す
return tempdate;
}
//頭に0をつける
var paddingZero = function(n) {
return (n < 10) ? '0' + n : n;
};
//訓練発信
function trainingSend(){
//uuidを取得しておく
let prop = PropertiesService.getScriptProperties();
let uuid = prop.getProperty("uuid");
let earthtemp = prop.getProperty("earthtemp");
//スプレッドシートを取得
let ss = SpreadsheetApp.openById(ssid)
let point = ss.getSheetByName("point");
let anpi = ss.getSheetByName("anpi");
let tempusr = ss.getSheetByName("user");
let users = tempusr.getRange("A2:G").getValues();
//発生時刻
let earthtime = new Date();
//uuidを生成
let uniqueid = Utilities.getUuid();
//pointに訓練震源地を追記
let temparr = [
earthtemp,
"震源地",
"",
"【訓練】日本海溝",
"34.0713943,150.0183957",
"地震",
earthtime
]
//earthtempのカウントアップ
earthtemp = Number(earthtemp) + 1
prop.setProperty("earthtemp",earthtemp);
//pointに行を追加する
point.appendRow(temparr);
//anpiにユーザ分の安否情報を生成
let array = [];
for(let j = 0;j<users.length;j++){
//レコードを取得
let urec = users[j];
//タイプがユーザかどうか？
if(urec[5] == "ユーザ"){
//安否確認データを生成
let tempuserarr = [
"'" + uuid,
uniqueid,
earthtime,
"【訓練】日本海溝",
urec[2],
"",
""
]
//書き込み用配列に追加
array.push(tempuserarr);
//uuidを更新
uuid = Number(uuid) + 1;
prop.setProperty("uuid",uuid)
}
}
//安否情報を書き込み
try{
endrow = Number(anpi.getLastRow()) + 1;
lastColumn = array[0].length; //カラムの数を取得する
lastRow = array.length; //行の数を取得する
anpi.getRange(endrow,1,lastRow,lastColumn).setValues(array);
//通知を送る
//HTMLサービスでCSSを取得
let css = HtmlService.createHtmlOutputFromFile('css')
.setSandboxMode(HtmlService.SandboxMode.IFRAME).getContent();
//ヘッダー部分
let formbody = "";
let csshead = "<head><style>";
let cssfoot = "</style></head>";
//冒頭の文章を追加する
let body = "【安否確認：訓練】<br>"
+ "大きな地震がありました。安否をお知らせください。非該当地域でも送信をお願いいたします。<br><br>"
+ "以下のリンク先より、安否情報を送ってください。<br><br>"
+ "<div><a href='" + urlscheme + "' target='_blank' class='button'>安否確認アプリを起動</a></div>"
+ "<br><br>"
//ヘッダーを追加
body = body + "<body><table class='type08'><thead><tr>"
+ "<th>項目名</th>"
+ "<th>値</th></tr></thead><tbody>";
//項目を構築
body = body + "<tr><th scope='row'><b>発生日時</b></th><td>" + makeDate2(earthtime) + "</td></tr>"
body = body + "<tr><th scope='row'><b>震源地</b></th><td>" + "【訓練】日本海溝" + "</td></tr>"
body = body + "<tr><th scope='row'><b>マグニチュード</b></th><td>" + "9.0" + "</td></tr>"
body = body + "<tr><th scope='row'><b>震度</b></th><td>" + "震度6強" + "</td></tr>"
body = body + "<tr><th scope='row'><b>震源の深さ</b></th><td>" + "10km" + "</td></tr>"
//テーブルを閉じる
body = body + "</tbody></table><body>";
//CSSでBodyを装飾
formbody = csshead + css + cssfoot + body;
//メールを送信する
MailApp.sendEmail({
to: notifmail,
subject: '【訓練】緊急地震速報',
htmlBody: formbody,
noReply: true
});
//処理を終了する
return true;
}catch(e){
//console.log(e.message)
return false;
}
}
//特定エリアのみ手動で発信する
function manualSend(id,title,msg,area,prefecture){
//uuidを取得しておく
let prop = PropertiesService.getScriptProperties();
let uuid = prop.getProperty("uuid");
//スプレッドシートを取得
let ss = SpreadsheetApp.openById(ssid)
let anpi = ss.getSheetByName("anpi");
let tempusr = ss.getSheetByName("user");
let users = tempusr.getRange("A2:J").getValues();
//発生時刻
let earthtime = new Date();
//anpiにユーザ分の安否情報を生成
let array = [];
let mailarr = [];
for(let j = 0;j<users.length;j++){
//レコードを取得
let urec = users[j];
//安否確認データ作成判定
if(area !== "指定なし"){
if(urec[9] == prefecture){
//メール配列に追加
mailarr.push(urec[2]);
}else{
//該当しないので処理をスルー
continue;
}
}else{
//指定なしなので、全ユーザを追加する
}
//安否データを作成
let tempuserarr = [
"'" + uuid,
id,
earthtime,
title,
urec[2],
"",
""
]
//書き込み用配列に追加
array.push(tempuserarr);
//uuidを更新
uuid = Number(uuid) + 1;
prop.setProperty("uuid",uuid)
}
//作成判定
console.log(array.length)
if(array.length == 0){
//送信先が無いのでエラーで返す
return false;
}
//指定なしの場合とありの場合で宛先分岐
let target;
if(area == "指定なし"){
//全宛先
target = notifmail;
}else{
//宛先をカンマ区切りに変換
target = mailarr.join(',');
}
try{
//anpiデータを書き込み
endrow = Number(anpi.getLastRow()) + 1;
lastColumn = array[0].length; //カラムの数を取得する
lastRow = array.length; //行の数を取得する
anpi.getRange(endrow,1,lastRow,lastColumn).setValues(array);
//通知を送る
//AppSheetアプリのurlschemeを追記
let body = msg + "<br><a href='" + urlscheme + "' target='_blank'>安否確認アプリを起動</a>";
//メールを送信する
MailApp.sendEmail({
to: target,
subject: title,
htmlBody: body,
noReply: true
});
//値を返す
return true;
}catch(e){
//エラーで返す
//return false;
}
}
css.html
通知メールはレスポンシブなキレイに整形されたメールを送るようにしています。そのため、CSSをHTMLファイルで作成しています。CSSのコードはこちらのサイトで生成しました。ボタンを押すと、AppSheetのアプリが起動します。
図：キレイに整形したメール
a {color:#FFFFFF; text-decoration:none;}
.button {
display : inline-block;
border-radius : 5%; /* 角丸 */
font-size : 12pt; /* 文字サイズ */
text-align : center; /* 文字位置 */
cursor : pointer; /* カーソル */
padding : 12px 12px; /* 余白 */
background : #ff4da6; /* 背景色 */
color : #FFFFFF; /* 文字色 */
line-height : 1em; /* 1行の高さ */
transition : .3s; /* なめらか変化 */
box-shadow : 8px 8px 9px #666666; /* 影の設定 */
border : 2px solid #ff4da6; /* 枠の指定 */
}
.button:hover {
box-shadow : none; /* カーソル時の影消去 */
color : #ff4da6; /* 背景色 */
background : #FFFFFF; /* 文字色 */
}
table.type08 {
border-collapse: collapse;
text-align: left;
line-height: 1.5;
border-left: 1px solid #ccc;
}
table.type08 thead th {
padding: 10px;
font-weight: bold;
border-top: 1px solid #ccc;
border-right: 1px solid #ccc;
border-bottom: 2px solid #c00;
background: #dcdcd1;
}
table.type08 tbody th {
width: 150px;
padding: 10px;
font-weight: bold;
vertical-align: top;
border-right: 1px solid #ccc;
border-bottom: 1px solid #ccc;
background: #ececec;
}
table.type08 td {
padding: 10px;
vertical-align: top;
border-right: 1px solid #ccc;
border-bottom: 1px solid #ccc;
}
.mobileHidden { display:none;}
@media screen and (min-width:480px) {
.desktopHidden { display:none;}
.mobileHidden { display:inline;}
}
コード内の値を修正する
前述のコードをコピペしてテスト実行後に、色々とコード内にある内容を修正します。main.gsの冒頭付近に記載されています。
- ssid : テンプレコピー後に一緒にコピーされてる書き込み先のスプシのIDをここに入力する
-
folderid : 管理機能のスプシバックアップで利用します。バックアップ先のフォルダのIDを指定します。
-
minScale : デフォルトは45を指定（50で震度5強となる）。これ以下の震度情報や地震についてはスルーします。
- notifmail : 指定震度以上の地震が発生した場合に、安否確認登録を促すメールを送る先のグループアドレスを指定します（全社員が含まれてるものが望ましい）
- urlscheme : AppSheetをデプロイした時のアプリのURL（Browser Linkがそれになります）。通知メールに記載されます。
- msg : 通知メールで送る冒頭の通知メッセージを記入します。ここに震源地や震度の情報をGASで連結して送信します。
事前に調べておいて入力しましょう。
初回認証の実行
ここで、Google Apps Scriptのコードの初回認証実行をしておきます。
- main.gs上部にあるメニューの実行する関数から「check」を選択する
- 左隣にある「実行」をクリックする
- 認証画面が出るので許可をし、承認する
- check関数が実行されて、画面下部のデバッグ欄にエラーがでなければ成功
図：Check関数で認証をしておく
スクリプトトリガーを設置する
今回のアプリは、以下の2つのスクリプトトリガーを設置する必要があります。
- getEarthQuakePoint関数 : 時間主導型/分ベース/5分で実行のトリガー
- deleteQuakeInfo関数 : 時間主導型/日付ベース/午前0時〜1時のトリガー
getEarthQuakePointが、P2P地震情報APIにアクセスして、最新の地震情報を20件拾ってくるもので、これが発火し指定の震度以上の地震があった場合には、そこから作業が始まります。
deleteQuakeInfoは、記録済みの地震情報やポイント情報の日付を見て、現在時刻よりも14日以上経過してるかどうかを判断し、データの行削除を行う処理を担当しています。消さないといつまでもマップに震源地が掲載され続けることになる。
この２つを手動で設置しましょう。
図：２つ設置する必要があります。
アプリのスクリプト参照を修正する
今回のAppSheetでは1箇所でこれらのスクリプトを参照するシーンがあります。
- 社員データ追加時に、住まいの住所データから緯度経度に変換する時。緯度経度に変換しないとマップに出てきません。
以下の手順で直します。
- AppSheetのアプリの開発画面を開く
- 左サイドバーのAutomationを開き、userの中にあるuseraddというBotを開きます。
- プロセスの中にある「convert」を開くと、右にApps Script Projectが出てきます。
- ファイル参照のアイコンをクリックして、自分が用意したスタンドアローンスクリプトを指定し直します。
- Function NameをconvertUserAddressを選択し、IDとAddressの引数にそれぞれ「ID」、「住まい」を指定する
- returnd valueはオンにしておき、返り値指定はBooleanを選択する。
- 右上のSAVEをクリックして保存する。
同様の作業を、手動送信ボット（関数はmanualSend関数を指定）、管理コマンドボット（関数はspotFunction関数を指定）に変更する必要があります。
図：GASファイルの参照を修正する
図：管理コマンドのボットの場合
図：手動送信のボットの場合
スプシに手動でユーザを追加した時
アプリ上からユーザを１名ずつ登録した場合には、住所から緯度経度に変換して追記します。しかし、初回セットアップ時や定期的な大量採用などで１名ずつ入れるのが大変といった時用に、１つスクリプトをスプレッドシートに用意しています。ただAppSheetでコピーするとコードが消えてしまうので手動で装備が必要。
スプレッドシートを開くとメニューに「▶設定」というのが出るので、データを貼り付けたら一括変換を実施してみてください。
- 住まい列の値を元に緯度経度情報に変換します
- 住まい列の値を元に都道府県を取り出し、エリアを特定しそれぞれの列に格納します。
- タイプ列に自動で「ユーザ」を記入します
- ID列が空の場合には、GUIDを生成して記入します。
これらを一括で行います。初回くらいしか使わないかもしれませんが、利用してみてください。時間短縮に繋がります。
//メニューを構築する
function onOpen(e) {
let ui = SpreadsheetApp.getUi();
ui.createMenu('▶設定')
.addItem('一括変換', 'allgeocode')
.addToUi();
}
//ユーザの住所から一括で緯度経度変換して格納する
function allgeocode(){
//スプレッドシートを取得する
let ss = SpreadsheetApp.getActiveSpreadsheet();
let ui = SpreadsheetApp.getUi();
let sheet = ss.getSheetByName("user");
let data = sheet.getRange("A2:J").getValues();
//dataを回して緯度経度変換
for(let i = 0;i<data.length;i++){
//レコードを取得する
let rec = data[i];
//データが空の場合スルーする
if(rec[1] == "" || rec[1] == undefined){
continue;
}
//データを書き換える
if(rec[0] == ""){
//uuidを生成
let uuid = Utilities.getUuid();
//uuidを入力
rec[0] = uuid
}
//住まいから都道府県とエリアを探索
let result = prefecget(rec[3])
//緯度経度とユーザを書き込む
rec[4] = georeturn(rec[3],0);
rec[5] = "ユーザ";
rec[8] = result[1]; //エリア
rec[9] = result[0]; //都道府県格納
}
//データを書き戻す
var lastColumn = data[1].length; //カラムの数を取得する
var lastRow = data.length; //行の数を取得する
sheet.getRange(2,1,lastRow,lastColumn).setValues(data);
//終了メッセージ
ui.alert("一括で住所データを変換しました。")
}
//住所から都道府県だけ抜き出す
function prefecget(word){
//都道府県取得
let ret = word.replace(/^(.{2}[都道府県]|.{3}県)(.+)/, "$1 $2").split(' ')
let prefec = ret[0];
//都道府県リストからエリアを取得
let ss = SpreadsheetApp.getActiveSpreadsheet()
let sheet = ss.getSheetByName("prefecture").getRange("A2:B").getValues();
//探索する
let area;
for(let i = 0;i<sheet.length;i++){
//レコードを一個取り出す
let rec = sheet[i];
//一致するか？
if(prefec == rec[0]){
area = rec[1];
break;
}
}
//答えを返す
return [prefec,area];
}
//ジオコーダー用ラッピング関数
function georeturn(address,option){
//ジオコーダクラスの宣言
var geocoder = Maps.newGeocoder();
//空のアドレスの場合には、スルーする
if(address == ""){
return "";
}
//ジオコーディング実施
var response = geocoder.geocode(address);
//optionによって値を返す
var ret = "";
var result = response.results[0];
switch(option){
case 0: //全部を取得
ret = result.geometry.location.lat + "," + result.geometry.location.lng;
break;
case 1: //緯度だけ取得
ret = result.geometry.location.lat;
break;
case 2: //経度だけ取得
ret = result.geometry.location.lng
break;
default:
ret = "オプションが指定されていません"
break;
}
return ret;
}
アプリをデプロイ
デプロイをしないと他の人もアプリを利用することが出来ません。以下の手順で最後にデプロイを実行しましょう。
- 開発画面左サイドバー一番上のボタンをクリックする
- Deploymen Checkの中のRun Deployment Checkをクリックする
- Move app to deployed stateをクリックする
- エラーがなければこれでBrowser LinkのURLでアクセス可能です。
図：デプロイ作業は最後に行おう
アプリの作成のポイント
タイムゾーン設定
自前で作っていて気がついた点ですが、プロジェクトをコピーして使った場合環境によっては以下の問題点が発生します。
- 訓練送信等で送った場合に、送信時日付時刻とメールに記載される時刻とにズレが生じてる
- 本番でも時刻に数時間のズレが発生してる
原因を探ってみたところ、以下の2点が影響していました。
- スクリプト側タイムゾーンの設定（こちらは自分はTokyoになっていた）
- スプシ側タイムゾーンの設定（こちらがGMT -8:00 Pasific timeになぜかなっていた）
GASで日付生成や調整をしている場合、特にスプシ側のタイムゾーン設定がTokyoになっていない場合、影響を受けるのでプロジェクト作成開始前にタイムゾーン設定がTokyoになっているかどうかをよく確認しましょう。
※自分の環境でなぜ起きたのか？おそらく理由はGemini for Google Workspaceで英語UIなどに切り替え等をしていた為。結果、このような設定が残ってしまったままPJを始めてしまった結果だと思われます。
図：スプシ側のタイムゾーン設定
図：GAS側のタイムゾーン設定
スプレッドシート設計
今回のアプリのスプシの設計はトリッキーと言える場所はそう多くはありません。
user, point, earthquakeシートについて
userシートは、ユーザ登録するとここに書き込まれます。緯度経度のみはGASで変換されて追記されます。地震日付列はpointシートと合わせる為に用意してるだけで未使用です。これはmapviewシートの説明で紹介します。タイプは全部「ユーザ」となります。手動でユーザが書き込む場所はこのシートくらいです。ユーザビューの元データになります。
その後の拡張で、ロールとエリアと都道府県を列追加しています。ロールでは管理者を選んでいない人はビューとして社員・管理用・手動送信は表示されないようになっています。また都道府県は手動送信時に特定エリアに対してのみ送信する場合に利用していますので必ず登録が必要です。
pointシートは、震源地および震度情報のポイントデータが書き込まれます。メアド列は使用しません。userシートと合わせる為に列名は同じにしていますが、震度情報をユーザ名列に格納しています。この地震日付を見て14日後に削除されたりします。タイプは「地震」「ポイント」の２つで、地震が震源地を表します。また、緯度経度に於いて分秒がないものが来るケースがあったので、pointシートの緯度経度列については、表示形式を「書式なしテキスト」にしています。
earthquakeシートは、APIで取得した地震情報そのものを記録的に残してるもので、特に直接的にアプリで利用してるものではありません。
図：ポイントシートはユーザ名欄に震度情報を記述
図：管理者のみ表示される機能
図：分秒の無いデータ対策
prefectureシートについて
こちらはユーザ一覧（user）および手動送信する機能(manual)のシートに入力するビューでドロップダウンで利用するもので、都道府県一覧とそれが属するエリアを定義しています。
エリアが定義されてる理由は、ビュー上でエリアのドロップダウンを選択した場合、次の都道府県のドロップダウンは対象エリアに属する都道府県のみを表示するようにフィルタする為に利用しています（二重選択ドロップダウンになります）。
図：二重プルダウンで確実な選択をさせる
anpiシートについて
地震情報が記録されるとGASによって、userシート全員分の安否情報入力欄が生成され、ここにレコードが登録されます。ユーザの安否情報ビューはこのシートを土台にしており、地震のメアドと一致するデータだけが表示されるようになっています。
安否列にユーザの選択した安否が追記され、Botによって同時にステータスがdoneに変更されます。doneが書き込まれてるレコードは安否情報ビューには出てこない仕組みにしてあります。
解答していない人は安否列に何も書かれていないので、誰が提出していないのか？をスプレッドシート上で一意に確認することが出来ます。
図：安否情報の元データ
mapviewシートについて
mapviewはちょっと特殊で、userシートとpointシートをユニオンクエリのように結合し、1枚のシートにしています。こうすることでユーザと震源地情報等が一緒になり、マップビューで表示されます。
数式的には極めてシンプルに以下の数式で結合してるだけ。空白列はAppSheetのマップビューでは無視されるので空白のままで問題ないです。特にユーザもシステムも値の書き換え等はしていません。
={user!A2:G;point!A2:G}
図：ユーザと震源情報をガッチャンコ
manualシート
このシートは管理者機能の１つである「手動送信」で送信履歴を残しておくためのシートです。地震などのように自動で作動するものではなく、台風などで特定の県に住んでいる人に対してだけ、安否確認を送りたい場合に利用します。
登録することでGASが自動的に発動し、ユーザの所属で該当する人に対してだけ、スポットで安否確認を送ることが可能です。
図：手動でスポット送信するための機能です
Adminシート
管理者用の機能を定義してるもので、AppSheetのアプリ用の定義シートですので記録を追加したり管理者が項目を追加することはありません。現在は以下の2つの機能を持っています。
- AppSheetで使ってるスプレッドシートを指定のフォルダにバックアップを行います。（実行後、anpiシートの中身はクリアされます）
- ボタン一発で全員に対して、安否確認の訓練発信を送信することが可能です。
GASを発動させる為に更新フラグに都度値を書き込みさせるために利用しています。ビュー上では項目をタップするだけで発動するように仕掛けをしています。
図：項目タップだけで発動します
APIの仕様
今回GASが常に監視してデータをリクエストする先のP2P地震情報APIでは様々な地震情報を発信しています。一方で次々に最新の地震情報が蓄積されていくわけで、コードを書く上ではちょっとやっかいなポイントもあります。
- リクエストエンドポイントは、リアルタイムなので「https://api.p2pquake.net/v2/jma/quake」を今回は利用しています。
- 震度情報は10〜70の数値で表現され、震度5弱は45という値で表現されます。
- ただし、リクエスト時に一部のポイントデータに置いて、46という数値で来るケースがあり、コードでこれを45に修正しています。
- 返ってくるデータでマグニチュードが-1で返ってくるものが含まれてるのでこれはスルーするようにしています。
- 緯度経度情報は、小数点以下2桁程度のデータなのでざっくりアバウトな場所になっています。
- 震源地情報はearthquakeの項目に入っていますが、付属のpointsデータに各計測拠点の震度情報が入っているので両方を利用しています。
- これらの情報を加工し、pointsやearthquake、anpiシートにデータを生成しています。
- getQuakeData関数がリクエスト本体ですが、URLにlimit=20と入れています。最新20件のデータを要求し、取得済みのデータはGASでスルーするようにしています。10では次々に来る震度情報に埋もれて出てこなくなる可能性があるため（ここがコードを書く上でちょっと大変な場所）。
- 同じ地震のIDのものが重複して含まれてるケースは、発表後に震度情報やマグニチュード情報が訂正されてるものです。これを除外する必要があります。
- 5分間隔のトリガーで取得しにいっていますが、APIの閾値は1分間に10リクエストまでです。
GASの注意点
本アプリのケース
GASで担当してることは前述までにもあるように、わりと明確な仕事だけを担当しています。
- getEarthQuakePoint：APIリクエストして地震データを取得し加工しスプシに書き出すメインの関数
- 処理対象にする地震情報はminScaleの値以上の場合だけに限定しています。
- マグニチュードが-1のデータについても処理をしません。
- earthquakeのIDはそれぞれ固有です。なので降順でソートしておき、この値と事前に取得済みのIDを比較して処理済みならばそこで処理終了とすることで重複してデータを取得するのを防いでいます（lastquake変数に格納して、スクリプトプロパティに格納）
- 同じ地震IDで重複してる「後で情報が訂正されたデータ」は、earthquake.timeの値が同じなのでこれをもって判定し、処理をスルーしています。
- 地震情報の連番は数値なので「'」を加えて文字列にしています。pointsとuserのシートを合体した時に不具合にならないように型を合わせています。
- getlatlon : 住所データを緯度経度に変換する関数
- changeScale : APIから返ってくる震度情報を震度5弱といったような文字列に変換してる関数
- getQuakeData : APIリクエストして地震データを取得してるだけの関数。
- convertUserAddress : AppSheet上でユーザ情報を登録時に住所データを受け取り、緯度経度に変換して追記してる関数
- deleteQuakeInfo : 14日を経過した地震、ポイントデータをpointsシートから削除する関数
- 削除しつづけるとシート上から書き込める行がなくなってしまうので、同時に空行を追加しています。
スクリプトのエラーについて
本スクリプトに於いて、P2P地震情報APIはアクセスが集中していても、タイムアウトとして返してくれません。結果として、GAS側でUrlfetchAppが時間いっぱいまで待機した上でException: Address unavailableとしてエラーが記録されます。5分間隔だと結構記録される場合もあります。
UrlfetchAppは意図的にタイムアウト時間を設定するオプションが存在しないので、こういった症状を緩和したい場合には、Google Cloud Run Functions側でリクエストをするように変更し、setTimeoutで一定時間後に強制的にタイムアウトさせたりAbortSignal.timeout()を使ってタイムアウト時間を指定して作り直すと良いでしょう。
図：タイムアウトが発生した事例
AppSheetでの注意点
ここからが、AppSheetで安否確認アプリを構築する解説です。結構色々ポイントがあるので、そこにフォーカスして解説します。
テーブルデータ
いくつかポイントになる場所があります。
- anpiテーブル：安否はEnum型にしています。既定の選択肢からドロップダウンで選ばせる為です。安否の左側にある鉛筆をクリックしてリストを追加しておいて上げます。
- ステータス欄は書き込み時に自動でdoneとする為、initial valueを入れています。
- スライスとして、以下の数式の処理を入れて、安否確認ビューで表示する土台としています。処理が終わったものや自分の安否確認データをこれで除外しています。
AND([メアド]=USEREMAIL(),[ステータス]<>"done")
- mapviewテーブル：読み取り専用にする為に、マップ専用のスライスを作成し、ReadOnlyとしています。これが地震マップビューの土台となります。
図：安否の選択肢を構築する
図：安否データはスライスしてフィルタ
図：安否選択時の画面
ビューの設定
userビュー
userビューだけがポイントです。ユーザのDeck表示で使う項目として、Primary HeaderとSecondary Headerをそれぞれユーザ名と住まいで指定しています。
マップビュー
ここは沼にハマった場所なのですが、避難所ビューのマップに於いて、ピンを触った時にIDと市町村の表示になっていました。しかし、IDは不要なのでビュー上でshowをオフにしたら出来ず。
理由はこの元になってるテーブルのID列にinitial valueの設定が無いから非表示に出来ないとのこと。なので、initial valueにUNIQUEID()を入力して保存。スプシをテーブルとして追加した時は空っぽなので必ず追加するようにしましょう。
そして、避難所一覧_Detail (system generated)に於いて市町村・施設名の順に出てきました。ここはテーブルの列の登場順になってるようです。Column Orderを変えても上下変わりませんでした。
図：市町村と建物名にしたいのです
安否確認ビュー
一般ユーザが利用する安否確認のビュー。デフォルトのままだと飛んできた確認のレコードをクリックすると、内容を一旦表示してDetail画面がでて、右下のEditをクリックしてから安否を報告するといった、ワンステップ入ってる状態になっています。これではちょっと面倒くさい。
ということで、この挙動を変更しておきます。
- 左サイドバーのViewをクリックしてから安否確認のビューを選択する
- 下の方にあるBehaviorをクリックして、Row SelectedのAuto Assign(View Detail)をクリックする
- 選択肢から「Edit」を選択する
- 右上のSAVEをクリックする
これで直接編集画面が開いてユーザは回答を入れてSAVEするだけでオッケーになりワンステップ飛ばすことが可能になりました。
図：1ステップでも削りたい
フォーマットルール
マップビューに於いて表示するピンの色やアイコンを指定するにはここで規定する必要があります。震源地、ポイント、ユーザの3つにそれぞれの色とアイコンを指定しています。If this condition is trueにて以下のような数式でフィルタするようにしています。
フォーマットルールはビューのアイコンをクリック時に表示されるので注意です。
[タイプ] = "ユーザ"
今回のアプリでは以下の３種類を定義しています。
- 黒いピン：震度情報を表示
- 赤いピン：震源地情報を表示
- 青いピン：ユーザ情報を表示
3つの情報がマッピングされることにより、震源と言うよりも、震度情報により近い位置で働いてる従業員が誰なのか？を把握しやすくなります。特に全国規模の企業や、フルタイムリモートで働いてる企業などに於いて、従業員の安全状態を確認しやすくなります。
※maxScaleを下げればそれだけ震度情報のピンが増えますが、見づらくなるのと震度4程度では心配は無用なので、現在は震度5弱でこのくらいの表示という感じです。
図：ピンのデザインを規定する
図：3種類のピンが表示される
アクション
タスク実行アクション
このアクションでは、管理用から各ボタンを押した場合に、Automationの管理用コマンドをトリガーしてGASを直接発動させるためのアクションをセットしています。実際にはアクションから直接GASをトリガーすることは出来ないので、adminシートの更新フラグにuniqueidを書き込ませてGASを発動させる仕掛けにしています。
- adminテーブルに対してアクションを作成します。
- Do thisは「Data: set the values of some columns in this row」を指定します。
- set these columnsにて、更新フラグ列にUNIQUEID()を入れる設定を記述します。
図：管理用コマンドのBotをこれでトリガーできる
済みにするアクション
このアクションでは、ユーザが安否確認を送信時に対象のレコードのステータス欄に「done」を記述するためのアクションを指定しています。このアクションを後述のAutomationにて指定し、呼び出しています。
- anpiテーブルに対してアクションを作成します。
- Do thisは「Data: set the values of some columns in this row」を指定します。
- set these columnsにて、ステータス列にdoneを入れる設定を記述します。
- Positionはhideにしてボタンとして表示しないようにします。
図：ステータスを変更することでGASが発動します。
Automation
ここでは、アプリで利用してるBotを定義しています。すでに前述紹介済みの「アプリのスクリプト参照を修正する」の内容になります。
- useradd : userの追加や情報更新時に作動。指定のGASで住所データを緯度経度変換し、追記後にnotificationで通知を送ります。
- done : anpiに於いて、安否確認が送られると、同じレコードのステータス欄にdoneを記述追加するアクションを実行しています。追記後にnotificationで通知を送ります。
- 手動送信 : 手動で安否確認を送る場合に利用されます。引数を渡して特定ユーザに対してだけ安否確認を手動で送信します。
- 管理コマンド：バックアップや訓練送信といった管理機能から呼び出されます。項目名でGAS側で判定して実行されます。
管理コマンドだけは更新フラグ列の書き込みを検知させる為に、EventのConditionにて以下の数式を入れて、書き込み前と後で値が変わっているかどうかの判定を入れています。
[_THISROW_BEFORE].[更新フラグ] <> [_THISROW_AFTER].[更新フラグ]
変わっていたらボタンが押されたと判定されてGASの発動が実行される仕掛けです。
図：doneのBotの事例
図：Conditionでボタン押下判定
安否確認入力欄
ここまでの時点で殆どを実装済みですが１箇所、既定のデータが入っていないと設定出来ない箇所があります。それが安否確認入力欄に於いて、「安否確認を送った後にビューに戻る」処理を付ける場所。anpiにダミーデータを生成しておく必要があります。
- 開発画面右側のプレビュー画面に於いて、安否確認を開く
- 来ている安否確認データをクリックし、鉛筆ボタンをクリックして編集画面を出す。
- 安否入力画面の右上あたりにカーソルを移動して、Editボタンをクリックして、Edit Viewをクリックする。
- 安否確認_Formの編集画面になり、Finish viewの値を「安否確認」に変更する
- この時、画面にTable 安否確認 dose not allow new entriesというエラーが出ますが気にせずスルーします。
- 右上のSAVEをクリックして保存する
この設定をする事で、安否入力後のSAVEボタンを押すと、安否確認のトップページに画面が遷移するようになります。できればこの欄でその時のGPS情報などを登録できるようテーブルを拡張しておくと、尚、安否確認情報として充実度が上がると思います。
図：ここから入力欄の設定画面へ入る
図：finish viewを設定する
図：エラーが出ても気にせず保存
避難所一覧
避難所パネルの装備
完全に個人用ということで装備したのが避難所一覧マップ。これの元データは国土地理院で配布してる全国11万件にもおよぶ避難所CSVデータを加工して入れました。shelterシートに格納しています。
洪水や高潮、火山や地震といった区分に1が入ってるものは指定避難所で、各施設の詳細な緯度経度情報も含まれている。
各区分に1が入ってるセルはTRUEに変換し、またTRUEの場所はタイトル名をカンマ区切りで結合して、区分列に格納しました。これで検索から「地震」と入れると、地震の際の避難所を検索できるようになります。また、緯度と経度に値が分かれていたので、カンマ区切りで結合して緯度経度情報に仕立て上げて、マッピングしています。
全部入れてしまうと膨大なので自分の住んでいる場所だけ入れてあります。
図：火山用シェルターを調べてみた
検索できるようデータを変換
しかし、国土地理院のデータは各避難所の属性に対してTrueが入ってるだけなので、AppSheet上でこれをそのまま実装しても、例えば「津波」属性を持ってる避難所を検索出来ません。よって、区分列に持っている属性（複数値）を格納して、検索可能にしてあげました。マップ上ではこの属性値は表示されませんが、データ的にはshowでsearchableにしてあります。
最近リリースされたスプシの複数値プルダウンを適用してみた様子が以下の様子。また、各属性は表示が「Y」となるので、YES/NOの時のそれぞれのtype detailをYの時は「◯」が表示されるように変更しています。この設定はビューの「避難所一覧_Detail (system generated)」でセットが必要です。その中にあるColumn orderをManualにして、各項目の鉛筆マークをクリックするとセット出来ます。
また、属性値がTRUEならば区分にカンマ区切りで入れるGASのコードも追加しています。各ヘッダを配列に格納しておき、データがTRUEならば一時配列に属性名を入れて、最終的にカンマ区切りの値になるようjoinして、一括書き込みしています。
//shelter属性情報整形
function shelterprop(){
//スプシ情報を取得
let ss = SpreadsheetApp.getActiveSpreadsheet();
let target = ss.getSheetByName("shelter")
let sheet = target.getRange("D2:K").getValues();
//属性情報
let prop = ["洪水","崖崩れ、土石流及び地滑り","高潮","地震","津波","大規模な火事","内水氾濫","火山現象"];
//格納用配列
let array = [];
//ループで属性情報を取得
for(let i = 0;i<sheet.length;i++){
//レコードを一個取り出す
let rec = sheet[i];
//属性情報がtrueならば一時配列に追加する
let temparr = [];
for(let j = 0;j<prop.length;j++){
//属性情報の名前を取得
let propname = prop[j];
//レコードの属性情報がtrueかどうか？
if(rec[j] == true){
temparr.push(propname);
}
}
//temparrが空じゃなければカンマ区切りに変換
if(temparr.length == 0){
//何もしないでスルーする
temparr = [];
}else{
//カンマ区切りに変換
temparr = [temparr.join(',')];
}
//temparrをarrayに追加
array.push(temparr);
}
//P列に一括書き込み
let lastColumn = array[0].length; //カラムの数を取得する
let lastRow = array.length; //行の数を取得する
//P列に一括で書き込み
target.getRange(2,16,lastRow,lastColumn).setValues(array)
ui.alert("変換完了！！")
}
図：複数値プルダウンに変更しました。
図：◯が表示されるようにセット
マップビューのピン表示項目を修正する
ピン表示内容を修正
テーブルをもとにマップビューを構築すると緯度経度に基づいてピンが表示され、ピンをクリックすると中身が表示されます。しかし、マップビュー上の下部で表示されてる内容がデフォルトだとテーブルの先頭から2列の内容になっていてイマイチです。これだと意味のないIDやらなにやらの表示になってしまいます。
これを修正するには以下の手順で修正します。
- 右側のプレビュー画面にて適当なピンをクリックして中に入る
- ピンの詳細データが出てくるので、非表示にしたいものにカーソルを合わせて鉛筆マークをクリック
- Edit Columnをクリック
- Showのチェックボックスを外して、Doneをクリック
- この作業を非表示にしたいもの全部に対して行います。
- 右上のSAVEをクリックして保存する
- 元の画面に戻ってきたら、その表示されてる内容の先頭2つがピンの表示項目として出てくることになります。
場合によっては、このフォームのDetailに入って、表示順番をColumn Orderで順番を入れ替えると尚良いです。
図：項目をまず非表示にする
図：こんな感じに直します
ユーザと地震ポイントでラベル切り替え
今回のマップビューではuserシートとpointシートの2つを合体させたmapviewシートを使っています。その為、userシートの列に合わせているため、地震データの場合ラベル的に合っていません。
そこで、タイプ列の値をもって判定しラベルの切り替えをさせています。
- 右側のプレビュー画面にて適当なピンをクリックして中に入る
- ピンの詳細データが出てくるので、非表示にしたいものにカーソルを合わせて鉛筆マークをクリック
- Edit Columnをクリック
- Displayに項目のDisplay Nameの入力欄をクリックする
- 以下のような数式を入れて判定切り替えさせる
if([タイプ]="ユーザ","氏名","震度")
上記はタイプがユーザの場合は氏名、ソレ以外の場合は震度とラベルを切り替えています。住まいのほうも同様に発生地に変えるように組んであげます。
- これをもう一個のほうも同じ用に設定する
- Doneをクリックする
- 右上のSAVEをクリックして保存する。
これで、タイプ列をみてラベルの表示が変わるので違和感がなくなります。
図：数式で表示切替できる
図：地震とユーザで表示ラベルを切り替え
管理機能の項目の挙動
管理機能の非表示
社員、管理用、手動送信のビューは一般のユーザには表示させたくない項目です。しかしそこだけ別アプリに切り出すのも管理が面倒です。ということで、スプレッドシートのuserシートに於いて、ロールが「管理者」の人の場合にだけ表示するように、これら3つのビューに対して表示制限を掛けています。
user, 管理用, 手動送信それぞれのビューのDisplay項目にある「show if」項目に於いて以下の関数をセットして実現しています。
if(LOOKUP(USEREMAIL(),"user","メアド","ロール")="管理者",true,false)
現在のユーザのメアドを元にLOOKUP関数でロールを拾ってきて、管理者ならTrue、そうでなければfalseを返して表示・非表示を切り替えています。一般ユーザは安否確認とマップの2つのビューのみが表示されるようになります。
図：管理用の表示の仕掛け
管理用ビューのボタン
バックアップと訓練発信の2つの機能だけ持っている管理機能ですが、これはボタンではなくレコードに画像フィールドを持たせて、ギャラリー表示してるだけのものです。
ただこの表示の為にいくつかロジックを構築しています。
- admin_Detail (system generated)のビューにて、Column orderの調整や非表示にする処理をして項目名だけが表示されるようにしています。デフォうとだとIDなどが表示されてしまい、具合が悪いため。
- 管理用ビューにて、View TypeをGallaryにしてBehaviorセクションにてRow Selectedに直接「タスク実行」のアクションを指定しています。これでタップするだけでアクション実行されてGASが発動するようになります。
図：Detailビューで表示を調整
図：Behaviorで仕掛けをする
二重ドロップダウン
社員および手動送信の2つのビューに於いて、エリア・都道府県のドロップダウンを装備しています。しかしエリアが東北なのに都道府県が東京都を選ばれたりするのを防ぐ為と、東北を選んでるのに都道府県に他県が選択肢として出てくるのも困ったものです。これを防ぐ為に、エリア選択をしたら、自動的にフィルタした内容を都道府県側に表示する様に仕掛けをしています。
都道府県選択後にあらためてエリアを変更すると都道府県ドロップダウンは空になってくれるので、誤った選択をさせない仕組みに出来ます。
- エリアのドロップダウンは普通に作成（今回はEnum型を指定して直接選択肢を追加しています）
- 都道府県のドロップダウンはprefectureに対してRefで作成（ただし追加を防ぐ為にsliceしたテーブルを指定しています）
- また、都道府県のドロップダウンのData ValidityセクションのValid Ifに対して以下の数式を入力して自動フィルタさせます
select(prefecture[都道府県],[エリア]=[_thisrow].[エリア])
- これでエリア選択⇒都道府県は絞られて表示が実現可能
図：Enumでラジオボタンになります。
図：Valid Ifでフィルタして表示させる
より高度な機能を搭載する
その後色々と追加要望が来たので自身で使う分について以下の実装を追加しています。但しサンプルには含まれていません。以下はその追加実装のあらましについて追記しています。
新たなる要件追加
今回のアプリではuserシート上ではユーザと管理者しか区別していません。そのため例えば以下のような要件追加が来た場合には、このままではアプリとしては不十分です。
- 管理部門や管理者以外に、部課長などのグループリーダー、チームリーダーに対してもBCP対策を分散して行わせたい。
- しかし、各トップは自身のグループ配下のメンバーのみを社員一覧、マップに表示するようにしたい。
- またスプシは管理部門・管理者のみが閲覧可として他の部課長には共有させたくない。
これらの要望に対しては以下のような実装が必要になります。
- userシートにスコープ列および所属列を追加する
- 列追加に伴い、AppSheet側でregenerateおよびGASの挙動を修正する
- ロールに新たに「マネージャー」を追加する
- スコープ列は管理者およびマネージャだけに割当をし、管理者は「全社」を指定し、マネージャは自身の所属グループを指定する
- 所属列は全メンバーに必要で、複数の値をもたせます（全社 + グループ + チームの最大3つを追加する）。ドロップダウンとして複数値追加を設定する。
- スコープ列および所属列で利用する部門を格納するscopeシートを用意する
- 管理者以外スプシを共有しないので他のマネージャが自身のメンバーの安否確認回答内容を閲覧するための回答確認ビューを新規に用意する
- マネージャは安否確認(回答確認)、マップ、社員の3つのタブは見られるように別途制御をする必要がある。
- 回答確認ビューはblank（未回答）を上に来るように昇順で自動ソートするようにする
細かな制御もまた必要となるので思っている以上に追加で実装が必要です（自分は4時間程度掛かりました）。
図：スプシはこんな感じで整備が必要
追加実装のポイント
今回この追加要望に対する追加実装のポイントは複数あります。
マネージャーの時に表示するビュー
これまではロールがユーザの場合は安否確認ビューのみ。管理者の場合にはマップ、ユーザ一覧、管理用、手動発信の4つを更に表示する仕組みでした。しかし、マネージャが追加された事と回答確認ビューを追加したことで、もう１つこれらのロールに応じて表示するビューを制御する必要が出てきました。
- 各ビューのshow ifに於いての制御を書き換える
- マネージャにも見せるビューの場合は以下のようなフィルタを書きます。よりロールが増えた場合にはswitch関数などを駆使する必要があるかもしれない。アクセスしてきてる者の「ロール」を見て判定させています。
if(LOOKUP(USEREMAIL(),"user","メアド","ロール")="ユーザ",false,true)
図：ユーザ以外の場合には表示として設定 - 管理者のみに見せるビューの場合は以下のようなフィルタを書きます。これはこれまで通りの数式
if(LOOKUP(USEREMAIL(),"user","メアド","ロール")="管理者",true,false)
各スコープに応じた内容のフィルタ
マネージャが増えたことで、マップ、社員一覧、マップに表示する項目はそれらマネージャのスコープの内容に応じてフィルタした結果を表示する必要があります。
これはそれぞれのビューに紐ついてる「スライス」に対して、Row filter conditionにてフィルタして上げる必要があります。数式としては以下のようなものになります。
AND(CONTAINS([_THISROW].[所属],LOOKUP(USEREMAIL(),"user","メアド","スコープ")),[タイプ] <> "地震")
- Contains関数を持って、レコードの所属に対象のスコープが含まれているかどうかを判定します。
- また、同時にタイプが「地震以外」の条件もあるため、この2つをAND関数で括ります。
こうすることで、アクセスしてきてるユーザのスコープ列の値に応じてフィルタした内容で表示させることが可能になります。
図：スコープに応じてフィルタ
回答確認ビューの作成
新たにマネージャ用に安否確認回答確認用のビューが必要です。しかし、すでに画面下のタブにはたくさん表示されている為、安否確認タブ内に「マネージャ・管理者の場合にだけ表示するボタン」を用意し、ボタンをクリックすると安否回答確認用のビューへと異動して中を前述同様にスコープ列でフィルタした内容を表示するという仕組みにしています。
- anpiテーブルに対して新規に「安否回答確認」というスライスを用意。スライスのフィルタは以下の通り。これを元に回答リストというビューを用意する
CONTAINS([_THISROW].[所属],LOOKUP(USEREMAIL(),"user","メアド","スコープ"))
※ここでスコープに応じて表示するメンバーをフィルタしてる
- anpiに対して新規アクションとして「回答確認」を用意し、Do thisは「App: go to another view within this app」とする
- この回答確認アクションのTargetには以下の式を入れました。
LINKTOVIEW("回答リスト") - また、このアクションのBehaviorのonly if this condition is trueには、以下のようなフィルタ式を追加する。コレを入れないと回答リストにまで同じボタンが表示されてしまう為。またこうすることで、マネージャと管理者の場合にボタンが表示されるようになります。
IFS( CONTEXT("VIEW")="回答リスト", false, CONTEXT("VIEW")="安否確認", if(LOOKUP(USEREMAIL(),"user","メアド","ロール")="ユーザ",false,true) ) - 回答リストのビューでは、Group byにて安否列をAscendingとしてソート。Tableとして表示しています。
これで殆どの要望を満たすことができるようになりました。
このビューはスマフォ表示では下のバーには列挙しないようにしてはいますが、デスクトップビューだと左サイドバーに表示されてしまうので、DisplayのShow ifに以下の制御をいれるのを忘れないように。スマフォビューとデスクトップビューとでこのように異なった表示になることがあるので、デバッグ時は両方の表示モードでチェックしましょう。
if(LOOKUP(USEREMAIL(),"user","メアド","ロール")="ユーザ",false,true)
図：新たに追加したアクションボタン
図：安否回答確認ビュー
動作サンプル動画
関連リンク
- エマージェンシーコール - infocom
- 安否確認システム - Secom
- 南海トラフ地震臨時情報が発表されたら！
- 気象庁XML用APIを利用した気象情報取得法の紹介
- Google App Script と Google Formsで安否確認Botを作ってみた
- 安否確認システムをスプレッドシート(GAS)で無料で作ってみた
- ～AppSheet で「防災備蓄管理」～【第22週】プログラミング未経験者が独学成果物で「1千イイね」とれるまでリアルタイム実況
- サテライトオフィス・安否確認 for Google Workspace
- 安否確認アプリ - Microsoft PowerApps
- 指定緊急避難場所データ - 国土地理院
- Ref not showing in Form View
- Show if based off of action button pressed
- Multiple criteria in Slice Row Filter Condition
- select() with in()
- Select expression with CONTAIN
- Viewを追加する［AppSheet入門 #9-2］
- Viewに応じてDisplay Nameを変えたい
- アクションの基礎 - AppSheetヘルプ
- 【これで解決！】select()関数の使い方
