구글앱스크립트(Google Apps Script)로 직원 근태 관리 자동화하기 (무료 코드 및 파일제공)

📊 Google Apps Script로 근태 관리 자동화하기

🔍 이 스크립트를 만든 이유

과거 Google 프로젝트에서 근무할 때, 자체 AUX System을 통해 직원들의 근무 상태(Available, Break, Lunch 등)를 기록하고 관리할 수 있었습니다. 하지만 새로운 부서에는 동일한 기능이 없어 직원 생산성(Productivity) 및 활용률(Utilization) 분석이 어려운 상황이었습니다.

 

이 문제를 해결하기 위해 고민하던 중, Google Apps Script(GAS)를 활용하여 직접 시스템을 개발하기로 결정했습니다. 이 프로젝트는 ChatGPT와 7일 이상 씨름하며 최적의 코드 구조를 설계한 결과물입니다.

이 스크립트는 개인 또는 직원들의 업무 진행 상태를 기록하고, 각 상태의 시작/종료 시간 및 지속 시간을 계산하여 업무 효율성을 분석할 수 있도록 설계되었습니다.

 

단, 🔖Google Sheets는 무료이며 웹 애플리케이션 기반으로 운영되기 때문에 Google 서버의 상태가 불안정하거나 다수의 사용자가 동시에 입력할 경우 데이터 입력이 누락될 가능성이 있습니다. 이 점을 참고하여 사용하시기 바랍니다.

❐ 기능 설명

• Google Sheets에서 (Custom) 메뉴를 추가하여 쉽게 상태 변경
• 직원의 상태(Start, Available, Break, Lunch, Training, Coaching, End) 자동 기록
• Training 및 Coaching 입력 시 사유(reason) 입력
• 각 상태의 시작 시간(Start Time), 종료 시간(End Time), 지속 시간(Duration) 자동 계산

Apps Scritp_Custom Button 만들기

공식 사이트 (Google Sheets - Menu 만들기)

https://developers.google.com/apps-script/guides/menus?hl=ko

Google Workspace의 맞춤 메뉴  |  Apps Script  |  Google for Developers

❐ 구글 시트 구조 설명

데이터 저장 구조 (Google Sheets 열 구성)

열 (Column)	내용
A	이메일 (Email)
B	상태 (Status: Start, Available, Break 등)
C	사유 (Reason: Training/Coaching 입력 시 필요)
D	시작 시간 (Start Time)
E	종료 시간 (End Time)
F	지속 시간 (Duration)

 

 

G ~ I 로 Date, Week, Month 를 함수로 계산 (함수가 이해가 안된다면 아래 포스팅을 참고 바랍니다.) 

구글 시트 - 만능 날짜 변환 공식 (Query + Arrayformula | Date → Week(주차), Month, Quarter 만들기)

구글 시트 - 만능 날짜 변환 공식 (Query + Arrayformula | Date → Week(주차), Month, Quarter 만들기)

 

구글 시트 구조 및 날짜 설정 함수

❐ 코드 & 초기 설정

아래 코드블록 오른쪽 상단에 복사 버튼을 클릭하세요! 📋

 

필요한 부분이 있으면 아래 코드를 자유롭게 수정하여 사용하세요.

코드가 이해되지 않는다면 ChatGPT 등을 활용하여 손쉽게 수정할 수 있습니다. 😊

// Google Sheets에서 "Agent Status" 시트 사용
var sheetName = "Agent Status";

// Google Sheets를 열 때 실행되는 함수 (UI 메뉴 추가)
function onOpen() {
  var ui = SpreadsheetApp.getUi();
  ui.createMenu('Change Status')
    .addItem('Start', 'setStatusStart')
    .addItem('Available', 'setStatusAvailable')
    .addItem('Break', 'setStatusBreak')
    .addItem('Lunch', 'setStatusLunch')
    .addItem('Training', 'setStatusTraining')
    .addItem('Coaching', 'setStatusCoaching')
    .addItem('End', 'setStatusEnd')
    .addToUi();
}

// 상태 변경을 위한 개별 함수
function setStatusStart() { setStatus("Start"); }
function setStatusAvailable() { setStatus("Available"); }
function setStatusBreak() { setStatus("Break"); }
function setStatusLunch() { setStatus("Lunch"); }

function setStatusTraining() {
  var reason = Browser.inputBox("교육 사유를 입력하세요:");
  setStatus("Training", reason);
}

function setStatusCoaching() {
  var reason = Browser.inputBox("코칭 사유를 입력하세요:");
  setStatus("Coaching", reason);
}

function setStatusEnd() { setStatus("End"); }

// 📌 직원 상태를 기록하는 메인 함수
function setStatus(status, reason) {
  var sheet = SpreadsheetApp.getActive().getSheetByName(sheetName); // 시트 가져오기
  var account = Session.getActiveUser().getEmail(); // 현재 사용자의 이메일 가져오기

  // 📌 기존 기록에서 사용자의 마지막 상태 찾기
  var data = sheet.getDataRange().getValues();
  var lastRow = -1;

  for (var i = data.length - 1; i >= 0; i--) {
    if (data[i][0] == account) { // 이메일이 같은 행 찾기
      lastRow = i + 1;
      break;
    }
  }

  // 📌 이전 기록이 없는 경우, 새 행 추가
  if (lastRow == -1) {
    sheet.appendRow([account, status, reason || "", "", "", ""]);
    return;
  }

  var currentTime = new Date(); // 현재 시간 가져오기
  var timestamp = Utilities.formatDate(currentTime, Session.getScriptTimeZone(), "yyyy-MM-dd HH:mm:ss");

  // 📌 기존 상태의 종료 시간 기록 (E 열)
  sheet.getRange(lastRow, 5).setValue(timestamp);

  // 📌 지속 시간 계산 (F 열)
  var startTime = new Date(sheet.getRange(lastRow, 4).getValue());
  var durationMs = currentTime - startTime;
  var duration = Utilities.formatDate(new Date(durationMs), "GMT", "HH:mm:ss");
  sheet.getRange(lastRow, 6).setValue(duration);

  // 📌 새로운 상태 추가 (새로운 행)
  sheet.appendRow([account, status, reason || "", timestamp, "", ""]);
}
    

 

초기 설정은 

1) Apps Script를 실행 

2) 위에 코드를 복사해서 붙여넣기 

3) 실행(Run) 버튼 클릭 및 정상 작동 확인

Apps Script 초기 설정 및 코드 복사

 

❐ 사용 방법

1. Google Sheets를 열고 확장 프로그램 → Apps Script에서 위의 코드를 붙여넣습니다.

2. 저장 후 실행하면 Google Sheets 상단 메뉴에 "Change Status" 버튼이 추가됩니다.

3. 원하는 상태를 선택하면 직원 상태가 자동으로 기록됩니다.

 

⁉️ 처음 실행하게 되면 아래와 같이 권한 승인을 해줘야 합니다. 전혀 어렵지 않으니 스크린샷을 보면서 따라해 보시기 바랍니다. 

Authorization required

권한 신청1

 

권한 신청2

 

[샘플] 구글 시트 링크 

Agent Status

 

📌 결론

이 스크립트는 7일간 ChatGPT와 협업하여 개발한 맞춤형 근태 관리 시스템으로, Google Sheets에서 근태를 기록하고 생산성을 분석할 수 있도록 합니다.

📢 이제 이 코드를 실제 업무에 적용하여 활용해 보세요!