Apps Script로 Google Sheets의 검색조건 Dropdown 선택 시 다른 셀에 하위정보 뿌려주기 (vlookup 대체)

 

 

 

💡Google Sheet에서, vlookup 함수를 Apps Script 의 event trigger를 이용해 구현해본다.

    vlookup 수식으로 작성해 놓았을 경우, 작성된 셀에서 delete키를 눌럿을 경우 수식이
   삭제되기 때문에,  event trigger를 이용해 구현해보았다.

 

 

 

 

 

조회조건인 명칭을 dropdown 에서 선택하면, 해당하는 시도와 지사를 자동으로 뿌려줘본다.

 

 

 

1. 마스터 데이터 가져오기

'공통데이터' 시트의 데이터를 가져온다.

function getMasterData() {
  const ss = SpreadsheetApp.getActive();
  const masterSheet = ss.getSheetByName("공통데이터");
  const masterData = masterSheet.getRange("A2:C"+masterSheet.getLastRow()).getValues();
  
  Logger.log(masterData);
}

 

2. 선택된 셀의 데이터만 가져오기

function getMappingData() {
  const ss = SpreadsheetApp.getActive();
  const sheet = ss.getSheetByName("조회조건");
  const value = sheet.getRange("B2").getValue();
  
  const masterSheet = ss.getSheetByName("공통데이터");
  const masterData = masterSheet.getRange("A2:C"+masterSheet.getLastRow()).getValues();
  
  for ( i in masterData ) {
    if ( value ==  masterData[i][2] ) {
      console.log(masterData[i]);
    }
  }
}

 

3. onEdit 트리거에서 셀에 값 뿌려주기

/**
 * edit event
 * @param {object} e = event
 */
function onEdit(e) {
  const getValue    = e.value;
  const spreadSheet = e.source;
  const sheetName   = spreadSheet.getActiveSheet().getName();
  const column      = e.range.getColumn();
  const row         = e.range.getRow();
  const activeCell  = spreadSheet.getActiveCell();

  if ( sheetName == "조회조건" ) {
    if ( column == '2' && row == '2' ) {
      const masterSheet = spreadSheet.getSheetByName("공통데이터");
      const masterData = masterSheet.getRange("A2:C"+masterSheet.getLastRow()).getValues();
      
      for ( i in masterData ) {
        if ( getValue ==  masterData[i][2] ) {
          const setCell1 = activeCell.offset(1,0);
          const setCell2 = activeCell.offset(1,2);

          setCell1.setValue(masterData[i][0]);
          setCell2.setValue(masterData[i][1]);
        }
      }
    }
  }
}

 

 

 

 

 

 

 

내장트리거를 사용할 경우에는 에러 발생 시 로그를 확인할 수 없다.

(try catch 문을 사용해도 되지만)

이럴 경우에는 외장트리거에 트리거를 추가하면, 어느정도 내가 발생한 오류를 찾아 해결 할 수 있다.

/**
 * edit event 2
 * @param {object} e = event
 */
function onEdit2(e) {
  const getValue    = e.value;
  const spreadSheet = e.source;
  const sheetName   = spreadSheet.getActiveSheet().getName();
  const column      = e.range.getColumn();
  const row         = e.range.getRow();
  const activeCell  = spreadSheet.getActiveCell();

  Logger.log(row+"/"+column+"/"+getValue);
}