Slack과 Google Docs로 연차관리 자동화하기

코드트리 법인을 설립하면서

1. 구성원들이 생성된 연차를 확인할 수 있고,
2. 간단한 Form을 통해 연차를 사용하고,
3. 연차를 Calendar에서 확인할 수 있고,
4. 남은 연차와 연차를 사용한 내역을 확인할 수 있는

시스템의 필요성을 느꼈습니다.

Flex라는 좋은 HR 관리 SaaS 제품이 있었지만, 연차 관리 시스템만 필요했기 때문에 직접 구현하기로 했습니다. (연차관리 시스템은 사서 쓰세요...) 기존에 쓰고 있던 슬랙과 구글 스프레드시트를 이용해서 시스템을 설계했습니다.

연동할 구글 스프레드시트를 아래 링크에서 복제할 수 있습니다.

연차 자동화 시트(링크)

슬랙에서는 Workflow Builder라는 기능을 제공하여 코드 없이 자동화 봇을 만들 수 있습니다.

(Workflow Builder는 Slack 무료 버전에서는 사용할 수 없습니다. 😭)

Workflow Builder에서 Spreadsheet에 접근할 수 있도록 하기 위해 Google Sheets for Workflow Builder를 설치합니다(Add to Slack을 클릭해서 진행합니다.).

이번에는 Workflow Builder를 사용하여 연차에 필요한 정보를 입력받는 Form을 만들고, 이를 구글 스프레드시트와 연동합시다. :)

1. 먼저 슬랙 워크스페이스에 좌측 상단을 클릭하고 Tools > Workflow Builder를 클릭합니다.

2. Create 버튼을 클릭하여 새로운 Workflow를 만듭니다.

3. Shortcut를 클릭하고 Bot을 사용할 채널과 봇 이름을 설정합니다.

4. Add a step를 클릭하고 Send a form을 클릭합니다.

연차에 필요한 정보를 입력받기 위한 Form을 만듭니다. 향후 Dashboard를 만들기 위해 휴가 종류(연차, 반차), 휴가 시작일, 휴가 끝일은 필수적으로 받고 나머지 정보는 필요한대로 추가하면 됩니다. :)

5. Add a step > Update a spreadsheet row를 클릭한 뒤, 위에서 만든 연차 관리 시트를 연동해줍니다.

6. 업데이트할 row를 선택하기 위해 inserted라는 column을 선택하고, o라는 값을 입력합니다. (최상단 inserted column에 o를 입력해줘야 합니다. inserted 열에 o값이 적혀진 행을 기준으로 새로운 값을 붙여넣기 때문입니다.)

7. 해당 row에 Form을 작성한 사람의 이름, 휴가 종류, 휴가 시작일, 휴가 끝일을 해당하는 column에 입력해주면 됩니다. Insert a variable을 통해서 Form의 값, 입력한 사람의 정보를 불러올 수 있습니다.
   (Person who submitted form에서 Name을 선택해주셔야 해당 사람의 이름을 불러올 수 있습니다! -> mention의 경우에는 해당 유저의 user_id가 표시되게 됩니다.😭)

8. Add a step > Send a message를 클릭한 뒤, 연차 사용 결과를 알려주는 메시지를 작성합니다.

이제 연차에 대한 Form을 받고 스프레드시트에 저장하는 Workflow가 완성되었습니다. :) 이렇게 입력된 정보를 바탕으로 Calendar를 업데이트해주는 기능을 만들어보겠습니다.

위에서 제공해드린 스프레드시트를 복제하셨다면 아마 Apps Script가 이미 생성되어 있을겁니다.

Apps Script란 Javascript로 구글 제품(스프레드시트, 캘린더, 메일 등...)을 자동으로 제어할 수 있는 기능입니다.

1. Extensions > Apps Script를 클릭한 뒤, Code.gs를 클릭합니다.

2. 새로 만드시는 경우나 Apps Script가 옮겨져있지 않은 경우에는 아래 코드를 복사해서 붙여넣기 합니다.

function VacationAutoexport() {
  const spreadsheet = SpreadsheetApp
    // sheet ID 입력 (https://docs.google.com/spreadsheets/d/your_sheet_id)
    .openById("your_sheet_id")
    .getSheetByName("Vacation Log");

  // 연차 정보를 기록할 Calendar ID 입력 (https://docs.simplecalendar.io/find-google-calendar-id/)
  const calendarId = "calendar_id";
  const eventCal = CalendarApp.getCalendarById(calendarId);

  const range = spreadsheet.getRange("A2:E");
  const values = range.getValues();
  const signups = values.filter(value => value[1] && value[2] && value[3] && value[4]);

  for (x=signups.length - 1; x>=0; x--) {    
    const shift = signups[x];

    if(shift[0] === 'v')
      break;

    const title = shift[1] + " - " + shift[2];
    const startDate = new Date(shift[3]);
    const endDate = new Date(shift[4]);

    if(startDate.getTime() === endDate.getTime()){
      eventCal.createAllDayEvent(title, startDate);
    } else {
      endDate.setDate(endDate.getDate() + 1);
      eventCal.createAllDayEvent(title, startDate, endDate);
    }

    const cell = range.getCell(x + 1, 1);

    cell.setValue('v');

    const cell2 = range.getCell(x + 2, 1);

    cell2.setValue('o');
  }
}

o 라고 입력된 row에 해당하는 정보를 Calendar에 기록하고, 모두 기록한 뒤에는 inserted를 v로 변경합니다. (이를 통해서 이미 기록된 row는 다시 기록되지 않도록 합니다.)

