구글 스프레드시트로 RE100 대시보드 만들기 (3. 트리거 설정 및 업데이트 자동화)

1편과 2편에 이어 이번 편에서는 2가지를 살펴볼 예정이다.

 

1.기존 데이터와 새로 수집하는 데이터의 비교를 통해서 신규로 추가된 데이터만 시트에 업데이트
2.트리거 설정을 통해서 만들어둔 함수를 일정 주기마다 실행되도록 설정 (Trigger)

 

 

 

1. 데이터를 업데이트하는 함수  : updateData()

 

우선 코드부터 살펴보면 다음과 같다.

function updateData(){
  //1. getCurrent 함수를 통해서, 웹의 데이터를 가져와서 2차원 배열 형태로 저장해둔다
  var dataFromWeb = getCurrent()

  //2. getSheetItems 함수를 통해서, 기존 시트의 데이터를 2차원 배열 형태로 변수에 저장한다.
  var dataFromSheet = getSheetItems()
  
  //3. 웹에서 가져온 데이터에서 filter 메소드를 통해서 시트에 이미 있는 행을 제거해준다.
  var result = dataFromWeb.filter(row => !dataFromSheet.includes(row[0]))

  //4. 만약 신규로 추가되는 데이터가 없을 경우, 그냥 return을 반환한다.
  if (result.length == 0){
    return
  
  //5. 기존 데이터의 마지막 행을 구하기 위한 helper function을 이용해서, 마지막 행 다음으로 데이터를 추가한다.
  var sh = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("data")
  
  var lr = get1stNonEmptyRowFromBottom(sh,2)
  sh.getRange(lr+1,2,result.length, result[0].length).setValues(result); 
  }
  
  
// getCurrent 함수는 2편에서 소개됨
  
  
///helper function 1) 데이터셋에서 마지막 행을 가져오기
function get1stNonEmptyRowFromBottom (sh,columnNumber) {
  var offsetRow = 1
  const search = sh.getRange(offsetRow, columnNumber, sh.getMaxRows())
  .createTextFinder(".")
  .useRegularExpression(true)
  .findPrevious();
  return search ? search.getRow() : offsetRow;
};


// helper function 2) data 시트의 데이터를 2차원 배열로 가져오기
function getSheetItems(){
  var sh = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("data")
  var lr = get1stNonEmptyRowFromBottom(sh,2)
  var data = sh.getRange("B2:B"+lr).getValues()
  // console.log(data)


  result = []
  for(n=0;n<data.length;++n){
    var value = data[n][0] ; // x is the index of the column    starting from 0
    result.push(value)
  }

  return result
}

 

위 코드 주석에서도 설명을 추가하였지만, 기본적인 원리는 다음과 같다.

 

1. 먼저 웹페이지 상의 데이터를 2차원 배열로 가져와서 변수 A (dataFromWeb)에 저장한다.

2. 이미 시트에 있는 데이터도 2차원 배열로  가져와서 변수 B (dataFromSheet)에 저장한다.

3. 자바스크립트 배열의 filter 함수를 이용해서, A중에서 이미 B에 있는 원소들은 제거해준다.

 - 데이터가 중복되는지 아닌지를 판단하는 기준은 '기업 명' (Company Name)이다. 새로운 기업이 추가되어, 새로운 기업명이 웹페이지에서 보이는 경우 filter 함수를 이용해도 제거되지 않는다.

4-1. 만약 새롭게 업데이트된 데이터가 없는 경우, 아무런 작업을 진행하지 않는다.

4-2. 만약 새롭게 추가되는 데이터 (예를 들어 기업 C가 추가됨)가 있는 경우, 시트의 마지막 행에 데이터를 추가한다.

 

 

2. 트리거 설정을 통해서 함수를 주기적으로 실행하기

 

updateData() 함수를 만들었으면 이 함수를 주기적으로 실행해줄 필요가 있다.

매주 수동으로 함수를 실행할 수는 있지만, 앱스크립트에서는 이런 번거로움을 해소해주는 트리거 (Trigger) 설정이 가능하다.

앱 스크립트 에디터 우측의 시계 모양을 클릭하면, 아래와 같이 설정을 할 수 있다.

 

앱스크립트의 트리거는 크게 

1) 시간 기반 (특정 시간, 특정 주기마다 실행하기)

2) 스프레드시트 기반 (스프레드시트가 열릴때마다 / 특정 셀의 값이 바뀔때마다 실행하기)

3) 캘린더 기반 

 

의 3가지 트리거가 있는데, 

여기서는 시간 기반 트리거 설정을 하면 된다.

시간 유형에서는 주 단위뿐만 아니라 분 단위, 일 단위 등 자유롭게 설정이 가능하다.

현재는 1주일에 한번씩, 매주 월요일 자정에 이 함수가 실행되도록 설정을 해두었다.

 

 

이렇게 매주 1회 웹페이지의 데이터를 확인하고, 시트의 데이터와 비교해서 신규로 추가된 기업이 있는 경우만 데이터가 추가되도록 하였다.