spreadsheet | 근태 기록(출근 퇴근 시간) 구글 시트 만들기 (자동화)

 

출근(출석, 입장, 입실), 퇴근(퇴실, 퇴장), 외출 시간을 기록하는 스프레드시트를 만들었다

단축키를 누르면 시간이 기록되고, 총 몇 시간 근무(집중, 공부 등)를 했는지 계산한다

 

방법은 간단하다.

 

1. 구글 시트에 시간을 기록하는 자동화 버튼을 만든다
2. 매크로를 이용해 단축키를 설정한다
3. 결과를 계산하는 수식을 넣는다

 

---

 

1. 자동화 버튼 만들기

 

구글 시트 상단에 2가지 버튼을 만들 것이다

근태 기록 버튼을 누르면 출/퇴근, 외출 버튼을 고를 수 있다

 

기입 예시

 

버튼을 설명하자면

• 출퇴근 : 현재 셀에 현재 시간을 입력한다 (HH:mm 꼴)
• 외출 : 현재 셀에 외출 시작 ~ 끝을 입력한다 (HH:mm - HH:mm 꼴) - 외출 시작과 끝 모두 같은 셀에서 버튼을 누른다

 

추후 제일 밑에 있는 셀(위 이미지 표에서 근무 시간 오른쪽)에 수식을 넣어 오늘 하루 8시간 기준 추가 근무 시간을 자동으로 계산할 것이다.

사진 속 계산 결과를 설명하자면, 점심시간 1시간+외출 1시간이 계산되어 -1:00이 나왔다. 평균 하루 근무 시간인 8시간보다 1시간이 부족하게 근무를 한 것이다.

 

 

이제 버튼을 만들어 보자

 

1) Apps Script 실행

 

먼저 확장 프로그램을 실행해야 한다.

 

구글 시트 기준으로, 위쪽 상단 [확장 프로그램] - [Apps Script]를 누르고, 동의 과정을 거쳐 Apps Script를 켜준다. 동의는 나중에 철회하지 않는 이상 처음 한 번만 하면 된다.

 

 

그럼 이렇게 빈 스크립트 화면이 보일 것이다. 이곳에 앞으로 코드를 작성한다.

 

 

2) 코드 작성

 

함수에 대한 구체적인 설명은 아래 <코드 자세히 보기>에 있다

↓↓↓↓↓↓↓↓

첫 번째, 상단에 버튼을 만드는 함수

 

function onOpen() {
  var ui = SpreadsheetApp.getUi();
  ui.createMenu('근태 기록')
      .addItem('출퇴근 (Ctrl+Alt+Shift+1)', 'recordCurrenttime')
      .addItem('외출 (Ctrl+Alt+Shift+2)', 'recordOutgoing')
      .addToUi();
}

 

출퇴근 (Ctrl+Alt+Shift+1) 이런 식으로 이름을 지어서 나중에 단축키 연결할 때 까먹어도 다시 쓸 수 있게 했다.

단축키 연결은 지금은 안 된다. 버튼 생성을 마치고 매크로 기능을 써서 연결할 것이다.

 

 

두 번째, 현재 시간을 받아오는 함수

 

// 현재 시간을 HH:mm 형식으로 반환하는 함수
function getCurrentTime() {
  var now = new Date();
  return now.getHours().toString().padStart(2, '0') + ":" + 
         now.getMinutes().toString().padStart(2, '0');
}

 

현재 시간을 받아서 우리가 자주 써서 이해하기 쉬운 HH:mm 꼴로 바꿔준다

 

 

세 번째, 출퇴근 버튼을 담당하는 함수

 

// 출퇴근 기록
function recordCurrenttime() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var activeCell = sheet.getActiveCell();
  activeCell.setValue(getCurrentTime());
}

 

위에서 만든 getCurrentTime() 함수를 이용해서 현재 셀에 시간이 입력된다

 

 

네 번째, 외출 버튼을 담당하는 함수

 

// 외출 기록
function recordOutgoing() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var activeCell = sheet.getActiveCell();
  var currentValue = activeCell.getValue();
  
  if (currentValue && currentValue.toString().trim() !== "") {
    // 기존 값이 있는 경우, 마지막에 시간 추가
    activeCell.setValue(currentValue + getCurrentTime());
  } else {
    // 기존 값이 없는 경우, 시간과 하이픈 추가
    activeCell.setValue(getCurrentTime() + " - ");
  }
}

 

빈 셀에 이 함수를 실행하면 현재 시간이 HH:mm - 꼴로 입력된다.

같은 셀에서 이 함수를 다시 실행하면, 셀이 비어있지 않기 때문에 앞에 있는 내용을 그대로 유지하면서 뒤에 현재 시간(HH:mm)이 추가된다. 결과적으로 HH:mm - HH:mm 꼴이 된다.

↑↑↑↑↑↑↑↑

 

