구글스프레드시트 셀 내 드롭다운 목록 만들기 (종속 드랍다운, 반응형 드랍다운) Dynamically populating a dropdown cell based on values in another dropdown cell

본 포스팅에서 구현하고자하는 것은 다음과 같다. 구글스프레드시트를 이용하여 드랍다운 목록을 만든다. 단, 다른 셀의 값에 기반하여 나타나는 목록이 달라지게 하는 것이다. 

아래와 같이 2개의 시트가 있다. Main 은 드랍다운 목록에서 데이터를 고를 수 있는 시트이고, lists 는 목록에 나타날 데이터 값이 들어있는 시트이다. 즉, Main 시트에서 용도를 '서버'로 선택할 경우에는 '리눅스, 유닉스, 윈도우, macOS' 가 선택할 수 있는 값으로 나와야하고, 모바일의 경우에는 '안드로이드, iOS'가 나와야하는 것이다.

시트명 : Main

시트명 : lists

 

데이터확인, 범위추가, indirect 함수를 이용한 단일행에서의 구현

일단 앞서 언급했듯이 2개의 시트는 만들어둔 상태에서 출발한다. Main 시트에서 A2 셀을 선택하고 데이터 > 데이터확인 을 누른다.

기준에서 범위에서의 목록을 선택하고 우측의 박스를 클릭하여 데이터 범위를 선택한다.

데이터 범위 선택

lists 시트의 첫번째 행인 A1:C1 을 선택한다.

잘못된 데이터가 입력되는 경우 경고표시를 하도록 선택하고 저장을 누른다.

A2 셀에 드랍다운 메뉴가 생겼다.

내려보면 앞에서 선택한 범위내의 값이 '서버, 모바일, 데스크탑'이 목록으로 표시됨을 알 수 있다.

이제 A2 셀에서 선택된 값에 따라 B2 셀에 운영체제를 선택할 수 있는 드랍다운 메뉴를 만들어보자. 이를 위해 lists 시트로 이동하여, 먼저 서버 값 선택시 표시되는 4개의 목록을 선택하고, 데이터 > 이름이 지정된 범위 정의를 누른다.

값에 '서버'라고 입력한 후 완료를 누른다.

마찬가지 방식으로 모바일과 데스크탑과 범위추가를 마친다.

이제 indirect 함수를 사용할 차례이다. indirect 함수는 문자열로 지정된 셀 참조를 반환한다. 즉, Main 시트의 A2 셀의 문자열값에 해당하는 앞서 명명한 범위내의 데이터를 반환한다. 

이제 B2 셀을 선택한 다음 데이터 > 데이터 확인을 눌러 데이터 범위를 선택한다.  

이 범위에 앞서 만든 indirect 함수 결과가 표시되는 E2:E5 영역을 선택하면 된다. 

아래와 같이 A2 셀의 값에 따라 B2 셀에 서로 다른 드랍다운 목록이 나옴을 알 수 있다.

 

다만 하나 수정해야할 점은 A2 셀에 목록에는 없는 '임베디드'가 입력된 경우, A2 셀에 경고표시와 함께 B2 셀에 잘못된 셀을 참조하는 경우 표시되는 #REF! 오류가 발생했음을 알 수 있다.

이 경우 lists 시트의 E2 셀로 이동하여 다음과 같이 iferror 함수를 추가한다.

수정되었음을 알 수 있다.

이제 남은 문제는 위의 방식은 하나의 행에만 작동한다는 것이다. 아래와 같이 A3 셀에서 모바일을 선택해도 모바일에 맞는 목록을 보여주지 못한다는 한계가 있다. 이제 스크립트를 이용하여 여러 행에 작동하도록 만들어보자. 

스크립트 편집기를 이용한 다중행에서의 종속형 드랍다운 구현

도구 > 스크립트 편집기로 이동한다. 먼저 프로젝트 이름을 수정하고 저장하자. 

전체 코드는 다음과 같다. 

 

function onEdit(){

  var tabLists = "lists";
  var tabValidation = "Main";

  var ss = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  var datass = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(tabLists);
  
  var activeCell = ss.getActiveCell();
  
  if(activeCell.getColumn() == 1 && activeCell.getRow() > 1 && ss.getSheetName() == tabValidation){
    
    activeCell.offset(0, 1).clearContent().clearDataValidations();

    var makes = datass.getRange(1, 1, 1, datass.getLastColumn()).getValues();
    
    var makeIndex = makes[0].indexOf(activeCell.getValue()) + 1;
    
    if(makeIndex != 0){
    
        var validationRange = datass.getRange(2, makeIndex, datass.getLastRow());
        var validationRule = SpreadsheetApp.newDataValidation().requireValueInRange(validationRange).build();
        activeCell.offset(0, 1).setDataValidation(validationRule);
  
     }  
      
  }
  
}

