[구글 스프레드시트/Apps Script] 구글에서 자동화 구현하기 (엑셀 VBA 대신 사용할 도구)

엑셀이나 구글 스프레드시트를 사용하다 보면 반복적인 작업을 자동화하고 싶을 때가 있다. 최근 나는 구글 스프레드시트에서 특정 데이터를 집계하는 작업을 자동화해야 해서 방법을 찾고 있었다.

 

엑셀에서는 피벗 테이블, COUNTIF 함수, VBA 등을 활용하면 원하는 데이터를 쉽게 정리할 수 있다. 하지만 구글 스프레드시트에서는 VBA를 사용할 수 없기 때문에 다른 방법이 필요했다.

 

그러던 중, Google Apps Script(GAS)라는 도구를 알게 되었다.

GAS는 구글에서 제공하는 확장 프로그램 형태의 스크립트 도구로, 자바스크립트(JavaScript) 기반이라서 이용하기 어렵지 않았다. 실제로 사용해보니 생각보다 간단하면서도, 반복적인 작업을 자동화하는 데 매우 유용했다.

 

활용했던 방법을 예시데이터를 활용하여 소개하고자 한다.

 

 

---

 

 

예시 데이터

다음과 같이 예시 테이블을 구성해보았다. 학생들의 활동과 점수를 기록하는 테이블로, 이름, 날짜, 사유, 상점과 벌점이 기록되어 있다. 이 데이터를 바탕으로 학생별 상점과 벌점을 자동으로 집계하는 기능을 구현해보고자 한다.

학생이름	날짜	사유	상점	벌점
홍길동	3월 7일	지각		1
김철수	3월 10일	봉사활동	1
이영희	3월 21일	결석		1
홍길동	4월 2일	발표	1
김철수	4월 13일	청소	1
최민수	4월 15일	지각		1

 

예를 들어, 홍길동은 벌점 1점과 상점 1점을 받았고, 김철수는 상점 2점을 받았다는 식으로 정리하는 것이다.

 

구글 스프레드시트에 다음과 같이 데이터를 준비한다.

 

 

Google Apps Script 연결하기

 

1. 구글 스프레드시트에서 [확장 프로그램] - [Apps Script] 를 클릭하면, 새로운 탭에서 페이지가 열린다.

 

2. 새로운 창이 열리면, 기본적으로 Code.gs 파일이 생성되며, 자바스크립트 코드를 입력할 수 있는 창이 표시된다. 오른쪽 위에는 배포라는 파란색 버튼을 볼 수 있다.

 

 

3. 여기에 우리가 원하는 기능을 수행할 자바스크립트 코드를 작성하면 된다.

 

예를 들어, 특정 범위의 데이터를 가져오는 코드는 아래와 같다.

function getData() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet(); // 현재 시트 가져오기
  var data = sheet.getDataRange().getValues(); // 모든 데이터 가져오기
  Logger.log(data); // 로그에 데이터 출력
}

 

 

 

자동 집계 코드 작성

 

이제 Apps Script를 활용해 학생별 상점과 벌점을 자동으로 계산하는 코드를 작성할 것이다.
우리가 구현할 기능은 다음과 같다.

✔ A열(학생이름) 기준으로 데이터 집계
✔ D열(상점)과 E열(벌점)을 합산하여 학생별로 정리
✔ H열부터 결과를 출력 (학생이름, 총 상점, 총 벌점)

 

1. 코드 작성

Code.gs 파일에 코드를 입력한다.

function updateScoreTable() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet(); // 현재 활성화된 시트
  
  var nameRange = sheet.getRange("A2:A").getValues().flat().filter(String); // A열 (학생이름) - 1행 제외
  var rewardRange = sheet.getRange("D2:D").getValues().flat(); // D열 (상점) - 1행 제외
  var penaltyRange = sheet.getRange("E2:E").getValues().flat(); // E열 (벌점) - 1행 제외

  var scoreCount = {};

  // 학생별 상점 및 벌점 합산
  for (var i = 0; i < nameRange.length; i++) {
    var name = nameRange[i];
    if (!scoreCount[name]) {
      scoreCount[name] = { reward: 0, penalty: 0 };
    }
    scoreCount[name].reward += rewardRange[i] ? rewardRange[i] : 0;
    scoreCount[name].penalty += penaltyRange[i] ? penaltyRange[i] : 0;
  }

  // 결과를 H1부터 출력
  var startRow = 1; // 1행부터 시작
  var startCol = 8; // H열

  // 기존 데이터 초기화 (헤더 유지)
  var lastRow = sheet.getLastRow();
  if (lastRow > startRow) {
    sheet.getRange(startRow + 1, startCol, lastRow - startRow, 3).clear();
  }

  // 헤더 추가
  sheet.getRange(startRow, startCol).setValue("학생이름");
  sheet.getRange(startRow, startCol + 1).setValue("총 상점");
  sheet.getRange(startRow, startCol + 2).setValue("총 벌점");

  // 데이터 정렬 후 출력
  var output = Object.keys(scoreCount).map(name => [name, scoreCount[name].reward, scoreCount[name].penalty]);
  output.sort();

  sheet.getRange(startRow + 1, startCol, output.length, 3).setValues(output);
}

 

 

2. 코드 실행하기

• Apps Script 창에서 Ctrl + S를 눌러 저장한다.
• "updateScoreTable" 함수 실행 버튼 을 눌러 실행한다.
• 스프레드시트로 돌아가 H1부터 자동으로 정리된 데이터를 확인한다!

 

 

 

코드가 실행되면, 스프레드 시트의 H1부터 학생별 상점과 벌점이 자동으로 정리된다.

 

학생이름	상점	벌점
김철수	2	0
이영희	0	1
최민수	0	1
홍길동	1	1

 

이제 매번 데이터를 직접 집계할 필요 없이, 한 번의 실행으로 자동 집계할 수 있다.

 

---

마무리

 

이번에 처음으로 Google Apps Script(GAS)를 사용해보면서, 반복되는 데이터 집계를 코드 한 줄로 자동화할 수 있다는 점이 정말 편리했다.

 

• Google Apps Script는 JavaScript 기반이라 기존 JS 지식이 있다면 쉽게 활용할 수 있다.
• Excel의 VBA처럼 강력하지만, 설치 없이 바로 실행할 수 있어 접근성이 뛰어나다.
• 단순 반복 작업을 자동화하면 업무 효율이 크게 향상된다.

다음에는 트리거 설정(자동 실행) 같은 기능도 추가해보면 편리할 것이다. 이번에 Google Apps Script를 알게 되었는데, 앞으로도 다양하게 활용해봐야 겠다.