3. Save Project를 클릭한 뒤, 왼쪽 Nav Bar에서 Triggers를 클릭합니다.

4. Add Trigger를 클릭한 뒤, 아래와 같이 설정합니다.

해당 스프레드시트가 변경될 때마다 Vacation Export 함수를 실행합니다.

Save를 클릭하면 권한을 승인하기 위한 로그인 화면이 뜹니다.

로그인을 하면 위와 같은 에러가 뜰텐데, 당황하지 않고 Advanced > Go to Vacation System Automation (unsafe)를 클릭합니다. 그러면 권한 제공 승인을 진행할 수 있습니다. :)

이제 슬랙에서 Form을 입력하면 연차 정보가 자동으로 기록되는 시스템이 완성되었습니다.

Vacation Log 시트에 있는 정보를 이용하여 각 구성원이

• 제일 최근에 사용한 연차
• A일부터 B일까지 사용한 연차의 수

를 보여주는 Dashboard를 만들어보겠습니다.

1. 먼저 Dashboard에 회사 구성원들의 이름을 적어줍니다.

• Tip. 입사일을 기록해두면 이후 현재까지 생성된 연차를 구해줄 수 있습니다.
• Tip 2. 위의 예시 스프레드시트처럼 별도의 시트로 관리해주면 다른 대시보드를 만드는 경우에도 편합니다.

2. VLOOKUP과 SORT 함수를 이용하여 Vacation Log 시트에 있는 연차 중 가장 최근에 사용한 연차를 찾아줍니다.

=VLOOKUP(
    // 이름이 담긴 셀
    A2,
    // Vacation_Log 시트의 범위, 최신 값을 가져오기 위해 날짜로 내림차순 정렬
    SORT('Vacation Log'!$B$2:$E, 3, FALSE),
    // 가져오고 싶은 정보가 있는 열의 순서
    2,
    // FALSE일 경우 정확하게 일치하는 값만을 반환
    FALSE
)

이 함수를 통해 구성원 중 가장 최신의 연차 휴가 정보를 가져올 수 있습니다.

(주의) 이때 연차를 한번도 사용하지 않은 경우에는 #N/A가 출력되는데, 이를 없애기 위해서는 IFERROR 함수를 사용해서 해당 경우에는 공백을 출력하도록 할 수 있습니다.

=IFERROR(
    VLOOKUP(
        A2, 
        SORT('Vacation Log'!$B$2:$E, 3, FALSE), 
        2, 
        FALSE
    ),
    // 위의 값이 에러일 때 출력할 값
    ""
)

3. FILTER 함수를 이용하여 해당 사람이 연차를 사용한 기록을 가져옵니다.

=FILTER(
    // Vacation_Log 시트의 범위
    'Vacation Log'!$B$2:$G,
    // 이름이 담긴 셀
    'Vacation Log'!$B$2:$B=A2
)

4. INDEX 함수를 이용하여 연차 사용 일수 column만 가져옵니다.

=INDEX(
    // FILTER 함수를 통해 가져온 범위
    FILTER(
        'Vacation Log'!$B$2:$G,
        'Vacation Log'!$B$2:$B=A2
    ),
    // 모든 row를 가져오기
    0,
    // 가져올 column의 순서
    6
)

5. 해당 범위에 SUM 함수를 적용하여 연차 사용 일수를 합산합니다. (내가 SUM을 탈 수 있는 곳은 여기밖에 없어...)

=SUM(
    INDEX(
        FILTER(
            'Vacation Log'!$B$2:$G,
            'Vacation Log'!$B$2:$B=A2
        ),
        0,
        6
    )
)

이렇게 각 구성원 별로 총 연차 사용 일수도 구할 수 있습니다. ☺️

위 스프레드시트에 이미 회계연도 기준에 따른 연차 계산 방법으로 연차를 계산해주는 함수가 있습니다. 내용이 복잡하여 여기서는 다루지 않겠습니다. 😅

아래 기준으로 연차를 계산해주는 함수를 만들었고, 혹여 요청이 있을 경우에 자세히 써보도록 하겠습니다. :)

회계연도 기준에 따른 연차 계산 에 따르면 근로기간 1년 미만일 때 생기는 연차와 회계일자(통상 1월 1일)에 발생하는 연차 유급휴가를 다르게 관리해주어야 합니다.

1. 회계연도에 발생하는 연차
- 회계 일자(통상 1월 1일)에 발생
- 내년 1월 1일에 일괄 만료
- 작년도 기준 1년간 80% 이상 근로
- 계속 근로 기간이 3년 이상인 경우 2년 간격으로 1일씩 증가 (최대 25일)

2. 근로기간 1년 미만일 때 생기는 연차
- 입사일 기준으로 1개월 만근 시 1일씩 발생(최대 11개)
- 발생일 기준 1년 뒤 사용하지 않으면 만료

기존에 있는 쓰고있던 솔루션들로 자동화하면서 슬랙과 구글 스프레드시트에 대해서 더 깊게 알게 되었습니다. 😊 이를 이용해서 구성원들 연차와 한국 휴일을 엑셀 달력에 뿌려주는 함수라든지... 하루마다 생일인 글또분들을 알려주는 슬랙봇이라든지... 편하게 만들 수 있게 되었습니다. 🙌

저희와 비슷한 고민을 하고 계신 소규모 스타트업분들께 도움이 되었으면 좋겠네요. 모두 화성으로 갑시다! 🚀