구글스프레드시트에서 간단하게 연간 습관 목표 달성표를 만들다가 생전 안해본 GAS라는 걸 써보게 됐다.
엑셀 VBA도 말로만 들어봤지 써본 적이 전혀 없는데 뜬금없지만 vlookup이나 sumif같은 걸로 생각보다 해결책이 쉽게 검색되지 않아서 차라리 코딩할 수 있으면 편할텐데 하다가 그렇게 됐다.
문제상황: 아래와 같이, 달력처럼 만들어놓고 그 날 성취한 만큼 숫자를 기록하면, 1년동안의 총 달성한 숫자의 합계나 평균, 매일 습관같은 경우 달성한 날이 전체 중 몇 퍼센트인지 등을 계산하고 싶었다.
아래와 같은 결과시트를 만들고 싶었던 것
이를 위해 처음으로 GAS Google Apps Script 에 들어가봤는데,
구글스프레드시트 화면에서 '확장프로그램' 메뉴를 눌러보면 'Apps Script'가 있고, 클릭하면 된다.
바로 코딩화면이 나온다.
대충 찾아보니 문법이 자바스크립트와 비슷하다.
정말 별 건 아니지만(무식하게 짰지만), 아래와 같이 for문으로 반복을 돌려서 해당 습관의 데이터값을 매달 1일부터 31일까지 가져왔다.(j로 월마다 반복하고 i로 일마다의 데이터를 반복해서 가져온다.)
가져온 값 자체를 더하거나 수행한 날수를 구하기 위해 1을 더하거나 해서 마지막에 '통계'시트에다 값을 입력해주도록 짰다.
function myFunction() {
// var sheet = SpreadsheetApp.getActiveSheet();
var rawdatasheet = SpreadsheetApp.getActive().getSheetByName("습관체크");
var sqtsdays = 0;
var sqtsalldays = 0;
var sumofsqts = 0;
var sumofbook = 0;
var sumoftistory = 0;
var sumofvelog = 0;
var sumofnonvegun = 0;
var sumofdiary = 0;
for(var j=7; j < 101; j=j+8)
{
for(var i=3; i < 34 ; i++)
{
var sqts = rawdatasheet.getRange(j, i).getValue();
var books = rawdatasheet.getRange(j+1, i).getValue();
var tistory = rawdatasheet.getRange(j+2, i).getValue();
var velog = rawdatasheet.getRange(j+3, i).getValue();
var nonvegun = rawdatasheet.getRange(j+4, i).getValue();
var diary = rawdatasheet.getRange(j+5, i).getValue();
if(sqts > 59){
sqtsdays += 1;
sumofsqts += sqts;
}
if(isNumeric(sqts)){
sqtsalldays += 1;
}
if(books > 0){
sumofbook += books;
}
if(tistory > 0){
sumoftistory += tistory;
}
if(velog > 0){
sumofvelog += velog;
}
if(nonvegun > 0){
sumofnonvegun += nonvegun;
}
if(diary > 0){
sumofdiary += diary;
}
}
}
var sqtspercent = (sqtsdays/365);
var sqtspercentofnow = (sqtsdays/sqtsalldays);
var avgsqts = (sumofsqts/sqtsalldays);
var resultsheet = SpreadsheetApp.getActive().getSheetByName("통계");
resultsheet.getRange('B2').setValue(sqtspercent);
resultsheet.getRange('D2').setValue(sqtspercentofnow);
resultsheet.getRange('E2').setValue(avgsqts);
resultsheet.getRange('B3').setValue(sumofbook);
resultsheet.getRange('B4').setValue(sumoftistory);
resultsheet.getRange('B5').setValue(sumofvelog);
resultsheet.getRange('B6').setValue(sumofnonvegun);
resultsheet.getRange('B7').setValue(sumofdiary);
}
// 숫자인지 검사하는 isNumeric function이 GAS에서 지원하지 않아 검색해서 찾아넣음 (https://sites.google.com/site/nnillixxsource/McPherson/mcpher/global/IsNumeric_js?overridemobile=true)
function isNumeric(s) {
return !isNaN(parseFloat(s)) && isFinite(s);
}아래 화면에서 동그라미 친 부분에서 실행, 저장, 어느 함수를 실행할 것인지 선택 등을 할 수 있다.
시트에 접근하는 권한이 필요하기 때문에 안내메시지가 나오는데 아래 글 참고(허용하면 됨)
https://velog.io/@chaejm55/%EA%B5%AC%EA%B8%80-%EC%8A%A4%ED%94%84%EB%A0%88%EB%93%9C-%EC%8B%9C%ED%8A%B8-%EC%9E%90%EB%8F%99%ED%99%942
이제 코딩을 다 마쳤고, 실행해봤을 때 내가 원하는 시트에 원하는대로 값이 입력이 된다면, 매번마다 내가 이걸 실행해줄 수는 없으니 실행되는 조건을 설정돼서 자동으로 실행하게 한다. 이걸 '트리거'라고 부른다.
맨 좌측 세로로 나열된 메뉴에서 시계모양이 있는데, 마우스를 갖다대면 트리거라고 나온다.
이런 식으로 친절하게 설정을 할 수 있다. 실행할 함수를 선택하고, 나같은 경우 스프레드시트에 값이 입력될 때마다 실행되게 하고 싶어서 '스프레드시트 + 수정시'로 설정했다. 과연, raw data가 있는 시트를 수정할 때마다 자동으로 해당 스크립트가 실행되면서 통계시트에 값이 입력되는 걸 볼 수 있었다.
다만 간단한 스크립트임에 비해 실행시간은 꽤 소요되는 느낌이다. 보통 4~5초에서 가장 오래 걸릴 땐 거의 10초 이상? 그래도 뭐 그렇게 빠른 실행속도를 요구하는 상황은 아니므로 쓰는데는 아무 무리가 없을 듯.
