Make your own paid leave management system by fully utilizing GAS〜first step, settings〜

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

List of actual setting procedures

Intro of setting procedures list

First, we will look and explain what needs to be set before explaining script.

Set up Google Form for paid application
Set the required elements on the spreadsheet sheet
Set up Google Form URL to call from Slack Bot

Setting task1: Set up Google Form for paid application

First, open spreadsheet and insert Google Form from spreadsheet to linkage both.

Procedure：［Insert］→［Form］

when clicking “Form” button, spreadsheet creates sheet for summarizing the answer from form and Google Form with binding spreadsheet.

Enter the following information in the form (these are examples only, so please enter the information appropriate for each company. However, in that case, you also need to change the script itself)

Employee Num　：［Short answer］
Name　　　：［Short answer］
Date of Paid leave request：［Date］
Days　：［Multiple Choice(all day/ AM/ PM) ］
Approver：［Short answer］（However, this content is not used this time）

With the above settings, a sheet is automatically created in the spreadsheet with the answers collected, and the first row contains the element set as the question.

Setting task2：Set the required elements on the spreadsheet sheet

We will slightly modify the sheet for which the Form answer aggregation sheet has been completed.

That said, there is nothing special, only changing the sheet name and creating a simple employee master.

Changing sheet name

sheetName：【Form Responses 1】→【有給申請管理】(*)

Later, sheet name as variable is using in script, so you can use any name here, but you will also need to change the corresponding script.

(*) original script is based on Japanese, so sheetName need setting Japanese. if you want to change from ja to en, please modify also script relatively.

Creating simple employee ledger（sheet_name：mail_adress_master)

This is a sheet to identify the person who applied for the form (applicant) and send it to the approver by e-mail. The purpose is to match the applicant and the approver and to get the email address of the approver.

ColumnA：employee ID
ColumnB：Employee Name
ColumnC：Approver Name
ColumnD：e-mail address of approver

That’ all for Setting task 2.

Setting task3：Set up Google Form URL to call from Slack Bot

Now, finally, Slack Bot settings.

As for SlackBot, this time we only need to add a customized automatic response, so I hope you can refer to the following Slack Official article for reference.

Slackbot 入門

In my case, when I chat about “;有給申請” on slack, slack bot will reply URL of Google Form as setting above.

actual response as above image.

Finally,

This time, I explained various settings that are the premise of the code. First of all, try this case pattern, and if you get used to it, I hope you can add more customizations.