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

Feb 10, 2023

thumbnail
output

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

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

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

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

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

연차 자동화 시트(링크)

make-a-copy

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

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

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

google-sheets-workflow

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

  1. 먼저 슬랙 워크스페이스에 좌측 상단을 클릭하고 Tools > Workflow Builder를 클릭합니다.
slack-workflow-builder
  1. Create 버튼을 클릭하여 새로운 Workflow를 만듭니다.
create-workflow
  1. Shortcut를 클릭하고 Bot을 사용할 채널과 봇 이름을 설정합니다.
shortcut
  1. Add a step를 클릭하고 Send a form을 클릭합니다.

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

send-a-form
  1. Add a step > Update a spreadsheet row를 클릭한 뒤, 위에서 만든 연차 관리 시트를 연동해줍니다.
update-spreadsheet
  1. 업데이트할 row를 선택하기 위해 inserted라는 column을 선택하고, o라는 값을 입력합니다. (최상단 inserted column에 o를 입력해줘야 합니다. inserted 열에 o값이 적혀진 행을 기준으로 새로운 값을 붙여넣기 때문입니다.)
update-spreadsheet-row
  1. 해당 row에 Form을 작성한 사람의 이름, 휴가 종류, 휴가 시작일, 휴가 끝일을 해당하는 column에 입력해주면 됩니다. Insert a variable을 통해서 Form의 값, 입력한 사람의 정보를 불러올 수 있습니다.
    (Person who submitted form에서 Name을 선택해주셔야 해당 사람의 이름을 불러올 수 있습니다! -> mention의 경우에는 해당 유저의 user_id가 표시되게 됩니다.😭)
update-columns
  1. Add a step > Send a message를 클릭한 뒤, 연차 사용 결과를 알려주는 메시지를 작성합니다.
send-a-message

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

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

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

  1. Extensions > Apps Script를 클릭한 뒤, Code.gs를 클릭합니다.
apps-script
  1. 새로 만드시는 경우나 Apps Script가 옮겨져있지 않은 경우에는 아래 코드를 복사해서 붙여넣기 합니다.
Code.gs
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는 다시 기록되지 않도록 합니다.)

  1. Save Project를 클릭한 뒤, 왼쪽 Nav Bar에서 Triggers를 클릭합니다.
trigger
  1. Add Trigger를 클릭한 뒤, 아래와 같이 설정합니다.
trigger-setting

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

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

verification-error

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

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

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

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

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

  1. 먼저 Dashboard에 회사 구성원들의 이름을 적어줍니다.
  • Tip. 입사일을 기록해두면 이후 현재까지 생성된 연차를 구해줄 수 있습니다.
  • Tip 2. 위의 예시 스프레드시트처럼 별도의 시트로 관리해주면 다른 대시보드를 만드는 경우에도 편합니다.
members
  1. VLOOKUPSORT 함수를 이용하여 Vacation Log 시트에 있는 연차 중 가장 최근에 사용한 연차를 찾아줍니다.
=VLOOKUP(
    // 이름이 담긴 셀
    A2,
    // Vacation_Log 시트의 범위, 최신 값을 가져오기 위해 날짜로 내림차순 정렬
    SORT('Vacation Log'!$B$2:$E, 3, FALSE),
    // 가져오고 싶은 정보가 있는 열의 순서
    2,
    // FALSE일 경우 정확하게 일치하는 값만을 반환
    FALSE
)

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

recent_vacation

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

=IFERROR(
    VLOOKUP(
        A2, 
        SORT('Vacation Log'!$B$2:$E, 3, FALSE), 
        2, 
        FALSE
    ),
    // 위의 값이 에러일 때 출력할 값
    ""
)
if_error
  1. FILTER 함수를 이용하여 해당 사람이 연차를 사용한 기록을 가져옵니다.
=FILTER(
    // Vacation_Log 시트의 범위
    'Vacation Log'!$B$2:$G,
    // 이름이 담긴 셀
    'Vacation Log'!$B$2:$B=A2
)
filter
  1. INDEX 함수를 이용하여 연차 사용 일수 column만 가져옵니다.
=INDEX(
    // FILTER 함수를 통해 가져온 범위
    FILTER(
        'Vacation Log'!$B$2:$G,
        'Vacation Log'!$B$2:$B=A2
    ),
    // 모든 row를 가져오기
    0,
    // 가져올 column의 순서
    6
)
index
  1. 해당 범위에 SUM 함수를 적용하여 연차 사용 일수를 합산합니다. (내가 SUM을 탈 수 있는 곳은 여기밖에 없어...)
