구글 앱스크립트 7차시 - 사용자 정의 함수와 메뉴

7차시: 사용자 정의 함수와 메뉴

셀에서 사용하는 함수 만들기와 맞춤 메뉴 추가하기

📚 학습 목표

• 스프레드시트 셀에서 사용할 수 있는 사용자 정의 함수 만들기
• 맞춤 메뉴를 추가하여 스크립트 실행하기
• 대화 상자와 입력창으로 사용자와 상호작용하기
• 실전 예제: 업무 자동화 도구 모음 만들기

📖 사용자 정의 함수란?

1 사용자 정의 함수의 개념

사용자 정의 함수는 스프레드시트 셀에서 =함수명() 형태로 직접 사용할 수 있는 함수입니다. SUM(), AVERAGE()처럼 셀 수식으로 사용할 수 있습니다.

사용자 정의 함수 특징:
• 셀에서 =함수명(인자) 형태로 사용
• 반드시 return 값이 있어야 함
• 외부 서비스 접근이 제한됨 (SpreadsheetApp 일부 제한)
• 계산 결과를 즉시 반환해야 함 (시간 제한)

🚀 실습 1: 사용자 정의 함수 만들기

2 간단한 계산 함수

기본적인 사용자 정의 함수를 만들어봅시다:

/**
 * 두 수를 더합니다.
 * @param {number} a 첫 번째 숫자
 * @param {number} b 두 번째 숫자
 * @return {number} 두 수의 합
 * @customfunction
 */
function 더하기(a, b) {
  return a + b;
}

/**
 * 숫자에 부가세(10%)를 더합니다.
 * @param {number} 금액 원래 금액
 * @return {number} 부가세 포함 금액
 * @customfunction
 */
function 부가세포함(금액) {
  return 금액 * 1.1;
}

/**
 * 할인율을 적용한 금액을 계산합니다.
 * @param {number} 원가 원래 가격
 * @param {number} 할인율 할인율 (예: 20)
 * @return {number} 할인된 가격
 * @customfunction
 */
function 할인가격(원가, 할인율) {
  return 원가 * (1 - 할인율 / 100);
}

스크립트 편집기에 저장한 후, 스프레드시트에서 다음처럼 사용해보세요:
• A1에 =더하기(10, 20)
• A2에 =부가세포함(10000)
• A3에 =할인가격(50000, 20)

3 문자열 처리 함수

텍스트를 다루는 함수를 만들어봅시다:

/**
 * 이름에 "님"을 붙여줍니다.
 * @param {string} 이름 사람 이름
 * @return {string} "님"이 붙은 이름
 * @customfunction
 */
function 님붙이기(이름) {
  return 이름 + "님";
}

/**
 * 전화번호에 하이픈을 넣어줍니다.
 * @param {string} 전화번호 숫자만 있는 전화번호
 * @return {string} 하이픈이 들어간 전화번호
 * @customfunction
 */
function 전화번호형식(전화번호) {
  let 문자열 = String(전화번호);
  if (문자열.length === 11) {
    return 문자열.substr(0, 3) + "-" + 
           문자열.substr(3, 4) + "-" + 
           문자열.substr(7, 4);
  } else if (문자열.length === 10) {
    return 문자열.substr(0, 3) + "-" + 
           문자열.substr(3, 3) + "-" + 
           문자열.substr(6, 4);
  }
  return 전화번호;
}

/**
 * 주민등록번호 뒷자리를 *로 가립니다.
 * @param {string} 주민번호 13자리 주민등록번호
 * @return {string} 뒷자리가 가려진 주민번호
 * @customfunction
 */
function 주민번호마스킹(주민번호) {
  let 문자열 = String(주민번호);
  if (문자열.length === 13) {
    return 문자열.substr(0, 6) + "-*******";
  }
  return 주민번호;
}

사용 예시:
• =님붙이기("홍길동") → "홍길동님"
• =전화번호형식("01012345678") → "010-1234-5678"
• =주민번호마스킹("9001011234567") → "900101-*******"

4 실용적인 계산 함수

실무에서 유용한 함수들을 만들어봅시다:

/**
 * BMI를 계산합니다.
 * @param {number} 키 키(cm)
 * @param {number} 몸무게 몸무게(kg)
 * @return {number} BMI 지수
 * @customfunction
 */