처음에는 시간 차를 계산하는 기능도 넣으려고 했다. 그러나 구글 시트에 버그가 있는지 Date 값을 내가 정한 시간이 아닌 전혀 다른 시간으로 받아오는 이슈가 있었다. mm부터가 전혀 달랐기 때문에 표준 시간대 문제도 아니었다.

 

아쉽지만 이번에는 시트에 직접 계산 수식을 적는 것으로 합의를 봤다.

 

전체 코드

function onOpen() {
  var ui = SpreadsheetApp.getUi();
  ui.createMenu('근태 기록')
      .addItem('출퇴근 (Ctrl+Alt+Shift+1)', 'recordCurrenttime')
      .addItem('외출 (Ctrl+Alt+Shift+2)', 'recordOutgoing')
      .addToUi();
}

// 현재 시간을 HH:mm 형식으로 반환하는 함수
function getCurrentTime() {
  var now = new Date();
  return now.getHours().toString().padStart(2, '0') + ":" + 
         now.getMinutes().toString().padStart(2, '0');
}

// 출퇴근 기록
function recordCurrenttime() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var activeCell = sheet.getActiveCell();
  activeCell.setValue(getCurrentTime());
}

// 외출 기록
function recordOutgoing() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var activeCell = sheet.getActiveCell();
  var currentValue = activeCell.getValue();
  
  if (currentValue && currentValue.toString().trim() !== "") {
    // 기존 값이 있는 경우, 마지막에 시간 추가
    activeCell.setValue(currentValue + getCurrentTime());
  } else {
    // 기존 값이 없는 경우, 시간과 하이픈 추가
    activeCell.setValue(getCurrentTime() + " - ");
  }
}

 

이 코드를 아까 열어둔 Apps Script 창에 복붙한다.

 

 

---

 

2. 매크로를 이용한 단축키 설정

 

화면에서 직접 버튼을 눌러도 되지만 키보드로 끝낼 수 있도록 단축키를 설정하겠다.

 

상단 메뉴 [확장 프로그램] - [매크로] - [매크로 기록]

 

구글 시트 기준으로, 위쪽 상단 메뉴에서 [확장 프로그램] - [매크로] - [매크로 기록]을 누른다.

 

하단에 매크로 설정이 뜬다

 

하단 설정에서는 '상대 참조 사용'을 선택한다. 그래야 그때그때 현재 내가 클릭한 셀에 단축키가 적용될 것이다.

이어서 위에서 만들어 둔 버튼을 하나 실행한 다음, 저장을 누른다.

 

 

저장하면서 단축키도 설정한다.

버튼마다 이 작업을 반복하면 된다.

 

 

---

 

3. 결과 수식 넣기

 

근무 시간을 계산해 보자

 

예를 들어 셀 A7에 결과를 넣는다고 할 때,

=(TIMEVALUE(A6)-TIMEVALUE(A4)) - IF(isblank(A5), 0, (TIMEVALUE(MID(A5, 9, 5))-TIMEVALUE(MID(A5, 1, 5)))) - 9/24

• TIMEVALUE(A6)-TIMEVALUE(A4)
  • 퇴근 시간(A6)에서 출근 시간(A4)을 뺀다
• IF(isblank(A5), 0, (TIMEVALUE(MID(A5, 9, 5))-TIMEVALUE(MID(A5, 1, 5))))
  • 외출 셀(A5)에 값이 있으면 계산하고, 값이 없으면 0으로 계산한다
• TIMEVALUE(MID(A5, 9, 5))-TIMEVALUE(MID(A5, 1, 5))
  • 외출 셀(A5)에서 - 를 기준으로 오른쪽 시간에서 왼쪽 시간을 뺌으로써 총 외출 시간을 계산한다
• 9/24
  • 평균 하루 근무 시간 8시간에 점심시간 1시간을 합한 9시간을 빼줌으로써 그날 초과 근무 시간을 계산한다. 기본 근무 시간을 다르게 두고 싶다면 이 값을 조절하면 된다.
  • TIMEVALUE는 24시간으로 나눈 값이기 때문에 단위를 맞추기 위해 9시간도 24로 나눠야 한다.

 

 

마지막 이번주 총 근무 시간 계산이다.

 

 

 

여기서는 2가지 설정을 해야 한다.

 

먼저, 이번주 5일의 초과 근무 시간 + 기본 40시간을 더하는 수식을 입력한다.

= sum(A7:E7) + 40/24

 

다음으로, 셀 서식을 바꿔준다.

 

구글 시트 상단 메뉴에서 [서식] - [숫자] - [맞춤 숫자 형식]으로 들어간 뒤, 서식에 [h]:mm을 입력한다. 이래야 24시간이 넘는 시간도 시간 부분이 그대로 표시가 될 것이다.

 

 

 

이 글에서는 근태를 예시로 만들었지만, 하루 공부 시간, 집중 시간 계산 등 다른 용도로도 만들어 볼 수 있겠다.