들어가며
안녕하세요. 트렌봇 개발팀 타노스입니다.
트렌봇 개발팀은 세계 곳곳에서 상품 정보를 수집하고 정제하여 트렌비 서비스나 운영 페이지에 제공하는 역할을 담당하고 있습니다.
각 상품의 브랜드, 색상, 구매옵션, 가격, 세일정보 등 여러가지 정보들을 관리하고 이와 관련된 다양한 요청을 받아 이를 운영에 반영해 드리고 있습니다.
예를 들면 특정 판매처의 상품들은 10%의 세일가를 적용하거나 혹은 특정 상품들을 ‘아울렛’ 같은 특수한 카테고리에 노출시키는 등의 요구사항입니다.
이러한 다양한 요구사항을 모두 수용할 수 있는 우주최강 관리툴을 제공해드리고 싶지만, 아쉽게도 우주최강 관리툴은 아직 개발중입니다…..
오늘은 위와 같은 상황에서 간편하게 데이터를 관리할 수 있게 하는 구글 시트(GoogleSheet)의 Apps Script를 소개하고자 합니다.
빠르게 적용해주세요!
상품 정보는 대부분 다 중요하지만 그 중에서도 타이밍이 중요한 것 들이 있습니다.
세일 같은 경우 특별한 기간에만 적용되거나 할인율이 변하기 때문에 이런 정보들의 경우 적용이 너무 늦을 경우 의미가 없어지게 됩니다.
운영팀에서는 빠른 처리를 위해 세일 등 중요한 정보들을 구글 시트로 정리해서 전달해 주시곤 합니다.
사실 구글 시트에 정리된 자료들은 그 자체로 데이터베이스와 다를 바 없기 때문에 이 구글 시트에 Apps Script를 적용하면 입력된 데이터들을 곧바로 활용할 수 있습니다.
Apps Script를 구글 시트에 적용하면 ‘Custom menu’, ‘Dialog’, ‘Sidebar’등을 만들어 붙일 수 있고, 직접 작성한 자바스크립트 함수들을 실행할 수 있습니다.
구글 시트에서 제공하는 대부분의 기능들은 Apps Script에서 제어가 가능합니다.
Google 계정을 통한 구글 시트파일의 권한 관리나 히스토리 관리가 가능하다는 점, 사용자가 익숙한 엑셀UI를 사용할 수 있다는 것도 상당한 이점입니다.
구글 시트 준비 및 Apps Script 생성
예를 들어 상품번호별로 별도의 할인율을 기록하고 상품 시스템의 API를 통해 기록한 할인율을 반영하는 상황을 가정 해 보겠습니다.
- 먼저 구글 시트에 접속해서 새 파일을 만들고 데이터도 입력합니다.
여기까지가 일반적으로 구글 시트를 사용하는 단계입니다.
이제부터 여기에 Apps Script를 사용하여 커스텀 기능을 추가해보겠습니다.
- 상단 메뉴의 Extensions -> Apps Script 를 통해 Apps Script 를 생성할 수 있습니다.
Apps Script 탭이 자동으로 열리게 되는데 좌측 메뉴를 살펴보면
- Overview : 전반적인 상태를 살펴볼 수 있습니다. 기본정보 및 배포현황, 현재 적용된 권한 등을 확인할 수 있습니다.
- Editor : 포함된 파일들의 관리, 수정이 가능하고 기본적인 테스트 등을 수행할 수 있습니다. 배포된 타 Apps Script를 라이브러리 형태로 불러오거나 Google에서 제공하는 다른 서비스와의 연결도 관리합니다.
- Triggers : Apps Script내의 특정 함수를 호출하는 Trigger를 추가 할 수 있습니다. 시트 내에서 발생하는 이벤트, 반복적인 이벤트, Calendar를 통한 이벤트 등을 설정할 수 있습니다.
- Executions : 실제 Apps Script에서 실행된 함수들의 로그를 확인할 수 있습니다. 디버깅을 위한 용도로 사용할 수 있습니다.
- Project Settings : 프로젝트에 대한 설정이 가능합니다. 기본 셋팅 몇가지와 Apps Script의 ID, Google Cloud Platform(GCP)프로젝트 설정 등을 볼 수있습니다.
생성된 Apps Script는 현재 오픈되어있는 구글 시트와 자동으로 연결됩니다.
기본적으로 현재 시트와 1:1 관계로 연결되지만 배포(Deploy)를 수행하면 다른 시트에서 배포된 Apps Script를 사용할 수도 있습니다.
그럼 이제 코딩 작업을 진행해보겠습니다.
SideBar 만들기
좌측 Editor 메뉴에서 기본적으로 제공하는 Code.gs파일을 수정해보겠습니다.
[Code.gs]
function onOpen(e) {
addMenu();
}
function addMenu() {
SpreadsheetApp
.getUi()
.createMenu("트렌비")
.addItem("할인율", "makeSideBar")
.addToUi();
}
function makeSideBar() {
var sidebar = HtmlService
.createTemplateFromFile("sidebar")
.evaluate();
sidebar.setTitle('상품 할인율 예제');
SpreadsheetApp.getUi() // Or DocumentApp or SlidesApp or FormApp.
.showSidebar(sidebar);
}
onOpen 함수가 이 Apps Script의 도입부입니다.
onOpen 함수는 별도의 설정 없이도 자동으로 실행되지만 Triggers 메뉴에서 시트 로드 시점에 지정한 함수를 실행하도록 추가 할 수도 있습니다.
addMenu함수에서는 커스텀 메뉴를 추가하고 있습니다. SpreadsheetApp객체를 사용해 상단 메뉴에 ‘트렌비’를 추가 하고 ‘할인율’ 하위 메뉴를 만들어줍니다.
‘할인율’ 메뉴를 선택하면 makeSideBar 함수가 실행되도록 makeSideBar를 입력하고 있습니다.
makeSideBar 함수에서는 사이드 바를 생성하고 UI에 추가해줍니다.
HtmlService를 사용해 ‘sidebar’ 파일로부터 사이드바 UI 객체를 생성하고 SpreadsheetApp를 이용해 화면에 사이드바를 표시해줍니다.
createTemplateFromFile("sidebar")에서 참조하는 파일이 아직 없으니 이번에는 파일을 추가해줍니다.
- Files 우측에 있는 ‘+’ 버튼을 누르고 html파일을 추가해주세요.
파일 이름을 sidebar 로 수정해주고 내용을 추가해줍니다.
[sidebar.html]
<!DOCTYPE html>
<html>
<head>
<base target="_top">
</head>
<body>
<input type=button id="applyDiscountRate" value="할인율 적용"/>
</body>
</html>
사이드바의 파일 내용은 Html 형식입니다. Html에는 나중에 시트의 데이터를 추출하여 적용하기 위한 버튼을 하나 추가합니다.
이제 사이드바를 표시하기 위한 작업은 마무리 되었으니 저장하고 (단축키는 Ctrl+s) 시트 탭으로 돌아가서 새로고침을 한번 눌러보죠.
상단 메뉴에 ‘트렌비’가 추가되었습니다. 추가한 메뉴를 한번 눌러보면…
이렇게 사용자에게 권한을 요청하는 팝업이 표시됩니다. 현재 로그인한 계정으로 처음 실행했기 때문에 권한을 요청하게 됩니다.
‘Continue’ 버튼을 누르면 구글 계정연결 과정이 안내되고 권한을 승인하는 프로세스가 진행됩니다.
모든 프로세스가 완료된 후 ‘트렌비 -> 할인율’ 메뉴를 다시 누르면 이제 우측에 사이드 바가 표시됩니다.
몇가지 주의사항들
onOpen함수에서 곧바로 사이드메뉴를 추가하지 않고 메뉴를 생성하는 과정을 거친 이유는 조금전의 권한요청을 위함입니다. 사용자의 직접적인 행위를 통하지 않으면 권한을 추가하라는 팝업이 뜨지 않고 ‘권한없음’ 오류만 발생하게 됩니다.- 간혹 권한요청을 통해 권한을 부여했음에도 동작이 실행되지 않고 ‘권한없음’오류가 발생하는 경우가 있는데 이건 현재 웹 브라우저에서 여러개의 구글 아이디가 동시에 로그인 되어있는 상태일 확률이 큽니다. Gmail은 A계정, 구글 시트는 B계정 등… 이미 알려진 이슈이며 별도의 해결책은 아직 없습니다. 한 개의 계정으로 로그인 해야만 정상적으로 동작합니다.
- 권한 관련 알려진 문제점 확인 링크 : https://developers.google.com/apps-script/guides/support/troubleshooting#issues_with_multiple_google_accounts
사이드 메뉴를 확인 했으니 이제 ‘할인율 적용’ 버튼에 이벤트를 연결해보겠습니다.
Event 연결
사이드바가 html형식으로 되어있으니 sidebar.html 안에 버튼 이벤트를 추가해줍니다.
[sidebar.html]
<!DOCTYPE html>
<html>
<head>
<base target="_top">
</head>
<body>
<input type=button id="applyDiscountRate" value="할인율 적용" onclick="applyDiscountRate();"/>
</body>
<script>
function applyDiscountRate() {
google.script.run.withSuccessHandler(sendDiscountResult).sendDiscountRate();
}
function sendDiscountResult(result) {
alert(result);
}
</script>
</html>
‘할인율 적용’ 버튼에 onclick 을 추가해주고 스크립트도 추가 해줍니다.
google.script.run 을 통해 Code.gs 내부에 있는 함수를 호출할 수 있습니다.
.withSuccessHandler(sendDiscountResult) 를 통해 성공시 이벤트 핸들러를 추가할 수 있습니다. withFailureHandler(function) 는 생략되어 있지만 실패시 핸들러도 추가할 수 있습니다.
.sendDiscountRate()가 Code.gs에서 호출될 함수입니다. 아직 작성되지 않았으니 미리 추가해줍니다.
[Code.gs]
function onOpen(e) {
addMenu();
}
function addMenu() {
.
.
function sendDiscountRate() {
const sheetName = SpreadsheetApp.getActiveSheet().getName();
const sheetId = SpreadsheetApp.getActive().getId();
const userEmail = Session.getEffectiveUser().getUserLoginId();
var values = SpreadsheetApp.getActiveSheet().getSheetValues(2,1,22,2);
const updateApi = "https://api-example.trenbe.com/product/discountrate";
var headers = {
"Authorization" : "Basic " + Utilities.base64Encode(USERNAME + ':' + PASSWORD)
};
var updateOptions = {
"method": "post",
"contentType": "application/json",
"muteHttpExceptions": true,
"headers": headers,
"payload": JSON.stringify({
worksheetName:sheetName,
sheetId,
userEmail,
values
})
}
var response = UrlFetchApp.fetch(updateApi, updateOptions);
Logger.log(response);
SpreadsheetApp.getUi().alert(`할인율이 적용되었습니다.`);
return values;
}
sendDiscountRate 함수롤 보면 SpreadsheetApp를 통해 시트에 대한 각종 정보를 가져옵니다.
예시에는 없지만 읽어오기 외에 쓰기에 대한 각종 기능들을 사용할 수도 있으며 UrlFetchApp을 통해 외부 rest api 호출도 가능합니다.
이를 통해 원하는 api와 연계하여 사용이 가능하며 SpreadsheetApp을 통해 화면에 안내 다이얼로그를 띄울 수도 있습니다.
마지막으로 시트에서 읽은 값들을 반환하고 있는데 이 반환값이 [sidebar.html]에서 withSuccessHandler를 통해 설정한 핸들러의 파라미터로 넘어가게 됩니다.
예시에서는 javascript의 alert을 통해 값을 표시했습니다.
이제 프로젝트를 저장한 후 사이드바에서 ‘할인율 적용’ 버튼을 눌러보면
앱이 실행되고 결과 창이 보입니다.
마치며
Apps Script 를 활용하여 커스텀 메뉴 및 사이드 바를 구성하고 외부 api 호출하는 과정을 소개해 봤습니다.
비슷한 과정을 통해 간단한 UI구성 및 API 연계 작업이 가능하고 이를 이용해 ‘엑셀파일을 PDF로 만들어 이메일로 전송하기’ 같은 작업 자동화 툴도 개발이 가능합니다.
이번에는 간단한 예제를 소개했지만 활용하기에 따라 상당히 복잡한 작업도 구현이 가능한데 Apps Script는 사실 구글 시트만을 위한 기능은 아닙니다.
Google에서 제공하는 다양한 서비스들에 적용하여 활용이 가능하므로 다양한 목적으로 유용하게 사용할 수 있습니다.
Gmail 자동 응답기, 나의 Gmail 사용통계보기(메일 쓰는 빈도나 보내는 메일의 용량 등..), 한번 읽으면 스스로 폭파되는 메시지 보내기 같은 재미있는 예시들이 많으니 한번 찾아보는 것도 좋을 것 같습니다.
Apps Script 공식 페이지 : https://developers.google.com/apps-script