【Series of script explanation】Make your own paid leave management system by fully utilizing GAS ~Sending email to the approver when the applicant registers the Form (html email setting by GAS and trigger setting)~

GAS
この記事は約20分で読めます。
Advertisements

Target Users

  • who want to use GAS(google apps script)
  • who need paid leave management system but not prepared it
  • who want to perform paid management at zero cost
  • who have built an internal system centered on GAS
  • who want to send e-mail automatically by GAS

Let’s review before previous articles

Well, last time we showed the goal image and the actual code as a whole. If you have forgotten, please read the following article.

【Series of script explanation】Make your own paid leave management system by fully utilizing GAS

In this time, let’s start explaining actual code detail.

Review actual code as a whole

Actual code as a whole is following, that is introduced by previous article.

In this time, focusing on script in “function sendMessage(e)“, I will explain it.

function sendMessage(e) {
  /*
  <summary>
   Receive the contents registered in Form as e parameter, extract necessary data from SS,
    Send an email to the approver. An option of approval / denial is given as the content of the mail.
    Register the status of the registered data as "Applying" in SS
  */
  
  //Obtain the row of the target sheet from e-parameters, etc., then register on target cell in spreadsheet as pending
  var row = e.range.getRow();
  var sheet = SpreadsheetApp.openById("XXXX").getSheetByName("有給申請管理"); //XXXX -> change actual spreadsheetID
  sheet.getRange(row,8).setValue("申請中");
  
  // Create Body to be pasted into email from generateBody function
  var bodies = generateBody(e.values);
  
  // url published this GAS in web application, add row and answer as address + parameter
  var url = "https://script...."+ "?row=" + row +"&answer="; //https:...以下ををスクリプトをウェブアプリケーションとして公開した際に表示されるURLへ変更
  
  
  //Get the approver's email. Insert the calculation formula in the sheet target cell in advance (here, it is OK to search by inserting conditional branches on GAS)
  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 += '有給休暇取得申請がありましたnn';
  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>
   After the approver makes a selection by e-mail, the selected result is reflected in the SS Status item.
    In addition, e-mail the result to the applicant. Then, the selected result is displayed on the browser screen for the approver.
  */
  
  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] +'されました。nn';
  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};
}

Explanation of Script

The followings are mainly executed by the function sendMessage (e).

  • Receive the contents registered in Form as e (event Object)
  • Extract necessary data from SS (Spreadsheet) and send e-mail to approver
  • An option of approval / denial is given as the content of the mail
  • Register the status of the registered data as “Applying” in SS

Spreadsheet Event Object

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

As of var row, assigned values of “e.range.getRow()” to variables “row”.

If just say that, you may be wondering what “e” is. Here, we will explain a little about EventObject.

Surely, when you open or change the spreadsheet, Google remembers what was done (Logging). The content is put into an object called “e”, so that we can use it.

This time, when the Form is registered(when applicant input and submit), the applicant’s registration details are filled in the spreadsheet. This means that have edited the spreadsheet, and this information is used to get the line number where the registration was filled.

In “e” object, you can also get value (input value) and user (email address of input user). e.values comes out soon. Please refer to the following Google Apps Script document for details.

Google Document: https://developers.google.com/apps-script/guides/triggers/events

Getting sheet and past status information

var sheet = SpreadsheetApp.openById("XXXX").getSheetByName("有給申請管理"); //XXXX -> change actual spreadsheetID
sheet.getRange(row,8).setValue("申請中");

Next is the method of getting sheet information and filling in data in that sheet, which we have used many times before. The number of rows is specified using the variable called row, which was previously set as a variable. Since the input column is fixed this time, the matrix of getRange () is fixed to (row, 8).

Pass e.values as arguments to your own function

var bodies = generateBody(e.values);

This generateBody is not a function originally in GAS, but a part that we create ourselves this time. The following code is at the end of the code list.

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};
}

Above “function generateBody(values)” is your own function, we mentioned.

In this time, variables body is body data of mail body by using own function. What do in generateBody is omitting this time, later will explain about it.

Well, there is e.values in () of generateBody (). This is also the Event Object I mentioned earlier. It means that the value (input value) is obtained and passed to the generateBody function

Function generateBody uses the passed e.values data to generate a value (this time the returned object is also an object).

The point is that the data to be entered in the email body is created using the data passed to Function generateBody and is stored as a variable. I will explain variables and arguments (e.values this time) someday. If you are interested, please google and you will see many articles.

Creating URL using in mail body

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

By the way, next is the part to make the url link to have you click in each case of approval / rejuect in the mail text. I’m assigning to var url, but I need to devise a little bit about the content of the assignment.

The goal of what clicking by approver is to call the doGet function which will be explained next time. To do this, you actually need to publish the application.

Until now, the code did not need to be accessed externally, so it was unnecessary, but this time you need to receive the result of having the approver select and approve and click on the email received by the approver. Therefore, the doGet () function is used when receiving an external action.

The publishing method is as shown in the following image. When you publish, the url will be displayed. Insert the relevant code in the “https: // script….” Part.

ウェブアプリケーションとして導入(公開する)方法

Selecting Approver from employee master sheet

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(); 

What you are doing in this script is very simple. The first is simply pasting the function to the target cell to display one day if applied for all days, and 0.5 days for other (AM and PM) applications.

The other two also include a formula to select the approver corresponding to the applicant from the employee master.

Sending email by GAS

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

  var body = "";
  body += '有給休暇取得申請がありましたnn';
  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});

The GmailApp class contains methods for sending emails and checking the contents of emails. This time, use the sendEmail () function to send an email.

sendEmail(recipient, subject, body, {options})

Also, we will send email by html format if you can receive html, plane mail otherwise.

The last line, “GmailApp (…., body, {htmlBody: html});” is set for separating. Send the basic body variable as the body of the email. However, if html can be read as an option, it is set to {htmlBody: html} so that html body can be used.

Finally,

It has become quite long. It is necessary to get accustomed to proceed while understanding at once so far, so if you understand the GAS specifications one by one and read the behavior of the goal in the first place, the meaning of each code I think you can see.

Next time, I will explain the doGet (e) function and the code corresponding to the action that takes place after the approver clicks the approve / reject email.

連載目次: GASで有給取得申請管理

  1. GASをフル活用して有給管理システムを自作する
  2. GASをフル活用して有給管理システムを自作する〜利用コードと実際画面フロー〜
  3. 【コード解説シリーズ】GASをフル活用して有給管理システムを自作する〜まずは設定から〜
  4. 【コード解説シリーズ】GASをフル活用して有給管理システムを自作する〜申請者がForm登録したタイミングで承認者にメールを送る(GAS htmlメールの設定とトリガー設定)〜
  5. 【コード解説シリーズ】GASをフル活用して有給管理システムを自作する〜承認者がメールの承認・否認を選択→申請者へ結果通知、承認者へ完了Web画面表示〜

コメント