구글 스프레드시트 소분류 종속 드롭다운 목록 생성 스크립트
구글 스프레드시트 소분류 종속 드롭다운 목록 생성 스크립트
구글 스프레드시트 종속 드롭다운 목록 만드는 방법에 대해 인터넷에서 여러 문서를 참조해 보았습니다. 주로 INDIRECT 함수를 활용하는 방법과 스크립트를 활용하는 방법입니다.
구글 스프레드시트에선 드롭다운 목록 범위를 지정하는 방식이 엑셀과 다릅니다.
엑셀에서는 종속 드롭다운 목록을 지정할 때 목록 범위에 상위 분류 셀 값을 참조하는 Indirect 함수를 상대참조로 수식으로 지정합니다.
구글 스프레드시트는 드롭다운 목록 범위에 수식으로 지정이 안되고 범위자체를 입력하게 돼있습니다. 인터넷에서 참조한 문서는 INDIRECT 함수로 가변 영역을 만들어 이 영역을 드롭다운 목록 범위로 지정하는데 첫 번째 입력 영역에만 적용되고 다음 입력 영역에서는 상응하는 종속 목록으로 변경되지 않습니다.
구글 앱스 스크립트를 사용하여 소분류 종속 드롭다운 목록을 만들어 보겠습니다. 작성된 스크립트 전문은 문서 하단에 있습니다. 구글 앱스 스크팁트와 자바스크립트에 대한 지식이 충분치 않은 점 양해 바랍니다.
최상위 분류 드롭다운 목록 생성
최상위 분류의 드롭다운 목록은 시트에서 만들고 하위 종속 드롭다운 목록은 스크립트에서 만들겠습니다.
참조 : 구글 스프레드시트 드롭다운 목록 목록으로 데이터 입력
입력은 입력 시트에서 하고 목록 항목 데이터는 목록 시트에 들어 있습니다.
예시에서는 시명, 구명, 동명을 상위 분류에 따른 하위 종속 분류를 입력합니다.
입력 시트
- 입력 시트의 범위
A3:A12가 최상위 분류 시명을 입력하는 범위입니다.
목록 시트
데이터 확인
문서 상단 메뉴 데이터탭에서 데이터 확인을 실행합니다.
- 입력 시트에서 최상위 입력 범위
A3:A12를 선택합니다. - 셀범위에 상위
‘입력’!A3:A12가 지정돼있는 것이 보입니다… - 범위에서의 목록에 최상위 분류 시명의 목록 범위
‘목록’!A1:A3를 지정하고 데이터 확인 설정을 저장합니다.
입력시트에 최상위 시명 입력 드롭다운 박스가 만들어졌습니다.
이름이 지정된 범위
종속 드롭다운 목록의 범위에는 상위 분류 목록의 항목값을 이름으로 지정합니다.
문서 상단 메뉴>데이터>이름이 지정된 범위를 통해 해당 분류 범위에 이름을 지정합니다.
예시에서는 하위 분류를 갖는 시명 2, 구명 4 총 6개의 하위 분류 범위에 대해 이름 지정이 필요합니다.
스크립트 작성
문서 메뉴>도구 탭에서 <> 스크립트 편집기를 선택하고 나오는 스크립트 편집기 창에서 다음의 스크립트 함수를 작성합니다.
드롭다운 목록을 만드는 데이터 확인 관련 함수(method)로 다음과 같은 함수를 사용했습니다.
- onEdit(e): 에디트 이벤트 처리 트리거
참조: 구글 스크립트 도움말 - newDataValidation(): 새로운 데이터 확인 규칙 빌더
참조: 구글 스크립트 도움말 - requireValueInRange(range).build() : range를 데이터 확인 목록으로 지정
참조: 구글 스크립트 도움말 - setDataValidation(rule) : 데이터 확인 규칙 삽입
참조: 구글 스크립트 도움말 - clearDataValidations() : 데이터 확인 규칙 삭제
참조: 구글 스크립트 도움말 - getCriteriaValues() : 데이터 확인 규칙 불러오기
참조: 구글 스크립트 도움말
드롭다운 목록 입력 영역에서 입력 또는 수정이 발생할 때마다 스크립트가 실행되어야하므로 구글 앱스 스크립트 기본 제공 수정(edit) 이벤트 처리 트리거인 onEdit를 스크립트 이름으로 스크립트를 작성합니다.
onEdit 함수는 인자로 이변트 변수를 매개 변수로 사용할 수 있습니다. 매개 변수 이름은 임의로 사용할 수 있으며 여기서는 e 로 쓰겠습니다.(onEdit(e))
입력 셀의 Rang 속성 구하기
|
|
위의 코드의 e 이벤트 변수에서 아래의 속성을 구합니다.
- 3행: 입력 셀의 range
- 4행-5행: 입력된 셀의 행번호와 열번호
- 6행: range의 해당 시트
- 7행: 시트명
입력이 실행될 범위 지정
|
|
입력이 실행될 시트 범위를 지정하는 코드를 스크립트에 작성합니다. 본 예시의 입력범위는 A3:C12입니다.
- frow: 편집 범위의 첫번 째 행(3행)
- lrow: 편집 범위의 마지막 행(12행)
- fcol: 편집 범위의 첫번째 열(1열)
- lcol: 편집 범위의 마지막 열(3열)
- esheet: 편집 시트명(입력)
입력된 셀 조건 체크
|
|
위에서 지정한 입력 범위 조건에 입력된 셀의 범위가 맞는지 검증합니다. 조건에 맞지 않으면 스크립트가 종료됩니다.
입력된 셀의 값과 해당셀의 드롭다운 목록의 값 비교
|
|
- 22행: 이벤트 변수
e에서 입력값을 구합니다. - 25-26행: 입력셀의 데이터 확인 규칙을 불러 옵니다.
- 위 함수에서 반환된 args 변수는 드롭다운 목록이 들어있는 Range 개체와 드롭다운 목록 표시 여부인 true 또는 false 값을 갖고 있습니다.
- 27행: args[0]에는 드롭다운 목록의 항목이 들어 있습니다. getValues() 함수로 드롭다운 목록의 항목을 구할 수 있으며 getValues() 함수반환값은 2차원 배열입니다.
입력값과 해당셀 드롭다운 목록 비교
데이터 확인 규칙에 잘못된 데이터 입력 거부로 설정한 경우 이 부분은 필요 없겠습니다.
|
|
- 30-35행: 위의 절차에서 구한 목록 값과 입력값을 비교 검증하고 맞으면 검증 통과 표시를 합니다.(vsw = 1)
- cvalues[i].indexOf(value)>=0 : 자바스크립트 함수 indexOf(value)는 배열에서 value에 해당하는 값의 index를 반환합니다. 없으면 -1을 반환합니다.
- 반환값이 >=0 이면 입력값이 목록 안에 있는 것입니다.
입력 값이 조건에 맞는 경우
본 예시의 입력 범위는 3중 드롭다운 목록으로 구성돼있습니다. 입력 셀이 첫째 열인 경우 우측에 2개 둘째 열인 경우 우측에 1개의 하위 종속 열이 있습니다.
|
|
- 42행: 우측 하위 분류 셀의 값 유무를 불문하고 우측 1열 하위 분류 셀의 값을 삭제합니다. (값 유무를 첵크하면 스크립트가 길어지므로 그냥 삭제하기로 했습니다.)
- 하위 종속 드롭다운 목록의 범위는 상위 셀값을 이름으로 지정한 범위입니다.
- 43행: 상위 셀값을 이름으로 지정한 범위를 불러옵니다.
- 44-46행: 종속 드롭다운 목록의 범위로 데이터 확인 규칙을 만듭니다.
- 47행: 우측 1열 하위 분류 셀에 드롭다운 목록을 만드는 데이터 확인 규칙을 삽입합니다.
|
|
- 48-52행 입력 셀이 최상위 분류이면 2차 종속셀의 값과 드롭다운 목록 데이터 확인 규칙을 삭제합니다.
입력값이 목록에 없는 경우
|
|
- 55-56행 우측 1열 하위 분류 셀 값 및 종속 데이터 확인 규칙을 삭제합니다
- 57-60행 입력 셀이 첫째 열인 경우 우측 2열 하위 분류 셀 값 및 데이터 확인 규칙도 삭제합니다.
보다 많은 다중 종속 분류를 사용하는 스크립트에서는 하위 종속 분류 셀 값및 데이터 확인 규칙을 삭제하는 반복문이 필요하다고 생각합니다.
드롭다운 목록이 생성된 입력 시트
- 상위 분류 셀이 입력되지 않은 셀은 종속된 드롭다운 메뉴가 만들어지지 않습니다.
- 상위 분류 셀이 입력되면 하위 종속 분류 셀에 드롭다운 메뉴 부착 여부를 표시하는 삼각형 표시가 약간 지연돼서 표시됩니다.(스크립트 수행 시간인 듯)
종속 드롭다운 목록 생성 스크립트
function onEdit(e) {
//입력 range 속성
var srange = e.range;
var srow = srange.getRow();
var scol = srange.getColumn();
var sheet = srange.getSheet();
var sname = sheet.getName();
//입력 범위 지정
var frow = 3;
var lrow = 12;
var fcol = 1;
var lcol = 3;
var esheet = "입력";
//입력 범위 검증
if(sname == esheet &&
(srow >= frow && srow <= lrow) &&
(scol >= fcol && scol <= (lcol-1))) {
//입력 값 드롭다운 목록과 비교 검증
var value=e.value;
var cvalues = [];
var drange;
var args = srange.getDataValidation()
.getCriteriaValues();
cvalues = args[0].getValues();
var vsw=0;
for (var i = 0; i < cvalues.length; i++) {
if (cvalues[i].indexOf(value)>=0) {
vsw = 1;
break;
}
}
//하위 분류 종속 드롭다운 목록 삽입
var drange;
var rule;
var ss = SpreadsheetApp.getActiveSpreadsheet();
if (vsw == 1) {
srange.offset(0,1).clearContent();
drange = ss.getRangeByName(value);
rule = SpreadsheetApp.newDataValidation()
.requireValueInRange(drange)
.build();
srange.offset(0,1).setDataValidation(rule);
if (scol == 1){
srange.offset(0,2)
.clearContent()
.clearDataValidations();
}
} else {
srange.offset(0,1).clearContent()
.clearDataValidations();
if (scol == 1){
srange.offset(0,2).clearContent()
.clearDataValidations();
}
}
}
}
[구글 스프레드시트 기초] 문서 자동화 구글 앱스 스크립트 시작 개요
엑셀 indirect 함수를 이용한 다중 종속(소분류) 드롭다운 목록
[구글 스프레드시트 기초] 구글 스프레드시트 자동화 매크로 만들기