”합류 이후, 처음 제게 주어진 메인 과제로 구글스프레드시트를 활용해 대시보드를 만들게 되었습니다.

최향근제품길드 G파티 백엔드 엔지니어
API 활성화・서비스 계정 생성 그리고 공유 설정

활용되는 지표는 칩스(Chips)에서 판매되는 일일 상품의 개수, 상품명, 판매 금액, 환불 수량 등이 되겠습니다.

Sheets API를 사용하기 위해서는 사전 작업이 필요합니다.
사전 작업은 Google Sheets for Developers의 Sheets API에서 Java Quick start에 자세히 나와 있습니다.

조건

  • Java 1.8 이상
  • Gradle 7.0이상
  • 구글 클라우드 프로젝트
  • 구글 계정

위 가이드 순서대로 설명해 드리겠습니다. 첨부 링크에서 사용할 API를 세팅해 줍니다.
저희는 Google Sheets API를 사용해야 하므로 아래 화면에서 사용 버튼을 활성화 해야겠죠?

이후 첨부 링크에서 프로젝트 생성 후 서비스 만들기를 통해 서비스 계정을 생성합니다.
그리고 “새 키 만들기”를 통해 키를 생성합니다. (JSON으로 생성, 이 키파일은 나중에 credential 인증을 위해 사용됩니다.)

그리고 삽질을 조금 많이 한 구간인데, 시트 자체에 대한 엑세스 권한을 설정해야 해당 시트에 접근할 수 있습니다.
(회사의 service account 계정이 있다면 그 계정으로 설정하시면 됩니다.)

설정하지 않으면 Permission 오류가 발생합니다.

Google Spread Sheet API 사용하기

아까 다운받은 키 파일은 resources의 하위 디렉터리에 넣었습니다.
자, 이제 API를 사용하기 위한 사전 준비작업을 완료했습니다.

본격적으로 코드를 작성해 보겠습니다.
build.gradle에 아래 dependency를 추가합니다.

API 호출할 때마다 매번 credential을 호출하는 절차가 번거롭기 때문에 저는 @Configuration을 통해 스프링 빈 설정 파일을 등록해 주었습니다.

  • createScoped는 읽고 쓰는 작업을 실행할 예정이기 때문에SheetsScopes.SPREADSHEETS로 설정합니다.
  • credential 파일로 인증 후 성공하면 Sheet API를 사용하기 위한 Service객체를 리턴하는 코드라고 보시면 됩니다.

See, edit, create, and delete를 위한 scope설정

Cell 읽기

스프레드 시트 API를 이용하면 시트를 생성, 읽기, 쓰기, 조작하는 작업을 할 수 있게 됩니다. 저는 Cell을 읽기, 쓰기, 조작(병합)하는 작업에 대해서 알아보도록 하겠습니다.

예시로 아래와 같은 스프레드 시트가 있다고 가정하겠습니다.

C3에서 부터 데이터가 계속 하루씩 증가하는 시트입니다.

  • 스프레드시트의 데이터들은 어떤 Range 안에 존재하기 때문에 valueRange라는 값을 통해 셀의 데이터를 가져올 수 있습니다.
  • get() 함수의 두 번째 인자로 “test!3:3 ” 구조는 시트 이름 + “!” + 접근할 셀 입니다.
  • get() 함수의 첫 번째 인자인 spreadSheetId는 URL에서 확인할 수 있습니다.

예를 들어,

https://docs.google.com/spreadsheets/d/abcd1234567890/edit#gid=123456

라는 URL이라면 /d/와 /edit 사이의 abcd1234567890이 접근할 시트파일의 아이디가 됩니다.

디버깅해 보면 values라는 ArrayList의 첫 번째 인덱스 필드에 160개의 값이 들어가 있는 것을 확인해 볼 수 있습니다. A3, B3행은 값이 비어 있으므로 0번째, 1번째 인덱스는 빈 값이 반환 되었습니다.

Cell 쓰기

Cell 값을 읽는 것도 중요하지만 본격적인 작업을 위해서 값을 쓰는 것이 더 중요할 것 같습니다. 아래 코드는 DAU Dto를 가져와서 ValueRange를 통해 값을 쓰는 작업입니다.

  • valueRange는 List<List<>>구조이기 때문에 리스트에 값을 넣을 데이터들을 다시 list에 담아서 세팅합니다.

valueInputOption : 입력 데이터를 해석하는 방법에 대한 옵션입니다.
저는 USER_ENTERED로 세팅했고, 지금 상황에서는 크게 중요하진 않지만 이런 옵션도 있다는 것 참고 부탁드리겠습니다.

https://developers.google.com/sheets/api/reference/rest/v4/ValueInputOption

Dimension 옵션을 통해 데이터를 ROW로 쓸지, COLUMNS로 사용할지 선택할 수 있습니다.
(https://developers.google.com/sheets/api/reference/rest/v4/Dimension)

참고로 스프레드 시트의 열의 경우 A, B, C로 증가하기 때문에 인덱스 <-> 컬럼으로 변환이 필요하다면 아래 함수를 이용하면 됩니다.

Cell 병합

XL1:XM1셀을 병합해 보도록 하겠습니다.

MergeType은 아래와 같이 3가지 Enum타입이 존재합니다.
저는 MERGE_ALL로 설정하였습니다.

  • GrideRange를 통해 병합할 셀의 범위를 지정합니다.
  • 주의
    sheetId에 spreadSheetId를 설정하면 안된다는 것입니다. 하나의 스프레드 시트 파일안에 각각의 시트 고유 숫자가 있는데 그값을 넣어야 합니다. sheetId는 URL의 마지막 부분 “gid=”뒤에 설정된 값으로 확인 가능합니다.
  • 인덱스를 세팅하는 것이 생각보다 골치 아프기 때문에 가이드를 잘 확인하는 것이 중요할 것 같습니다. (위의 예시에서 635값은 XL 컬럼을 의미합니다.)

https://developers.google.com/sheets/api/reference/rest/v4/spreadsheets/other#GridRange

한계

Google 스프레드 시트 매뉴얼에 따르면 현재 1000만 셀까지 밖에 작업할 수 없습니다. 만약 1만개의 상품이 있다면 1000일(3년 이 안되는 시간)이 밖에 이용할 수 없는 문제가 생깁니다. 대용량 데이터를 관리해야 하는 상황이라면 셀의 개수가 늘어남에 따라 시트 자체의 속도도 매우 느리기 때문에 다른 대안을 찾는 편이 좋아 보입니다.

마무리

굳이 스프레드 시트 API를 활용하기 귀찮고, Google BigQuery 데이터를 그대로 반영되게 하는 방법으로 Apps Script를 이용하는 방법도 있습니다. Apps Script는 서버리스 환경에서 간단한 function만 작성하면 시트에 반영되기 때문에 좀 더 간편하게 활용할 수 있습니다. 다만 이 경우도 Google BigQuery에 적재 되는 테이블 데이터만 가져올 수 있기 때문에 한계가 있겠네요.