구글 스프레드시트와 App Script로 데이터 수집 자동화 하기

얼마 전 오픈소스 컨트리뷰션에 대한 글을 올렸다. 지금도 마스터 과정이 한창 진행 중이다. 우리 팀 분들은 다 내가 저 나이엔 뭘했더라를 생각하게 만드는 풋풋하고 열정 넘치는 분들인데 페어 프로그래밍도 이걸 계기로 처음 해봤다. 멘토님들도 퇴근하고 나면 매일 피드백/리뷰를 해주시고, 여러모로 재밌는 활동이라 남은 마지막 기간동안 최대한 많이 기여하려고 한다.

 

우리는 매주 전체 회의를 하는데, 얼마전 전체 회의에서 팀별로 얼마나 활동을 했는지 github 지표를 수집하는 역할을 할 사람이 필요하다 하셨다. 작년에 했던 분들은 데이터를 어떻게 수집했는지 여쭤봤는데, 수동으로 수집했다 하셨다.

수집 대상이 11명이라 수작업으로도 충분히 할 수 있지만, 원하는 기간별로 자유롭게 데이터를 뽑아볼 수 있도록 자동화를 하면 좋겠다는 생각이 들었다. Github는 서비스에서 쓰이는 거의 모든 데이터를 내려주는 풍부한 API를 지원하는 회사이기도 하고 말이다.

구글 스프레드시트를 선택한 이유

처음에는 터미널에서 명령어를 입력하면 수집 결과가 텍스트가 좌르륵 나오는 스크립트의 형태를 고안했다. 하지만 다른 사람이 사용하려면 파일을 내려받아야 하고, OS가 윈도우인 사람에겐 접근성이 떨어질 것 같다는 생각이 들었다. 그래서 엑셀을 Github API와 연동할 방법이 있나 찾다가 스프레드시트에 App Script라는 기능이 있다는 것을 알게 되었다.

 

App script는 구글 워크스페이스 플랫폼에서 가벼운 애플리케이션 개발이 가능하도록 만들어진 스크립팅 플랫폼이다. JavaScript로 코드를 작성할 수 있는 웹 에디터를 제공하고 스프레드시트에 데이터를 get/set 할 수 있는 api를 제공한다. 다만 외부 모듈을 import 하는 것이 불가능하고, App Script에서 지원하는 라이브러리만 사용할 수 있기 때문에 간단한 어플리케이션만 만들 수 있다.

개발 과정

일단 내가 수집하려는 데이터는 다음과 같았다.

• commit 개수
• PR 개수
• issue 개수
• PR에 달린 리뷰 개수
• Discussion

데이터 수집시 설정할 수 있는 조건

• organization 하위에 있는 모든 레포지토리
• 데이터 수집 기준 일자를 선택할 수 있어야 한다.

App Script

스프레드 시트 상단 메뉴에서 '확장 프로그램' - App Script를 선택하면 코드 에디터가 나온다.

자바스크립트를 몇 년 전에 기초 수준으로만 써봤기 때문에 거의 모든 문법을 잊어버린 상태였다. w3cschool을 뒤져가며 힘겹게 코드를 짰다. 만드는데 2일 정도 소요됐는데 JS가 익숙한 사람이면 훨씬 빨리 할수 있을 것이다.

const sheet = SpreadsheetApp.getActiveSpreadsheet()
const range = sheet.getRange('A1')

• 코드 에디터에서 이런 식으로 스프레드 시트 객체를 가져와, getRange로 시트에 있는 영역을 선택해 데이터 get, set, 디자인 변경 등 이것저것 원하는건 거의 다 할 수 있다.
• api call을 하려면 UrlFetchApp이라는 것을 사용해야한다.

에디터에서 코드를 다 짜고 나면 실행/디버그 버튼을 눌러 테스트하거나 디버깅을 해볼 수 있다.

 

스크립트 개발이 완료되면 '트리거'라는 탭에 들어가 언제 어떤 조건에서 실행시킬건지 설정할 수 있다. 나는 시트에 값이 업데이트 될 때마다 자동으로 실행되도록 트리거를 설정했다.

결과물

저 노란색 칸이 수정할 수 있는 부분인데, 알맞은 값을 입력하고 몇초 기다리면 하단 표에 데이터가 업데이트된다.

수집하는 기간이 길수록 기다려야 하는 시간이 좀 길어진다.

REF!는 기여를 했지만 오픈소스 컨트리뷰션엔 참여하지 않아서 팀 정보가 없는 사람들이다

우리끼리 쓰는 툴이니까 예외처리를 그렇게 디테일하게 하진 않았고 유효하지 않은 날짜에 대한 예외 처리만 해놨다.

