상품 가격 감시 & 메일 통보 받기 구현 설명 @구글 스프레드 시트

가격비교 사이트나 온라인 쇼핑몰에서 구매를 원하는 상품의 가격을 스프레드 시트에 지정하면 그 가격 밑으로 하락 했을 때 메일로 통보해주는 스프레드시트를 만들었습니다. 저번 포스팅에서는 설치 및 사용법에 대해서 설명 하였습니다. 

상품 가격 감시 & 메일로 통보 받기 @ 구글 스프레드 시트 구현

이번 글에서는 어떻게 구현 했는지  설명을 해보려고 합니다. 

어떻게 동작 하는가?

다음은 상품 목록 시트 입니다. 

1. 제품가격을 실시간을 가져와서 셀에 자동입력 ( D열의 최종상품가격) . 
    제품가격은 구글 스프레드시트의 IMPORTXML 함수를 이용하여 가져 옵니다. 

2. 구글 앱스 스크립트(Google Apps Script)를 이용하여 매일 지정된 시간에 실행하는 함수 giftCert() 를 작성
 해당 값을 체크하여  감시 가격이내로 떨어지면 사용자에게 메일을 보냅니다. 

3. 작성한 함수를 트리거(예약된 작업)에 등록 

​최종상품가는 D열  : 상품 페이지의 가격을 가져오는 부분의 수식은

=hyperlink(H2,importxml(H2,VLOOKUP(B2,'코드'!A:C,3,false)))

VLOOKUP 함수는 B2셀에 있는 정보제공처의 Full XPath 문자열을 가져옵니다. 
B2셀 값을 코드시트의 A 열에서 찾아서 A:C 테이블 중 3번째열에 있는 값을 가져 옵니다.  

hyperlink 는 직접 접속 할 수 있도록 해당 페이지(H2)의 링크를 만드는 기능이고 
최종으로 다음 수식으로 완성되게 됩니다. 지정된 셀에는 상품의 가격이 반환 되게 됩니다. 

=importxml(" https://www.enuri.com/detail.jsp?modelno=101748140 ", 

 "/html/body/div[3]/div[2]/div[5]/div[5]/div/div[3]/div[1]/div/div[2]/div[1]/div[2]/div/div[1]/span/em")

지원 여부의 수식

=IF(IFNA(MATCH(INDEX(SPLIT(H2,"/"),1,2),'코드'!B:B,0)>0,FALSE),"지원","미지원(xPath추가필요)")

INDEX(SPLIT( H2 , "/"),1,2) 에서 H2는 URL입니다.
사이트 주소를 얻기 위함입니다.   
URL이 http://items.gmarket.co.kr/item  이면 "/" 로 분리(SPLIT)해서 받은 배열의 2번 째 입니다
분리된 호스트(items.gmarket.co.kr을 코드 시트의 B열에서 찾아서(MATCH) 존재 하면 지원  없으면 미지원으로 표시 됩니다.
만약 미지원으로 나온다면 코드시트에 Full XPath 문자열을 등록을 해줘야 하는데  쇼핑몰 사이트에 가서 Full XPath 문자열을 찾아서 코드 시트에 추가를 해야 되겠죠.  XPath 문자열을 추가하는 방법은 이전 포스팅에 설명되어 있습니다. 

스크립트 에디터 이용

giftCert 함수

메뉴의 확장 프로그램에서 Apps Script 에 들어가면 Code.gs 파일에 있습니다.

상품 목록 시트는 계속 추가 되므로  데이터가 들어가 있는 모든 열과 행을 모두 가져오기 위해서 getDataRange() 메서드를 사용합니다. 배열 형태로 반환 받으므로 for 문을 이용하여 루프를 돌립니다.

items[행번호][열번호] 같이 배열에 들어가게 되는데 현재 상품가는 prodPrice 는  importxml 로 가져온 값으로 4번째 열이므로  items[i][3]  가 됩니다. 

인덱스 0 부터 시작하는 i 는 시트의 행을 의미 합니다.  값을 지정하기 위해서 제목과 인덱스 0으로 시작하기 때문에 실제 시트의 행은 row + 2 를 해줘야 합니다. 
items 배열변수가  테이블이고 shift() 를 해주면 배열의 1행만 날라 갑니다. 

다음 함수에서 주석을 달아 놨으니 참고 하시면 됩니다. 

function giftCert()
{
  var activeSheet = SpreadsheetApp.getActiveSpreadsheet();
  var prodSheet = activeSheet.getSheetByName("상품목록");

  // 날짜 계산 하기 
  var strDate = Utilities.formatDate(new Date(), "GMT+9", "yyyy-MM-dd");
  // 개인 이메일
  var email = Session.getActiveUser().getEmail();
  // 항목 불러오기 - 시트에서 데이터가 들어가 있는 테이블을 가져옴 
  var items = prodSheet.getDataRange().getValues();  
  // 제목 행 날림
  items.shift();  
  
  // 모든 행을 루프 돌림
  for(var i=0; i < items.length; i++)
  {
    row = i + 2; 
    var prodName = items[i][0]; // 제품명
    var stdPrice = items[i][2]; // 감시가
    var prodPrice = items[i][3]; // 실시간 제품가 
    var lastDate = items[i][4]; // 검색 마지막 날짜
    var lastPrice = items[i][5]; // 검색 마지막 가격 
    var prodURL = items[i][7]; // 상품 페이지

    // 0제품명	1상점	2감시가 3최종상품가격	4최종검색날짜	5최종가격기록	6지원여부

	// 비어 있지 않으면  yyyy-MM-dd 포멧으로 문자열로 바꿉니다.  
    if(lastDate) lastDate = Utilities.formatDate(lastDate, "GMT+9", "yyyy-MM-dd");

    // 감시가 이하 인지, 
    if( prodPrice < stdPrice && strDate != lastDate )  // 금일 검색된 것은 제외함.  
    {
      // 감시가 출현시 날짜/가격 업데이트   
      prodSheet.getRange("E"+row).setValue(strDate);
      prodSheet.getRange("F"+row).setValue(prodPrice)
      
      // 메일 전송 
      Logger.log("가격 하락 했습니다." + strDate + ":" + prodPrice )
      GmailApp.sendEmail(email, prodName +" 감시 가격 이하로 하락 했습니다.", prodName +"\n 감시가 "+ stdPrice + " 현재가:" + prodPrice+"\n상품페이지:"+ prodURL
      +"\n시트로 가기:" + activeSheet.getUrl());
    }

    // 가격이 오르면 감시가 재설정 | 초기화  
    if( prodPrice > lastPrice && lastPrice > 0  ) 
    {
      Logger.log("가격 올랐습니다." + strDate + ":" + prodPrice)
      prodSheet.getRange("E"+row).setValue("");
      prodSheet.getRange("F"+row).setValue("");
      GmailApp.sendEmail(email, prodName +" 가격이 올랐습니다. 감시가를 재설정 해주세요." , prodName 
        +"\n 감시가 "+ stdPrice + " 현재가:" 
        + prodPrice+"\n상품페이지:"+ prodURL +"\n시트로 가기 : "+ activeSheet.getUrl());
    }
  }
}

 

가격이 오르면 등록된 날짜, 가격을 초기화 하고. 사용자에게 메일을 보냅니다. 
activeSheet.getUrl() 함수를 이용하여 현재 스프레드 시트에 이동하여 감시가를 재 설정할 수 있도록 링크를 제공합니다.