function BMI계산(키, 몸무게) {
  let 키미터 = 키 / 100;
  let bmi = 몸무게 / (키미터 * 키미터);
  return Math.round(bmi * 10) / 10;
}

/**
 * 나이를 계산합니다.
 * @param {string} 생년월일 YYYYMMDD 형식
 * @return {number} 만 나이
 * @customfunction
 */
function 나이계산(생년월일) {
  let 문자열 = String(생년월일);
  let 년 = parseInt(문자열.substr(0, 4));
  let 월 = parseInt(문자열.substr(4, 2));
  let 일 = parseInt(문자열.substr(6, 2));
  
  let 오늘 = new Date();
  let 나이 = 오늘.getFullYear() - 년;
  
  let 생일지남 = (오늘.getMonth() + 1 > 월) || 
                (오늘.getMonth() + 1 === 월 && 오늘.getDate() >= 일);
  
  return 생일지남 ? 나이 : 나이 - 1;
}

/**
 * 등급을 반환합니다.
 * @param {number} 점수 점수 (0-100)
 * @return {string} 등급 (A-F)
 * @customfunction
 */
function 등급변환(점수) {
  if (점수 >= 90) return "A";
  if (점수 >= 80) return "B";
  if (점수 >= 70) return "C";
  if (점수 >= 60) return "D";
  return "F";
}

사용 예시:
• =BMI계산(175, 70) → 22.9
• =나이계산("19900101") → 만 나이
• =등급변환(85) → "B"

📋 맞춤 메뉴 만들기

5 기본 메뉴 추가하기

스프레드시트 상단에 맞춤 메뉴를 추가합니다:

/**
 * 스프레드시트가 열릴 때 자동 실행되는 함수
 */
function onOpen() {
  let ui = SpreadsheetApp.getUi();
  
  // 메뉴 만들기
  ui.createMenu('📊 내 도구')
    .addItem('인사하기', '인사함수')
    .addItem('현재 시간 보기', '시간보기함수')
    .addSeparator()
    .addItem('데이터 정리', '데이터정리함수')
    .addItem('보고서 생성', '보고서생성함수')
    .addToUi();
}

// 메뉴에서 실행될 함수들
function 인사함수() {
  SpreadsheetApp.getUi().alert('안녕하세요!', '반갑습니다 😊', 
                                 SpreadsheetApp.getUi().ButtonSet.OK);
}

function 시간보기함수() {
  let 지금 = new Date();
  let 시간 = Utilities.formatDate(지금, "Asia/Seoul", "yyyy년 MM월 dd일 HH:mm:ss");
  SpreadsheetApp.getUi().alert('현재 시간', 시간, 
                                 SpreadsheetApp.getUi().ButtonSet.OK);
}

function 데이터정리함수() {
  let sheet = SpreadsheetApp.getActiveSheet();
  sheet.getDataRange().trimWhitespace();
  SpreadsheetApp.getActiveSpreadsheet().toast('공백이 제거되었습니다!', '완료', 3);
}

function 보고서생성함수() {
  SpreadsheetApp.getUi().alert('보고서 생성', 
                                 '보고서 생성 기능은 개발 중입니다.', 
                                 SpreadsheetApp.getUi().ButtonSet.OK);
}

저장 후 스프레드시트를 새로고침하면 상단에 "📊 내 도구" 메뉴가 생성됩니다!

6 사용자 입력 받기

대화 상자로 사용자로부터 값을 입력받습니다:

/**
 * 사용자 이름을 입력받아 인사합니다
 */
function 이름입력인사() {
  let ui = SpreadsheetApp.getUi();
  
  // 텍스트 입력 창
  let 응답 = ui.prompt('이름 입력', 
                       '이름을 입력하세요:', 
                       ui.ButtonSet.OK_CANCEL);
  
  // 버튼 확인
  if (응답.getSelectedButton() === ui.Button.OK) {
    let 이름 = 응답.getResponseText();
    ui.alert('환영합니다', 
             '안녕하세요, ' + 이름 + '님! 😊', 
             ui.ButtonSet.OK);
  } else {
    ui.alert('취소되었습니다.');
  }
}