비동기처리를 안해서 생긴 문제

긴 기간의 데이터를 조회하려면 Paging index를 하나씩 높여가면서 요청을 여러번 보내야 했다. 처음엔 별 생각 없이 while 문으로 구현했는데 기간을 수년 단위로 설정하니 값이 아예 불러와지지 않는 문제가 있었다.

느낌상 비동기처리를 안해서 생긴 문제인거 같았는데 검색해보니 자바스크립트엔 멀티 쓰레드 같은 개념이 없었다.

async랑 await라는 키워드를 알게 됐고 이걸 함수/api 호출부에 붙이니 허무하게 해결이 되었다.

구현하지 못한 부분

1. 엑셀 실력 ISSUE : #REF! 없애는 법을 모르겠다

총 6개의 열 중에 Team 부분은 스크립트가 아니라 엑셀의 INDEX랑 SUMPRODUCT 함수를 썼다.

다른 시트에 정리해 놓은 팀원 목록에서 이름이 일치하면 팀 이름을 가져온다.

구글링으로 어찌어찌 팀 이름을 가져오는 함수까진 만들었지만 팀원 정보가 없을 경우 저 #REF를 어떻게 없애는지 모르겠다. (IFERROR는 효과가 없었다)

보기에 좀 안예쁠 뿐 치명적인 문제는 아닌거 같아서 그냥 저대로 냅뒀다. 엑셀로 글자 입력하고 색깔 입히는 법밖에 모르는 엑알못인지라 엑셀 초고수가 보면 좀 알려주시면 좋겠다.

 

2. 세가지 값이 수집되지 않고 있다.

Discussion, 공동 Author, PR comments 다.

엄청 귀찮을거 같아서 안한게 1개고 구현이 사실상 어려울거 같아 안한게 2개다.

• Team Discussion은 API가 있지만 내가 수집해야하는건 Organization level discussion 이었다. 이건 몇몇 오픈소스들에서만 사용하는 기능이라 깃허브 API에서는 아직 지원하지 않고 있어 구현하지 않았다. App Script는 외부 모듈을 쓸 수 없기에 이것까지 수집하려면 HTML content를 통째로 불러와서 직접 파싱을 해야할거 같은데 거기까진 ㅎㅎ;;
• 멘토님들이 공동 Author 기능을 활용하는걸 적극적으로 장려해서 몇몇 분들이 기여하실 때 co-author로 commit을 하셨다. 다만 api 만으로는 공동 author가 존재하는지 알 수 없어서 이걸 구현하려면 커밋메세지를 직접 파싱해야한다. 문제가 공동 커밋을 할때는 git config에 들어가는 NAME을 쓰는데, 깃허브 ID랑 커밋할때 NAME이 다른 경우가 많아 동일 인물인지 확인하기가 어려웠다. 일단 그래서 이것도 패쓰
• PR comments는 PR list를 내려주는 api 응답에 들어있는 comments라는 값의 합계다. 처음엔 당연히 이 값이 PR마다 달려있는 모든 comment 갯수인줄 알고 구현했다. 근데 실제로는 깃허브 PR 탭에서 보이는 코멘트 갯수보다 숫자가 훨씬 적게 나온다. 이게 response 응답값이 각각 뭐하는 값인지 공식문서에서 하나하나 설명해주지 않아서 comments가 대체 뭔지 직접 세보고 정체를 알게 됐다;
  
  Github PR에서 abcd라는 사람이 코멘트를 남기면 프로필 옆에 뜨는 텍스트가 크게 두 종류다.
  'abcd commented' 이렇게 뜨는게 있고, 'abcd left a comment' 이렇게 뜰 때도 있다.
  이 comments라는 값은 abcd commented라고 보이는 '일반 코멘트'만 집계된다.
  코드리뷰나 approve하면서 남기는 메세지는 left a comment가 되는데, 이것들은 포함되지 않는다.
  
  코드 리뷰 갯수까지 완벽하게 포함하려면 PR id를 가지고 review list를 받아오는 API를 한번 더 이용해야 한다. 만약에 검색 기간이 길어서 조회해야하는 PR이 100개면 API를 100번 쏴야해서 속도도 그렇고 api limit를 넘길 수 있다. 개별 comment 갯수를 생략한 것도 동일한 이유다. 어떤 사람이 코멘트를 작성했는지 확인하려면 PR 별로 comment list를 받아와야 하기 때문이다.

아무튼 기억이 희미해지기 전에 스프레드 시트 자동화를 시도하면서 겪은 문제들을 기록해봤다

이런 기능을 무료로 제공하다니 역시 구글 짱짱