코드를 한 줄씩 살펴보자. 먼저 리스트가 포함된 시트의 이름을 tabLists 로, 드랍다운이 구현될 시트 이름을 tabValidation 으로 지정한다.

  var tabLists = "lists";
  var tabValidation = "Main";

현재 스프레드시트의 활성화된 시트를 ss, 그리고 tabLists 이름을 가지는 시트를 datass 로 지정한다.

  var ss = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  var datass = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(tabLists);

활성화된 셀의 컬럽값이 열값이 1, 행값이 1보다 크고, 시트의 이름이 tabValidation 에서 지정한 이름(즉, Main)인 경우, if 함수 내부를 실행한다. 

if(activeCell.getColumn() == 1 && activeCell.getRow() > 1 && ss.getSheetName() == tabValidation){
  
}

if 함수내부를 보면, 먼저 활성화된 셀의 바로 우측열의 컨텐츠와 검증규칙을 지우게 된다.

• offset(rowOffset, columnOffset) - 주어진 행과 열(음수도 가능함)만큼 새로운 범위를 반환한다. 새로운 범위의 크기는 기존 범위의 크기과 같다. 
• clearContent() - 포맷을 유지한채 해당 범위의 컨텐츠를 지운다.
• clearDataValidations() - 해당 범위의 데이터검증규칙을 제거한다.

    activeCell.offset(0, 1).clearContent().clearDataValidations();

makes 는 아래의 용도 타이틀 범위를 가리키며, makeIndex 는 makes[0] 에서 활성화된 셀의 값이 나타나는 위치값에 1을 더한 값임

• getRange(row, column, numRows, numColumns) - 주어진 좌표의 상단좌측에서부터 주어진 수의 행과 열을 가지는 범위를 반환함
• getLastColumn() - 컨텐츠가 있는 마지막 컬럼의 위치를 정수로 반환
• getValues() - 행, 그리고 열에 의해 인덱스된 2차원 배열 반환
• getValue() - 범위내의 최상단 좌측값 반환, 반환형은 숫자, 불린, 날짜, 문자열이 될 수 있음. 비어있는 셀의 경우 빈 문자열 반환함

    var makes = datass.getRange(1, 1, 1, datass.getLastColumn()).getValues();
    
    var makeIndex = makes[0].indexOf(activeCell.getValue()) + 1;

indexof() 메소드는 찾는 문자열이 없는 경우 -1 을 반환한다. 따라서 반환값에 1을 더한 makeIndex 가 0 이라는 얘기는 활성화된 셀의 값을 가지는 리스트가 없다는 말이다. 예를 들어, 활성화된 셀이 값이 '임베디드'인 경우, 위의 '서버', '모바일', '데스크탑'에 일치하는 문자열이 없으므로, if 함수 내부를 실행하지 않는다. 

함수 내부를 보면, 리스트가 있는 시트의 두번째 행, 앞서 도출된 makeIndex 값의 열이 위치한 셀에서부터 컨텐츠가 존재하는 행만큼 검증 범위를 반환한다. 그리고 활성화된 셀의 바로 우측셀에 리스트 목록을 반환한다.  

• getRange(row, column, numRows) - 주어진 좌표의 최상단좌측 셀에서 주어진 갯수의 행만큼 범위 반환
• getLastRow() - 컨텐츠가 있는 마지막 행 위치 반환
• requireValueInRange(range) -입력이 주어진 범위내의 값과 일치하는지 검증하는 규칙 설정
• build() - 빌더에 적용된 설정에 따라 데이터 검증 규칙 생성

    if(makeIndex != 0){
    
        var validationRange = datass.getRange(2, makeIndex, datass.getLastRow());
        var validationRule = SpreadsheetApp.newDataValidation().requireValueInRange(validationRange).build();
        activeCell.offset(0, 1).setDataValidation(validationRule);
  
     }  

여러 행에서도 무리없이 작동함을 알 수 있다. 다만 A2 셀 용도 선택 후 B2 셀에서 운영체제 목록이 업데이트되는데 약간의 딜레이가 느껴지는 점이 단점이기는 하다.