/**
 * 값을 입력받아 셀에 쓰기
 */
function 메모작성() {
  let ui = SpreadsheetApp.getUi();
  let sheet = SpreadsheetApp.getActiveSheet();
  
  let 응답 = ui.prompt('메모 작성', 
                       'A1 셀에 입력할 내용:', 
                       ui.ButtonSet.OK_CANCEL);
  
  if (응답.getSelectedButton() === ui.Button.OK) {
    let 내용 = 응답.getResponseText();
    sheet.getRange("A1").setValue(내용);
    SpreadsheetApp.getActiveSpreadsheet().toast('메모가 작성되었습니다!', '완료', 3);
  }
}

/**
 * 확인 대화상자
 */
function 데이터삭제확인() {
  let ui = SpreadsheetApp.getUi();
  
  let 응답 = ui.alert('데이터 삭제', 
                      '정말로 모든 데이터를 삭제하시겠습니까?', 
                      ui.ButtonSet.YES_NO);
  
  if (응답 === ui.Button.YES) {
    SpreadsheetApp.getActiveSheet().clear();
    ui.alert('삭제 완료', '모든 데이터가 삭제되었습니다.', ui.ButtonSet.OK);
  } else {
    ui.alert('취소됨', '삭제가 취소되었습니다.', ui.ButtonSet.OK);
  }
}

onOpen() 함수에 이 함수들도 메뉴에 추가해보세요!

🎯 실습 2: 완전한 업무 자동화 도구 만들기

7 통합 메뉴 시스템 구축

모든 기능을 포함한 완전한 메뉴를 만들어봅시다:

/**
 * 스프레드시트 열릴 때 실행
 */
function onOpen() {
  let ui = SpreadsheetApp.getUi();
  
  ui.createMenu('⚡ 자동화 도구')
    .addSubMenu(ui.createMenu('📝 데이터 관리')
      .addItem('데이터 정리', '데이터전체정리')
      .addItem('빈 행 삭제', '빈행삭제')
      .addItem('중복 제거', '중복제거'))
    .addSeparator()
    .addSubMenu(ui.createMenu('📊 보고서')
      .addItem('매출 보고서', '매출보고서생성')
      .addItem('성적표 생성', '성적표자동생성')
      .addItem('통계 요약', '통계요약생성'))
    .addSeparator()
    .addSubMenu(ui.createMenu('🎨 서식')
      .addItem('제목 행 서식', '제목행서식적용')
      .addItem('테두리 추가', '테두리자동추가')
      .addItem('색상 초기화', '색상초기화'))
    .addSeparator()
    .addItem('❓ 도움말', '도움말보기')
    .addToUi();
}

// 데이터 관리 함수들
function 데이터전체정리() {
  let sheet = SpreadsheetApp.getActiveSheet();
  sheet.getDataRange().trimWhitespace();
  SpreadsheetApp.getActiveSpreadsheet().toast('공백 제거 완료!', '정리 완료', 3);
}

function 빈행삭제() {
  let sheet = SpreadsheetApp.getActiveSheet();
  let 마지막행 = sheet.getLastRow();
  let 삭제개수 = 0;
  
  for (let i = 마지막행; i >= 1; i--) {
    let 범위 = sheet.getRange(i, 1, 1, sheet.getLastColumn());
    let 값들 = 범위.getValues()[0];
    let 비어있음 = 값들.every(function(값) { return 값 === ""; });
    
    if (비어있음) {
      sheet.deleteRow(i);
      삭제개수++;
    }
  }
  
  SpreadsheetApp.getUi().alert('완료', 
    삭제개수 + '개의 빈 행이 삭제되었습니다.', 
    SpreadsheetApp.getUi().ButtonSet.OK);
}

function 중복제거() {
  SpreadsheetApp.getUi().alert('중복 제거', 
    '중복 제거 기능은 개발 중입니다.', 
    SpreadsheetApp.getUi().ButtonSet.OK);
}

// 보고서 함수들
function 매출보고서생성() {
  SpreadsheetApp.getActiveSpreadsheet().toast('매출 보고서를 생성합니다...', '처리중', 3);
  // 5차시의 매출보고서 함수 호출 가능
}

