Google Apps Script - 구글 스프레드시트에서 매크로만들기
동생과 영어공부를 위해 스프레드 시트를 활용하여 단어시험지를 만드는 과정을 일부 자동화시켰다. 이 과정에서 구글 스프레드 시트에서 활용가능한 Google Apps Script에 대해 알게된 내용을 정리해보고자 한다.
계기
영어공부를 해야겠다고 마음먹었는데, 이왕 하는거 공인 시험에서 점수를 받아보고 싶었다. 대학교 졸업조건을 채우기 위해 토스를 본 적은 있지만, 많은 사람들이 응시하는 토익을 한 번도 치뤄본 적이 없어서 이번 기회에 도전해보기로 했다.
동생이랑 같이 구글 스프레드시트에서 단어 시험지를 만들어서 매일 시험을 보기로 했다. 동생이 예쁘게(?) form을 만들었고, 해커스에서 제공하는 단어장에 있는 단어로 번갈아가며 시험지를 만들고 채점해주기로 했다.
엑셀은 그저 csv 파일이라고만 생각했는데, 시험지를 만들면서 체크박스 같은 것도 셀에 넣을 수 있다는 것을 알았다. 체크박스의 값으로는 true/false가 들어갈 수 있어서 엑셀의 COUNTIF집계함수를 이용해 체크박스의 true 개수를 집계해서 자동으로 채점되도록 했다.
=COUNTIF(A3:I19, TRUE)&" / 50"
몇 일 동안 시험지를 만들면서 느낀 점은, 시험지 내용을 작성하는 건 만들면서 단어도 외우고 괜찮은데, 저 포맷을 유지시키는 것이 너무 귀찮았다. 그리고 엑셀에는 생각보다 다양한 기능이 많았다. ‘매크로’ 기능을 발견했고 아래와 같은 반복작업을 자동화해보기로 했다.
자동화할 작업 목록
1. 시트를 복사하고 시트이름을 DAY0 형태로 수정
2. A1 셀에 '0일차', 날짜를 기입
3. 체크박스를 모두 해제
4. 전날 기입한 단어를 삭제
5. 빨간색으로 오답표시한 셀을 다시 검은색으로 변경
매크로 만들기
도구 > 매크로 > 매크로기록 를 실행하고 시트복사, 이름수정, ‘0일차’ 값을 기입하는 동작을 하고 저장했다.
하지만 이 방법으로는 매번 동적으로 바뀌어야하는 값들을 처리할 수 없었다. 예를들어 매크로를 기록할 때 셀에 데이터를 ‘DAY11’ 이라고 입력했으면 매크로 실행 시 매번 ‘DAY11’ 이라고 기입되는 것이다. 이것은 내가 원하던 자동화가 아니었다…
이 문제를 해결하기 위해 구글에 찾아보니 방금 기록한 매크로를 스크립트로서 확인하고 변경할 수 있다는 것을 알았다 !!!
Google Apps Script 작성
방금 매크로기록을 통해 저장한 매크로는 다음과 같이 스크립트로 저장되어있었다.
/** @OnlyCurrentDoc */
function myFunction() {
var spreadsheet = SpreadsheetApp.getActive();
spreadsheet.getRange('C3').activate();
spreadsheet.duplicateActiveSheet();
spreadsheet.getActiveSheet().setName('DAY11');
spreadsheet.getCurrentCell().setValue('DAY11');
spreadsheet.getRange('A2').activate();
};
현재 활성화된 시트를 getActive() 함수를 통해 SpreadcheetApp 객체로 받아와서 다양한 함수들을 사용하는 모습을 볼 수 있다. 매크로 기록 당시 C3 셀을 클릭하는 바람에 activate() 함수로 활성화가 된 것 같고, 시트 복사는 duplicateActiveSheet() 함수를 통해 할 수 있는 것으로 보인다. 해당 파일은 .gs라는 확장자를 가지고 있었고, 관련 API 문서가 분명 있을 것 같아서 찾아봤다.
Google Apps Script (.gs)
modern JavaScript platform (V8엔진 런타임) 에서 동작하는 스크립트 언어로 Google Apps (Calender, Drive, Gmail, Sheets, Slides …) 에서 사용할 수 있다. 자세한 내용
사용해보니 기본적으로 JavaScript 문법에 각 Google App 별로 제공되는 API 함수들을 사용할 수 있는 것으로 느껴진다.
GitHub API, OAuth, JSON, XML Parser 등 다양한 API들과 상호작용 할 수 있다고 한다. 자세한 내용
그 결과 다음과 같이 자동화 매크로를 완성시켰다.
/** @OnlyCurrentDoc */
function myFunction() {
const spreadsheet = SpreadsheetApp.getActive();
const sheetName = spreadsheet.getSheetName();
const newSheet = spreadsheet.duplicateActiveSheet();
let cnt = parseInt(/\d+/.exec(sheetName)) +1;
newSheet.setName(`DAY${cnt}`);
newSheet.getRange('A1').setValue(`${cnt}일차`);
newSheet.getRange('B1').setValue(`=TODAY()`).setNumberFormat(' yyyy.mm.dd (ddd)');
newSheet.getRange('A3:I19').clearContent().setFontColor("black");
};
SpreadsheetApp.getActive() 함수를 통해서 Class Spreadsheet 를 얻고 제공되는 함수를 사용해서 시트를 조작할 수 있다.
현재 시트의 이름을 가져와서 숫자부분만 정규표현식으로 추출한다. 1을 더해서 cnt 변수에 저장하고 새로 만들 시트에 이름(DAY0), ‘0일차’를 표시할 때 사용한다.
newSheet.getRange('B1').setValue(=TODAY()).setNumberFormat(' yyyy.mm.dd (ddd)'); 는 다음과 같이 동작한다.
Spreadsheet 객체에서 제공하는 getRange() 함수를 통해 ‘B1’ 셀 영역에 해당하는 Class Range 를 얻고 제공되는 함수를 사용해서 시트를 조작할 수 있다.
B1 셀에 엑셀에서 날짜를 표시하는 수식 =TODAY() 를 입력하면 =TODAY() 글자 그대로 텍스트가 입력되는 것이 아니라 수식 함수가 엑셀에서처럼 동작하여 결과값이 입력된다.
Range 객체에서 get을 제외한 여러 함수들은 계속 스스로를 리턴한다. 즉, this Rnage 객체를 리턴한다. 그래서 체이닝이 가능해진다.
날짜 포맷을 2021.09.07 (화) 와 같이 지정했다. 스프레드시트(엑셀) 내에서 서식 > 숫자 메뉴에서 서식을 지정해주는 것과 같은 동작이다.
newSheet.getRange('A3:I19').clearContent().setFontColor("black"); 는 다음과 같이 동작한다.
‘A3:I19’ 셀 영역에 해당하는 Range 객체를 얻고, clearContent() 함수로 내용을 지워준다. clear()를 사용하면 적용된 옵션까지 다 지워지므로 주의한다. 그리고 setFontColor() 함수를 이용해서 오답을 위해 빨간색으로 처리한 셀들을 다시 검은색으로 모두 변경해준다.
🤣
뭔가, 하다보니 열심히(신나게) 만들었는데, 동생한테 사용하라고 했더니…
우리가 앞으로 시험보는 날 까지 매 번 이 작업을 수동으로 하는 시간을 다 합쳐도 언니가 이거 만드는 시간보다 오래 걸렸을까?
라는 질문을 받았다. ㅋㅋㅋ
그렇다면 이렇게 블로그에 포스팅해서 이 세상 누군가의 시간도 절약할 수 있도록 하겠다.
Reference
- https://support.google.com/docs/answer/9331168?hl=ko
- https://developers.google.com/apps-script/reference/spreadsheet/spreadsheet
- https://developers.google.com/apps-script/reference/spreadsheet/range