=SUM(
    INDEX(
        FILTER(
            'Vacation Log'!$B$2:$G,
            'Vacation Log'!$B$2:$B=A2
        ),
        0,
        6
    )
)

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

sum

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

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

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

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

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

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

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

4개의 댓글

이용선

7 months ago

안녕하세요 이번에 글또8기에 참여하게된 리오입니다. 저도 자동화에 관심이 많아서 여러가지 방안을 살펴보고 있습니다. 직접 자동화를 하시다니 대단하네요 ㄷㄷ. 요즘 자동화에 관심이가서 Zapier를 살펴보는데 이렇게 커스텀 하기 쉽지 않더라구요. 좋은 글 감사합니다!

Chavo Kim Profile

Chavo Kim

7 months ago

인녕하세요 리오님 🙇‍♂️ 생각보다 구글 워크스페이스와 슬랙에서 지원해주는 기능들이 많아서 요구사항만 분명하다면 해당 플랫폼을 쓰는 것들이 직관적인 경우가 많았던 것 같습니다 ☺️ 저도 자동화와 일의 효율성을 높이는데에 관심이 많아 관련된 얘기 많이 할 수 있었으면 좋겠네요 :) 댓글 정말 감사드립니다. 🙌

박지수

6 months ago

안녕하세요! 너무 좋은 시스템 감사합니다! 저희 회사가 구글과 슬랙을 사용해서 알려주신것 처럼 연차관리를 하려고 진행하는데, Update a spreadsheet row 부분에서 오류가 발생한것 같습니다 ㅠㅠ 연차 신청하면 시트에 시트별로 구분이 안되고 inserted 행에 <@U02KH9V9WJX>연차2023-03-172023-03-17테스트트트트 이렇게만 표기가 되네요... 혹시 해결 방법 알려주실수 있으실까요?

Chavo Kim Profile

Chavo Kim

6 months ago

안녕하세요 지수님 :) 회사 시스템에 도움이 되었다니 다행입니다. Update a spreadsheet row를 사용할 때 1. Find a row를 통해 insert할 row를 찾으신 다음 2. 넣고자 하는 값들을 Update these colums에서 column에 알맞은 값들을 넣어주면 됩니다! 해당 내용이 본문에 자세히 적혀있지 않아서 어려움을 겪으신 것 같습니다. 😭 본문에 내용 보충했으니 확인해주시면 됩니다. 또 어려움이 있으시면 말씀해주세요. :) 알려주셔서 감사합니다!

박지수

6 months ago

창작자님 답변 너무 감사합니다! 덕분에 셋팅은 잘 되었는데, 다른 오류가 발생하였습니다 ㅠㅠ 제가 슬랙 한국어 버전이라 메뉴 이름을 한국어로 말씀 드리는점 죄송합니다 ㅠㅠ 제작 완료된 워크플로 편집에서, 활동 매뉴로 들어가면, [워크플로 오류]라고 나오면서 오류된 항목에 아래와 같이 영어로 표기 되어 있습니다. Could not find a row that matches the criteria search value for column "Inserted" Inserted 셋팅 시 o 라고 표기해놓았는데, 이 부분이 문제가 생긴것 같습니다... 또 문의 드리기 죄송하지만 ㅠㅠ 답변 주시면 감사하겠습니다!!

Chavo Kim Profile

Chavo Kim

6 months ago

해당 에러는 구글 스프레드시트 Vacation Log에 'o'로 표시된 열이 보이지 않아서 생긴 문제인 것 같습니다. (위의 예시 시트에서처럼 마지막 행은 o로 처리해주어야 합니다.) 혹시 Inserted 컬럼에 o로 표시된 항목이 있을까요?

김다연

6 months ago

안녕하세요 :) 회사에서 구글과 슬랙을 이용하여 올려주신 내용은 연차관리에 정말 많은 도움이 되었습니다! 혹시 연차 변경이나, 취소 관련 자동화 방법도 있는지 문의 드려요 !!

Chavo Kim Profile

Chavo Kim

6 months ago

안녕하세요 다연님 :) 연차 관리에 도움이 되었다니 기쁩니다. 연차 변경이나 취소 관련해서도 로직을 추가할까 고민을 했었지만, 너무 복잡해질 것 같아 변경이나 취소는 직접 엑셀에 수기로 수정했었습니다 😭 (슬랙 빌더의 역할도 결과적으로는 엑셀에서 글을 써주는 과정을 자동화하는거라 로직이 바뀌진 않습니다.) 간단한 아이디어로는 슬랙 빌더에서 연차 날짜, 이름으로 검색해서 해당 row를 업데이트하는 식으로 짜볼 수 있을 것 같습니다. 👀