GASをフル活用して有給管理システムを自作。GAShtmlメールとトリガー設定の実装

読んで欲しい人
前回までのおさらい
コード全体像のおさらい
コード解説
まとめ

読んで欲しい人

GASを使ってみたい人
有給管理システムが必要だが手をつけられていない方
コストゼロで有給管理を行いたい会社
GASを中心にした内部システム構築を行なっている方
GASで自動メールの送信を行いたい人

前回までのおさらい

さて、前回ゴールイメージと実際のコード全体を明示しました。お忘れのかたは以下の記事をどうぞ。

さて、続きとなる今回は、実際のコードを解説していきたいと思います。

コード全体像のおさらい

シリーズ2つ目で示したコードの全容は以下でした。今回はfunction sendMessage(e)で利用しているコードを解説していきます。

function sendMessage(e) {
  /*
  <summary>
  　Formで登録された内容をe(event Object)として受け取り、SS上から必要データを取り出して、
   承認者向けにメールを送付する。メールの内容として、承認・否認の選択肢を付与する。
   SSには登録されたデータのStatusを”申請中”として登録する
  */
  
  //eから対象シートのRowを取得、ついでにSSに申請中と登録
  var row = e.range.getRow();
  var sheet = SpreadsheetApp.openById("XXXX").getSheetByName("有給申請管理");　//XXXXをSSのIDへ変更
  sheet.getRange(row,8).setValue("申請中");
  
  // メールに貼り付けるBodyをgenerateBody関数から作成
  var bodies = generateBody(e.values);
  
  // urlはこのGASをウェブアプリで公開した、アドレス＋パラメータとして、rowとanswerをつける
  var url = "https://script...."+ "?row=" + row +"&answer=";　//https:...以下ををスクリプトをウェブアプリケーションとして公開した際に表示されるURLへ変更
  
  
  //承認者のメアドを取得。事前にシート対象セルに計算式をはめ込む（ここは、GAS上で条件分岐を入れて検索してもOK）
  sheet.getRange(row, 9).setValue("=if(G"+ row +"=\"全日\",1,0.5)");
  sheet.getRange(row, 10).setValue("=lookup(B"+ row + ",mail_adress_master!A:A,mail_adress_master!C:C)");
  sheet.getRange(row, 11).setValue("=lookup(B"+ row +",mail_adress_master!A:A,mail_adress_master!D:D)");
  var approver = sheet.getRange(row,11).getValue(); 
  
  var recipient = approver;
  var subject = "有給休暇取得申請のお知らせ";

  var body = "";
  body += '有給休暇取得申請がありました\n\n';
  body += bodies.plane;
  body += "承認する場合は以下をクリック";
  body += url + "ok";
  body += "承認しない場合は以下をクリック";
  body += url + "ng";
  
  var html ="";
  html += '<h1>有給休暇取得申請のお知らせ</h1>';
  html += '<p>以下の有給休暇取得申請がありました</p>';
  html += bodies.html;
  html += "<p>承認する場合は、<a href="+ url + "ok>【承認する】</a>をクリックしてください</p>"
  html += "<p>承認しない場合は、<a href="+ url + "ng>【否認する】</a>をクリックしてください</p>"
  
  GmailApp.sendEmail(recipient, subject, body, {htmlBody:html});
  
}


function doGet(e){
  /*
  <summary>
  　承認者がメールで選択したのち、選択した結果をSSのStatus項目に反映する。
   また、申請者には結果をメールする。そして、承認者には選択した結果をブラウザ画面で表示する
  */
  
  var row = e.parameter.row;
  var sheet = SpreadsheetApp.openById("XXXX").getSheetByName("有給申請管理"); //XXXXをSSのIDへ変更
  var values = sheet.getRange(row, 1, 1, 7).getValues()[0];
  Logger.log(values);
  
  var bodies = generateBody(values);
  var answers = e.parameter.answer;
  var result = {
    ok:"承認",
    ng:"否認"
  };

  var recipient = values[5];
  
  if(answers){
  sheet.getRange(row, 8).setValue(result[answers]);
 
  var subject = "有給休暇取得申請" + result[answers] +"のお知らせ";
  var body = "";
  body += '以下の有給休暇取得申請が'+ result[answers] +'されました。\n\n';
  body += bodies.plane;
  
  var html = '';
  html += '<h1>有給休暇取得'+ result[answers] +'のお知らせ</h1>';
  html += '<p>以下の有給休暇取得申請が'+ result[answers] +'されました。</p>';
  html += bodies.html;
    
  GmailApp.sendEmail(recipient, subject, body, {htmlBody:html}); 
  
  var html = '';
  html += '<h1>有給休暇取得申請の'+ result[answers] + '</h1>';
  html += '<p>あなたは以下の有給休暇取得申請を'+ result[answers] +'しました</p>';
  html += bodies.html;
  
  return HtmlService.createHtmlOutput(html);
  };
}

