How to create a safety confirmation system with spreadsheet(GAS) for free.

日本語Verは👉こちら

---

Thank you for reading 🌸
I work as a HR member at a startup company with about 100 employees.
My goal is to assist people who would like to expand their business in society.
For this goal, I'd like to provide useful information in this Note :)

---

we pray for those affected by the Noto Peninsula earthquake in  January of this year.

the reason I write this article.

no employees at our company were directly affected by the earthquake, but it occurred on January 1st, we were unable to take  appropriate measures to confirm the safety of each employee.

We didn't have the proper line of communication beforehand, so managers and leaders tried to reach their subordinates with the HR department. 

Therefore, we discussed introducing a safety confirmation system that would function properly in the event of a disaster.

There are many services online, but we would like to keep initial and monthly costs low.
At that time, we were able to make a similar system by combining spreadsheets and GAS

In this article, we will explain how to create the system .

Overview of this safety confirmation system

when you press the button on the spreadsheet, all members on the sheet's list will receive an email.
The email content is placed in another Google Doc.

When you press the button on the spreadsheet, all members on the sheet's list will receive an email.
The email content will be written in a separate Google Doc.
( image of mail content )

Names of the list will be put In the field "{姓}{名}"(last and first name)

By including the URL of the form for responding to the safety status in the mail, each employee can enter their status.
( image of form for responding )

( image of an email )

In addition, by including a URL of the sheet to see each employee's response, all employees can share their status each other.

Let's take a look at each step !

How to make this system

1.create a spreadsheet
Write email address and first and last name on the sheet.
Write the header in the 1st row as shown below.

create a checkbox to send an email.
I use a checkbox to send a mail aside from a button because the button doesn't work when I press it by smartphone.
Checkbox can be displayed by selecting 'Checkbox' from  'Insert' in the 'Toolbar' .

2.Write the mail body in Google Docs.
When sending an email, the contents of the spreadsheet will be entered in the format  '{姓}{名}'. (last name , first name).

3.create a Google form to respond to  safety situation.
you can choose what to ask.
It's a good idea to ask about the family situation and whether employees will be able to come to the office.

4.Create GoogleAppsScript(GAS)
Please copy and paste the program below.
This program is written in 'Apps Script'.
You can display it by selecting 'Extensions' on the toolbar, and then  selecting 'Apps Script'.

For Document ID, enter the Document ID of your Google Doc above.

(Document ID)

Enter your Google Account email address.

(Program)

function myFunction() {

  var sheet = SpreadsheetApp.getActiveSheet(); // シートの取得
  var lastRow = sheet.getLastRow(); // 最終行の取得
  
  var DOC_ID = 'XXXXXXXXXXXXXXXXXXXX'; // 文面のドキュメントID
  var doc = DocumentApp.openById(DOC_ID);
  var docText = doc.getBody().getText(); // テキストのドキュメントを取得

  var subject = '安否状況の提出依頼'; // メールタイトル
  var strFrom = 'XXXXXXXX.com'; // 送信元メール
  var sterSender = 'XXX社'; // 送信者名
   
  for(let i = 2; i <= lastRow; i++){

    var mailAddress = sheet.getRange(i, 1).getValue();
    var lastName = sheet.getRange(i, 2).getValue();
    var firstName = sheet.getRange(i, 3).getValue();
  
    
    var body = docText.replace('{{姓}}',lastName).replace('{{名}}',firstName);
    
    try{
      GmailApp.sendEmail(
        mailAddress,
        subject,
        body, {
          from: strFrom,
          name: sterSender
        }
        
        );
    }catch(e){
      console.log('エラーを検知しました。');
      console.log('エラー内容：'+e.message);
    }

  }
} 

// checkButton
function onEdit() {
  var sheet = SpreadsheetApp.getActiveSheet(); // シートの取得
  // ONになったとき
  if(sheet.getRange('E1').getValue()==1){
    this.myFunction()
  }
  sheet.getRange('E1').uncheck();  // 終わったらチェックを外す
}

(Set a trigger)
In order to start this tool on smartphone, you need to set a trigger.
Select the 'Triggers' menu in the left menu of Apps Script.

Select 'Add Trigger' in the bottom right. 

select 'on Edit' under 'function', and 'on edit' under 'event type'.

That's all !
If you check the box, members of the list will receive an email.

Summary

There are many services for medium-sized companies on the internet, but they are  a bit expensive.
So, if one of them can be created for free, I think it would be good for those companies!

---

Thank you for reading to the end !
Please share your comments :)

---