중년코딩
534
2019-05-16 07:32:59 작성 2019-05-16 07:47:15 수정됨
0
664

[중년코딩|2-2]구글시트로 만드는 도서대여


안녕하세요. 직장생활 15년만에 파이썬으로 코딩을 시작한 대머리 아저씨입니다.

2-1편 구글시트와 Zapier만으로 만드는 도서대여 프로세스(https://okky.kr/article/579112)에 이어서 오늘 2-2편에서는 실제 함수를 어떻게 적용했는지 적어보겠습니다.

도서대여를 위한 DB는 다음 구조로 설계했었습니다. m은 마스터DB, t는 트랜잭션DB입니다.

m_book
  구매기록 -> t_rental_history
t_rental_history
t_review
m_team: 외부DB인 직원 리스트(구글 시트)
t_bookjom: 조르기를 처리하기 위한 임시DB

입력은 다음 포맷으로 받아옵니다.

구글설문지_transaction: 대여/반납/한줄평
  대여/반납 -> rental_history
  한줄평 -> review

구글설문지_book_jom: 반납조르기
  조르기 -> bookjom


그냥 그림을 봅시다. 

Book 마스터 DB입니다. 별 거 없습니다. 이메일은 외부db에서 끌고 옵니다. vlookup(importrange(DB명,시트!범위)) 씁니다.

시간이 많이 걸린 건 도서분류 가져오는 작업입니다. 크롤링입니다. 하하하하하하. 뿌듯하네요.

ISBN을 입력하면 도서분류를 가져옵니다. 간단해 보입니다만, 모든 자료수집이 그렇듯 만드는 방법을 찾는 데에는 엄청난 시간이 걸립니다.

국내 인터넷 서점, 서지정보 사이트들의 api를 다 뒤졌는데 결국 교보문고에서 답을 찾았습니다. 교보문고는 책 상세페이지의 주소가 http://www.kyobobook.co.kr/product/detailViewKor.laf?barcode=9791162540381 이런 식으로 되어 있습니다. 즉 barcode=xxxxxxxx 를 던져주면 책 상세 페이지로 바로 연결됩니다. 함수 추가합니다.

=arrayformula(if(I2:I<>"","http://www.kyobobook.co.kr/product/detailViewKor.laf?barcode="&I2:I,""))

매 라인의 ISBN으로 주소를 만들어서 던져줍니다. 이젠 상세페이지에서 도서분류를 끌고 오기만 하면 됩니다. 참 쉽죠? 안 쉽습니다!!!!

왜냐. 도서 분류를 ul 여러개로 박아놔서 엘리먼트를 특정하기가 지저분합니다. 자바스크립트 끌고 오기로 합니다. 그래서 M, N, O열이 생겼습니다. 자바스크립트에 박혀 있는 상세정보는 이렇게 생겼습니다.

var _title = "[내서] 10년 동안 적금밖에 모르던 39세 김 과장은 어떻게 1년 만에 부동산 천재가 됐을까?"; /* 
도서상세페이지 */ _ct =_RP("국내도서/경제경영/재테크금융/부동산/부동산투자일반"); _pd =_RP("10년 동안 적금밖에 
모르던 39세 김 과장은 어떻게 1년 만에 부동산 천재가 됐을까?"); _pd_barcode=_RP("9791162540381",1); 
_amt = _RP("15,120",1); _A_amt[_ace_countvar]="15,120"; 
_A_nl[_ace_countvar]="1"; _A_pl[_ace_countvar]="9791162540381"; 
_A_pn[_ace_countvar]="10년 동안 적금밖에 모르던 39세 김 과장은 어떻게 1년 만에 부동산 천재가 됐을까?"; 
_A_ct[_ace_countvar]="국내도서/경제경영/재테크금융/부동산/부동산투자일반"; _ace_countvar++;

필요한 건 마지막 부분이라서, A_ct[_ace_countvar]= 의 위치를 find()함수로 찾아 내고(N열), 마지막"++"의 위치를 찾아내서(O열) 두 개를 더하고 빼고 해서 "국내도서/경제경영/재테크금융/부동산/부동산투자일반" 이 문자열만 뽑아냅니다. 뿌듯합니다.

아... 자바스크립트는 if(J1<>"",importxml(J1,"/html/body/script[11]/text()"),"") 요 함수로 가져옵니다. J1이 아까 만들었던 상세정보 URL이고, script[11]이 도서상세정보가 기재된 자바스크립트 위치입니다. 구조가 바뀌면 틀어질테니 도서 정보는 정기적으로 값복사를 하도록 도서관리자에게 지시합니다. 나의 게으름을 위해서는 다른 직원의 귀찮음이 필수입니다.


얘는 rental_history 테이블입니다. 함수는 이렇게 생겼습니다.


={filter(book_master!H2:H,book_master!B2:B<>"");filter(transaction!A2:A,transaction!$A2:$A<>"")}

즉 book_master에서 구매일 있는 놈을 가져오고 ";"으로 트랜잭션 시트(응답시트)에 기록된 대여/반납 리스트를 가져옵니다. 



 

위 화면이 실 사용자들이 보는 화면입니다. 도서 리스트가 나오고, 도서 상태값과 관련 정보들이 들어 있습니다.

보유자는 대여중인 상태일 때 제일 마지막(트랜잭션 일자 기준) 대여자를 가져옵니다.

=if(N2<>"대여중",filter(rental_history!B2:B,rental_history!C2:C=A2,rental_history!E2:E=or("구매","반납"),rental_history!F2:F=maxifs(rental_history!F2:F,rental_history!C2:C,A2)),"")

상태값은 대여횟수와 반납횟수를 비교하여 처리합니다.

=filter(arrayformula(if(M2:M=L2:L,"대여가능","대여중")),A2:A<>"")

예외처리 따위 없습니다. 사람들은 예외를 일으킬만큼 부지런하지 않습니다.

액션 버튼 역할을 하는 놈들을 url링크로 달아 줬습니다. 구글 설문지 링크이고 전편에 얘기했던 것처럼 값이 미리 채워진 링크입니다. 현재 상태에 어긋나는 액션을 할 수 없게 상태에 따라 url값을 바꿔줍니다. switch함수와 hyperlink함수입니다.

=filter(switch(N2:N, "대여가능", hyperlink("https://docs.google.com/forms/d/e/대여반납설문지/viewform?usp=pp_url&entry.15780="&A2:A&"&entry.19915="&B2:B&"&entry.778=대여&entry.1580="&text(today(),"yyyy-mm-dd"),"대여처리"), "대여중", hyperlink("https://docs.google.com/forms/d/e/1/반납조르기시트/viewform?usp=pp_url&entry.869="&O2:O&"&entry.5359="&B2:B,"반납조르기")),A2:A<>"")

즉, 대여가능 상태에서는 대여반납 설문지를 "대여처리"라는 이름으로 링크를 해주고(설문지에 도서명, 도서key, 사용자명, 일자, 트랜잭션 유형(대여)는 자동으로 넣어줍니다.)

대여중인 상태에서는 반납을 조를 수 있는 링크로 연결해줍니다. 역시 도서key, 도서명, 현재보유자를 자동입력합니다.

반납하기 링크는 옆 열에 생성해줍니다. 즉 P열은 책을 대여하려는 사람, Q열은 책을 반납하려는 사람이 사용하는 열입니다. UX에서는 사용자 분리가 생명입니다. 캬... 예외처리는 없습니다.

=filter(switch(N2:N, "대여가능","", "대여중", hyperlink("https://docs.google.com/forms/d/e/대여반납설문지/viewform?usp=pp_url&entry.1580="&A2:A&"&entry.19975="&B2:B&"&entry.78=반납&entry.10="&text(today(),"yyyy-mm-dd"),"반납하기")),A2:A<>"")

아까 썼던 함수를 재활용합니다. 똑같이 대여반납설문지로 연결하지만 트랜잭션 유형이 "반납"유형인 것만 다릅니다. 대여가 안되어 있는 상태면 링크가 안 나옵니다.

구글 시트는 끝!


빨리 출근해야 하니 Zapier로 갑니다.

(중간 저장합니다.)


액션은 저렇게 생겼습니다. Step 3개부터는 돈을 내야 합니다. 구글 설문지에, 새 응답이 생기면 동작하는데, 구글 어카운트를 지정해주고, 지정한 option으로 가져옵니다. 옵션에서는 가져올 구글 시트, 시트내의 워크시트를 지정해줄 수 있습니다. SQL에서 select * from table과 똑같은 결과가 되겠지요? DB인증 처리를 안 해줘도 되니 세상 편합니다.

두번째 스텝의 핵심은 템플릿입니다.

Step1에서 가져온 값들을 누구한테 보낼 건지, 어떤 텍스트를 만들건지에 활용할 수 있습니다.


대여자, 신청자, 도서명 정보를 가져와서 텍스트를 생성해줍니다. 

이렇게 하면 설문지 응답이 기록되면(반납조르기를 실행하면) 슬랙으로 다음과 같은 메시지가 갑니다.


.....책 제목이 지금 제 심정을 반영하네요. ㅋㅋㅋㅋ

취미생활은 여기까지. 이제 출근하러 갑니다.

2
0
  • 댓글 0

  • 로그인을 하시면 댓글을 등록할 수 있습니다.