function generateBody(values){
  
  var staff_num = values[1];
  var staff_name = values[2];
  var date = values[3];
  var hours = values[6];
  
  var plane = "";
  plane += '社員番号: ' + staff_num + '\n';
  plane += '氏名: ' + staff_name + '\n';
  plane += '有給取得日: ' + date + ' \n';
  plane += '時間: ' + hours + '\n';
  
  var html = '';
  html += '<ul>';
  html += '<li>社員番号: ' + staff_num + '</li>';
  html += '<li>氏名: ' + staff_name + '</li>';
  html += '<li>有給取得日: ' + date + ' </li>';
  html += '<li>時間: ' + hours + '</li>';
  html += '</ul>';
  
  return {plane:plane, html:html};
}

コード解説

今回解説するfunction sendMessage(e)関数で実行していることは、主に以下になります。

Formで登録された内容をe(event Object)として受け取り、SS（Spreadsheet)上から必要データを取り出して、承認者向けにメールを送付する。メールの内容として、承認・否認の選択肢を付与する。SSには登録されたデータのStatusを”申請中”として登録する

Spreadsheet Event Objectについて

function sendMessage(e) {
  var row = e.range.getRow();

var rowとして、row変数に e.range.getRow()を代入。

とだけ言っても、そもそもeってなんだという疑問がわかれるかと思います。ここでは、少しEventObjectというものの説明をしてみます。

スプレッドシートを開いた時や変更した時に当然グーグル側ではどんな作業がなされたのかを記憶（Logging）しています。その内容をeというobjectに入れ込んで、私たちが使えるようにしてくれています。

今回は、Form登録した時、スプレッドシートに申請者の登録内容が記入されています。これは、スプレッドシートをEdit(編集）したことを意味しており、その情報を利用して今回は何行目にその登録内容が記入されたのかを取得しています。

また、e objectでは、他にもValue(入力された値）やuser(入力者のemailアドレス）なども取得できます。e.valuesはすぐに出てきます。詳細は以下のGoogle Apps Scriptのドキュメントをご参照ください。

Googleドキュメント: https://developers.google.com/apps-script/guides/triggers/events

シートを取得して、ステータスを貼り付ける

var sheet = SpreadsheetApp.openById("XXXX").getSheetByName("有給申請管理");　//XXXXをSSのIDへ変更
  sheet.getRange(row,8).setValue("申請中");

次はこれまでも何回も利用してきた、sheet情報の取得と、そのシート内にデータを記入する方法です。先ほど変数設定したrowという変数を利用して、行数を指定しています。今回は入力する列が決まっているので、getRange()の行列は、(row,8)と固定値にしてあります。

独自関数にe.valuesを引数として渡す

var bodies = generateBody(e.values);

このgenerateBodyというのは、GASでもともとある関数ではなく、今回自分たちで独自に作成する部分になります。コード一覧の最後に以下のようなコードがあります。

function generateBody(values){
  
  var staff_num = values[1];
  var staff_name = values[2];
  var date = values[3];
  var hours = values[6];
  
  var plane = "";
  plane += '社員番号: ' + staff_num + '\n';
  plane += '氏名: ' + staff_name + '\n';
  plane += '有給取得日: ' + date + ' \n';
  plane += '時間: ' + hours + '\n';
  
  var html = '';
  html += '<ul>';
  html += '<li>社員番号: ' + staff_num + '</li>';
  html += '<li>氏名: ' + staff_name + '</li>';
  html += '<li>有給取得日: ' + date + ' </li>';
  html += '<li>時間: ' + hours + '</li>';
  html += '</ul>';
  
  return {plane:plane, html:html};
}

このfunction generateBody(values)というのが、そうです。

今回変数bodyには、この独自に作った関数を利用して、メール本文の内容データを作成しています。generateBody内で何をしているかは今回は一旦省略して、後日コード解説する予定です。

さて、generateBody（）の（）内に、e.valuesというのがあります。これも先ほどお伝えした、Event Objectです。そのValues（入力値）を取得して、generateBody関数に渡しているということを意味しています。

generateBodyは渡された、e.valuesのデータを使って、値（今回は返ってくるのもobjectですが)を生成します。

要は、メール本文内に記入するデータをgenerateBodyに渡したデータを使って作成して、変数として保持しているということです。変数や引数（今回のe.values）については、いつか説明します。関心のある方はググっていただければたくさん記事は出てくるはずです。

urlを作成する

var url = "https://script...."+ "?row=" + row +"&answer=";

さて、次はメール本文内に、承認・否認の場合それぞれで、クリックしてもらうためのurl linkを作る部分です。var urlに代入していくのですが、代入内容に少し工夫が必要なので、その説明です。

クリックしてもらって何をしたいかと言うと、次回説明するdoGet関数を呼び出すことが目的となります。このためには、実はアプリケーションを公開する必要があります。

これまでの、コードは外部からアクセスする必要がなかったので不要でしたが、今回は承認者が受け取ったメールで承認・否認を選択してクリックしてもらった結果を受け取る必要があります。そのため、外部からのアクションを受け取る際に利用するのがdoGet()関数となります。

公開方法は次の画像の通りです。公開すると、urlが表示されるので、”https://script….”の部分に当該コードを挿入してください。

承認者を社員マスタから選択する

sheet.getRange(row, 9).setValue("=if(G"+ row +"=\"全日\",1,0.5)");
  sheet.getRange(row, 10).setValue("=lookup(B"+ row + ",mail_adress_master!A:A,mail_adress_master!C:C)");
  sheet.getRange(row, 11).setValue("=lookup(B"+ row +",mail_adress_master!A:A,mail_adress_master!D:D)");
  var approver = sheet.getRange(row,11).getValue();

実行していることはシンプルです。1つ目は、全日で申請された場合は１日、それ以外（午前・午後）が申請された場合は0.5日をシートに表示するために、関数を対象セルに貼り付けているだけです。

他の二つも、社員マスタから申請者に対応する承認者を選択する計算式を入れたりしているものです。

GASでメールを送る

var recipient = approver;
  var subject = "有給休暇取得申請のお知らせ";

  var body = "";
  body += '有給休暇取得申請がありました\n\n';
  body += bodies.plane;
  body += "承認する場合は以下をクリック";
  body += url + "ok";
  body += "承認しない場合は以下をクリック";
  body += url + "ng";
  
  var html ="";
  html += '<h1>有給休暇取得申請のお知らせ</h1>';
  html += '<p>以下の有給休暇取得申請がありました</p>';
  html += bodies.html;
  html += "<p>承認する場合は、<a href="+ url + "ok>【承認する】</a>をクリックしてください</p>"
  html += "<p>承認しない場合は、<a href="+ url + "ng>【否認する】</a>をクリックしてください</p>"
  
  GmailApp.sendEmail(recipient, subject, body, {htmlBody:html});

GmailAppクラスには、メールを送ったり、メールの中身を確認するメソッドが含まれています。今回は、メールを送るメソッドsendEmail()関数を利用します。

sendEmail(recipient//受取人, subject//メール件名, body//メール本文, {オプション})

また、送付するメールは、htmlが受け取れる場合はhtmlメールを、それ以外はplaneメールとして送ることにしています。

最終行のGmail.App(….,body,{htmlBody:html});で分岐されております。基本body変数をメールのBodyとして送付します。ただ、オプションとしてhtmlが読み込める場合、html bodyにできるように{htmlBody:html}としています。