【GAS】Google Apps Script 活用事例　Google Formsと連携して、労災報告メールを効率化した話

2020年2月8日 21:50

外出すればするほど、やる事が増える

もはや、自分たちはフィリピンのスタッフに、こき使われている日本人なんじゃないかと思うくらい、通訳担当の人が頻繁に病院同行に出掛けている現状があります。コンシェルジュじゃないんだぞ。懐事情の問題で、生活するのが精一杯で、万が一の備え（医療費）は無いので、誰かが立て替える必要がある.....そんな側面もあります。

とはいえ、外出する度に交通費申請、場合によっては仮払申請、それから上長やチームに経緯報告などをしなければならず、効率的ではありません。（※これからはデスクで営業する時代です。みたいな広告も最近よく見かけます。）

入力は、やっぱり、Google Forms

効率化、生産性の向上を声高に叫んでも、入力作業は避けられません。その入力してもらった内容を無駄にする事なくシームレスにメールないし、Slackに飛ばして生かす事が今回の効率化の目的です。

同じ内容を繰り返しタイプする事って、実務では、あるあるだったりします.....。

Formsを作る前に労災を勉強しよう！！

大別すると、通勤災害、業務災害の2つがあります。駅の階段から滑って転んだ場合は、通勤災害。業務中にガラス片が刺さって怪我したとかは、業務災害。で、必ず労災が起こったら労災指定病院に行けと言われるのは、無料になるからっぽいです。・・・労災受けた事無いので、ちょっと分からず。

労災指定病院で診療を受けた場合は、基本的に窓口払いはありません。労災指定病院以外で診療を受けた場合には、一旦窓口払いを行った後、後日療養（補償）給付を国に請求することで、かかった医療費の還付を受けることができます。

スモビバ！より引用

病院同行は、労災対象の傷病以外も考慮に入れていて、選択肢がたくさんあるのですが、通勤災害、業務災害を入れておいて、その2つが選ばれたら、労務課の課内共有アドレス宛に送られる仕組みにしたら、良さそうです。

Formsの選択肢はこんな感じ（考えられるのをパーッと書き出してみました。）

スクリーンショット 2020-02-08 8.47.38

完成形のスクリプト

/*IDからスタッフ名を調べるVLOOKUP的な動きをするスクリプト*/
function vlookup() {
 
 var spreadsheet  = SpreadsheetApp.getActiveSpreadsheet();
 var formsRawData = spreadsheet.getSheetByName('Forms');
 var lastRow      = formsRawData.getLastRow();
 var lastColumn   = formsRawData.getLastColumn();
 
 /*3列目　最終行のIDを習得*/
 var staffId      = formsRawData.getRange(lastRow, 3).getValue();
 Logger.log(staffId);
 
 
 var staffDb         = spreadsheet.getSheetByName('DB');
 var lastRow2        = staffDb.getLastRow();
 var values          = staffDb.getDataRange().getValues();
 var specificValues  = staffDb.getRange(1, 1, lastRow2).getValues();
 
 /*範囲の確認*/
 Logger.log(staffDb.getRange(1, 1, lastRow2).getA1Notation());
 
 /*IDのみが存在する1次元配列から、特定のIDを見つける*/
 var targetRow = Array.prototype.concat.apply([], specificValues);
 var row       = targetRow.indexOf(staffId);
 
 Logger.log(targetRow);
 Logger.log(row);
 Logger.log(values[row][2]);
 
 return values[row][2];
}