function 성적표자동생성() {
  SpreadsheetApp.getActiveSpreadsheet().toast('성적표를 생성합니다...', '처리중', 3);
  // 4차시의 성적표 함수 호출 가능
}

function 통계요약생성() {
  let sheet = SpreadsheetApp.getActiveSheet();
  let 마지막행 = sheet.getLastRow();
  let 마지막열 = sheet.getLastColumn();
  
  let 정보 = "시트명: " + sheet.getName() + "\n" +
            "데이터 행 수: " + (마지막행 - 1) + "\n" +
            "데이터 열 수: " + 마지막열;
  
  SpreadsheetApp.getUi().alert('통계 요약', 정보, 
    SpreadsheetApp.getUi().ButtonSet.OK);
}

// 서식 함수들
function 제목행서식적용() {
  let sheet = SpreadsheetApp.getActiveSheet();
  let 마지막열 = sheet.getLastColumn();
  
  sheet.getRange(1, 1, 1, 마지막열)
    .setBackground("#4285f4")
    .setFontColor("#ffffff")
    .setFontWeight("bold")
    .setHorizontalAlignment("center");
  
  SpreadsheetApp.getActiveSpreadsheet().toast('제목 행 서식 적용 완료!', '완료', 3);
}

function 테두리자동추가() {
  let sheet = SpreadsheetApp.getActiveSheet();
  let 범위 = sheet.getDataRange();
  
  범위.setBorder(true, true, true, true, true, true, 
                 "#000000", SpreadsheetApp.BorderStyle.SOLID);
  
  SpreadsheetApp.getActiveSpreadsheet().toast('테두리 추가 완료!', '완료', 3);
}

function 색상초기화() {
  let sheet = SpreadsheetApp.getActiveSheet();
  sheet.getDataRange().setBackground(null);
  SpreadsheetApp.getActiveSpreadsheet().toast('색상이 초기화되었습니다!', '완료', 3);
}

// 도움말
function 도움말보기() {
  let 도움말 = "📚 자동화 도구 사용법\n\n" +
              "▶ 데이터 관리: 데이터 정리 및 정돈\n" +
              "▶ 보고서: 자동 보고서 생성\n" +
              "▶ 서식: 빠른 서식 적용\n\n" +
              "문의: help@example.com";
  
  SpreadsheetApp.getUi().alert('도움말', 도움말, 
    SpreadsheetApp.getUi().ButtonSet.OK);
}

저장 후 스프레드시트를 새로고침하면 완전한 자동화 메뉴가 생성됩니다!

8 최종 테스트

만든 도구들을 테스트해봅시다:

테스트 체크리스트:
✓ 스프레드시트를 새로고침하여 메뉴가 나타나는지 확인
✓ 각 메뉴 항목을 클릭하여 정상 작동하는지 확인
✓ 사용자 정의 함수를 셀에 입력하여 결과 확인
✓ 대화 상자의 버튼들이 정상 작동하는지 확인

테스트 데이터를 만들어 각 기능을 하나씩 실행해보세요!

💡 사용자 정의 함수 작성 팁

• 주석 활용: JSDoc 형식 주석을 작성하면 함수 설명이 자동 완성에 표시됩니다
• 에러 처리: 잘못된 입력에 대한 처리를 추가하면 안정적입니다
• return 필수: 사용자 정의 함수는 반드시 값을 반환해야 합니다
• 성능 고려: 셀 수식은 자주 재계산되므로 빠른 실행이 중요합니다

⚠️ 주의사항

• onOpen() 함수는 스프레드시트를 열거나 새로고침할 때만 실행됩니다
• 사용자 정의 함수에서는 SpreadsheetApp의 일부 기능이 제한됩니다
• 메뉴에서 실행하는 함수는 권한 승인이 필요할 수 있습니다
• 사용자 정의 함수는 실행 시간이 30초를 초과하면 오류가 발생합니다

🎯 7차시 정리

이번 시간에는 스프레드시트 셀에서 사용할 수 있는 사용자 정의 함수를 만들고,
맞춤 메뉴를 추가하여 스크립트를 편리하게 실행하는 방법을 배웠습니다.
이제 여러분만의 업무 자동화 도구를 만들 수 있습니다!
다음 차시에서는 이메일 자동 발송과 트리거(자동 실행) 기능을 배워보겠습니다.