Apps Script로 Google Sheets에서 Dropdown의 multi select(다중선택) 구현

 

 

현재 ‘code’ 라는 시트의 데이터를 Dropdown(선택상자, SelectBox) 을 조회 조건으로 사용 중이다.

 

 

 

 

‘testSheet’ 라는 시트에서,

G/L 계정의 조회 조건을 다중 선택을 하고 싶다는 요구 사항을 받았다.

Google sheets에서 제공하는 기능은 없었고, 커스텀이 필요했다.

 

앞의 코드를 선택하면, 코드에 부합하는 뒤의 명칭을.

뒤의 명칭을 선택하면, 명칭에 부합하는 앞의 코드를 뿌려주는 기능도 물론 포함이다.

 

 

일단, onEdit() 트리거를 사용한다.

code 와 명칭이 변경될때 event를 생성한다.

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

	// 'testSheet' 에서 edit event catch
	if ( sheetName == "testSheet" ) {
		if ( column == '2' && row == '2' ) {
			// code가 빈 값이 경우 명칭도 초기화
			if ( isNull(getValue) ) {
				activeCell.offset(0, 1).setValue("");
			} else {
				if ( getValue != getOldValue ) {
					writeMatchedValues(spreadSheet, getValue, "code", activeCell);
					concatDropdownValues(activeCell, getValue, getOldValue);
				}
			}
		} else if ( column == '3' && row == '2' ) {
			// 명칭이 빈 값이 경우 code도 초기화
			if ( isNull(getValue) ) {
				activeCell.offset(0, -1).setValue("");
			} else {
				if ( getValue != getOldValue ) {
					writeMatchedValues(spreadSheet, getValue, "name", activeCell);
					concatDropdownValues(activeCell, getValue, getOldValue);
				}
			}
		}
	}
}

 

 

/**
 * 코드/명칭 간 선택된 G/L계정 정보 뿌려주기 (다중선택)
 * 코드가 선택되면 명칭을 채우고,
 * 명칭이 선택되면 코드를 채운다.
 * @param {object} spreadSheet  = Current Spreadsheet
 * @param {string} getValue     = 현재 값
 * @param {string} type         = code / value 구분
 * @param {object} activeCell   = 선택된 셀
 */
function writeMatchedValues(spreadSheet, getValue, type, activeCell){
  const masterSheet = spreadSheet.getSheetByName("code");
  const data        = masterSheet.getDataRange().getValues();
  
  if ( type == "code" ) {
    for ( var i=1; i<data.length; i++ ) {
      if ( data[i][0] == getValue ) { //[0] because column A
        var nextCell      = activeCell.offset(0,1);
        var nextCellValue = nextCell.getValue();
        var returnVal     = masterSheet.getRange((i+1), 2).getValue();
        concatDropdownValues(nextCell, returnVal, nextCellValue, "mutli");
      }
    }
  } else if ( type == "name" ) {
    for ( var i=1; i<data.length; i++ ) {
      if ( data[i][1] == getValue ) { //[1] because column B
        var preCell       = activeCell.offset(0,-1);
        var preCellValue  = preCell.getValue();
        var returnVal     = masterSheet.getRange((i+1), 1).getValue();
        concatDropdownValues(preCell, returnVal, preCellValue, "mutli");
      }
    }
  }
}

/**
 * drop down 선택 값 연달아 붙이기
 * @param {object} activeCell   = active cell
 * @param {string} getValue     = 현재 값
 * @param {string} getOldValue  = 이전 값
 */
function concatDropdownValues(activeCell, getValue, getOldValue) {
  const newValue = getValue;
  const oldValue = getOldValue.toString();

  var applyValue;
  if ( !newValue ) {
    applyValue = "";
  } else if ( newValue == "-" ) {
    applyValue = getValue;
  } else {
    if ( !oldValue || oldValue == "-" ) {
      applyValue = newValue;
    } else {
      if ( oldValue.indexOf(newValue) >= 0 ) {
        applyValue = oldValue.replace(", "+newValue, "").replace(newValue + ", ", "");
      } else {
        applyValue = oldValue + ', ' + newValue;
      }
    }
  }

  activeCell.setValue(applyValue);
}

/**
 * Null 체크
 * @param {obj}  obj  = Null 체크 대상
 */
function isNull(obj) {
   return (typeof obj != "undefined" && obj != null && obj != "") ? false : true;
}

 

 

이미 선택된 항목을 다시 선택하면, 해당항목은 사라진다.

 

 

 

 

 

 

 

 

 

위 파라미터 query 에서 in절로 사용하기 위해 파라미터 셋팅하는 부분

/**
 * dropdown 멀티로 가져온 데이터를 쿼리의 in 절로 변경
 * @param {string} sheetUrl = 해당 function을 호출한 sheet의 URL
 */
function createQueryString(){ 

  const sheet = SpreadsheetApp.getActiveSheet();
  const values = sheet.getRange("C2").getValue();

  console.log(values);

  var valueList = values.split(", ");
  var result;
  for ( var i in valueList ) {
    if ( i > 0 ) {
      result += ', "'+ valueList[i] + '"';
    } else {
      result = '("'+ valueList[i] + '"';
    }
  }
  result += ")";

  console.log(result);
  return result;
}

 

 

 

이렇게 쓰기 위해서는,

데이터 확인 규칙의 [경고 표시] 옵션을 선택해야 한다.