function createDraft() {
 var spreadsheet  = SpreadsheetApp.getActiveSpreadsheet();
 var formsRawData = spreadsheet.getSheetByName('Forms');
 var lastRow      = formsRawData.getLastRow();
 var lastColumn   = formsRawData.getLastColumn();

 /*最終行の内容を取得*/
 var rowContents  = formsRawData.getRange(lastRow, 1, 1, lastColumn).getDisplayValues();
 
 Logger.log(rowContents);
 Logger.log(rowContents[0][3]);//業務災害、通勤災害
 
 if(rowContents[0][3].indexOf('業務災害') !== -1 || rowContents[0][3].indexOf('通勤災害') !== -1 ){
   /*SpreadsheetURLの生成*/
   var fileName     = spreadsheet.getName();
   var url          = spreadsheet.getUrl();
   var sheetId      = formsRawData.getSheetId();
   var marge        = url + "#gid=" + sheetId;
   
   
   /*スタッフ名を新しい配列に追加する*/
   var staffName    = vlookup();
   rowContents[0].splice(3, 0, staffName);
   
   Logger.log('Google Formsの回答シートの最終行は、' + lastRow);
   Logger.log(rowContents);//配列
   Logger.log(rowContents[0][3]);//STAFF名
   
   var mailTitle = rowContents[0][2] + '　' + rowContents[0][3] + '　労災発生報告について'
   
   var message = '労務各位' +'<br><br>' + 'お疲れ様です。' + '<br>' + rowContents[0][1] + 'です。' + '<br><br>' +
   '表題スタッフの労災について、ご報告申し上げます。' + '<br>' + '詳細は下記の通りです。' + '<br><br>' +
   '【スタッフID】' + rowContents[0][2] + '<br>' +
   '【スタッフ名】' + rowContents[0][3] + '<br>' +
   '【症状】' + rowContents[0][4] + '<br>' +
   '【発症日】' + rowContents[0][5] + '<br>' +
   '【労災病院に行ったか？】' + rowContents[0][6] + '<br>' +
   '【保険証の使用について】' + rowContents[0][7] + '<br>' +
   '【病名・診断名】' + rowContents[0][8] + '<br>' +
   '【病気の詳細・本人の様子】' + rowContents[0][9] + '<br>' +
   '【完治するまでにどれくらい掛かりそうか？】' + rowContents[0][10] + '<br>' +
   '【復帰見込み】' + rowContents[0][11] + '<br><br>' +
   '詳細は、下記Spreadsheetをご確認ください。' + '<br><br>' + fileName + '<br>' + marge;
   
   Logger.log(mailTitle);
   Logger.log(message);
   
   
   var mailCc      = 'チームのメーリスを入れる';
   var mailBody    = '';
   var mailAddress = '労務宛のメールアドレスを入れる';
   var mailArgs = {
     cc: mailCc,
     htmlBody: message,
   }
   GmailApp.createDraft(mailAddress, mailTitle, mailBody, mailArgs)
   
 }
}

function vlookupに関しては、GAS本に記載があったものを拝借し、Google Formsで入力されたIDと、DBのIDが一致するかを調べて、同じ行の2列目の値を取得みたいな感じです。

=VLOOKUP(A2,'DB'!A:D,2,0)

スクリプトでVLOOKUP的な処理を書くメリットは、シートを増やさなくて済む事です。シートが多くなればなるほど、管理が行き届かなくなります。シートは少なく、見るべきシートが瞬時に分かる方が好ましいです。

外国人スタッフの名前は長く、入力が面倒くさい

名前にミドルネームがあるので、まぁ面倒くさい。そして面倒なものは次第に使われなくなります。なので、Formsには、スタッフIDのみ記入してもらい、スクリプト側で名前を取得するという処理を書いています。

Google Formsの回答先シートの最終行を取得し、vlookupの処理で取得したスタッフ名を配列に追加しています。そして、メールの件名、本文に使っています。

/*スタッフ名を新しい配列に追加する*/
   var staffName    = vlookup();
   rowContents[0].splice(3, 0, staffName);

業務災害、通勤災害だったら、メールを送るための条件分岐

 if(rowContents[0][3].indexOf('業務災害') !== -1 || rowContents[0][3].indexOf('通勤災害') !== -1 ){
//具体的な処理
}

二重否定っぽい感じ　-1（一致していない）ではなかったら、ちょっと分かりづらいかも。素直に　=== 1　とすべきかも。

テスト運用中のため、GmailApp.createDraft(mailAddress, mailTitle, mailBody, mailArgs)　と書いていますが、本運用では、sendEmailにしておき、フォーム送信時にメールが飛ぶ仕組みの方が遥かに効率が良くなるでしょうね。

メールの次世代ツールがSlackとかHangOut Chatみたいな位置付けだから場合によってはメールではなくSlack botを作成してみてもいいかもしれません。

別件で、VLOOKUP的な処理をするスクリプトを凄い人に書いてもらいました。

var pk = [];
 for(var i=1; i<values.length;i++){
   pk.push(values[i][0]);
 }
 
 //pkをprimary keyとしてシート[VLOOKUP:参照]のフィールド[氏名]を参照してnewValuesに格納してsetValuesする
 var rfSheet  = spreadsheet.getSheetByName('VLOOKUP:参照');
 var rfValues = rfSheet.getDataRange().getValues();
 
 var newValues = [];
 
 for(var i=0; i<pk.length; i++){
   for(var j=1; j<rfValues.length; j++){
     if( rfValues[j][0].indexOf(pk[i]) !== -1 ){
       newValues.push([rfValues[j][1]]);
     }
   }
 }
 sheet.getRange(3,2,newValues.length, newValues[0].length).setValues(newValues);
 
 return

TSUJI KENZO　さんに書いてもらいました。対象となるシートのIDのみを配列pkに入れ、DBとなるシートのID蘭とぶつけて、一致したらnewValuesに、名前を格納していく。

病院同行は一回で終わらないケースも散見されるので、次回の通院もあるようなケースがあるので、そういった場合に、IDを全て調べて複数回登場したら、経過報告書みたいな感じでGoogle ドキュメントを作成するみたいな処理を書くのに使わせてもらおうかなと思います。

この記事が気に入ったらサポートをしてみませんか？