Apps Script로 Google Sheets의 종속되는 Dropdown 만들기

 

 

입력시트에서, 공통데이터 시트의 데이터들을 dropdown으로 선택하여 입력하고 싶다.

 

하지만 data validation을 위해

시도에서 서울시를 선택하면 서울시에 해당하는 지사들만 입력하고 싶다.

• 동작지사
• 강남지사
• 강남지사
• 중구지사
• 중구지사
• 관악지사
• 강북지사
• 강동지사

 

 

 

 

공통데이터에서 사용할 데이터 범위의 이름을 먼저 지정해준다.

범위를 지정하고, 우클릭을 한 뒤 [이름이 지정된 범위 지정] 을 클릭한다.

 

 

 

범위 이름을 지정하면, 오른쪽과 같이 지정된 셀 범위의 이름을 확인 할 수 있다.

 

 

 

 

/**
 * 데이터 구조 가져오기.
 */
function getStructreData() {
  const ss = SpreadsheetApp.getActive();
  const data = ss.getRangeByName("deptData").getValues();

  console.log(data);
  return data;
}

 

 

 

/**
 * 지사 리스트 가져오기.
 */
function filterJisa(sido, data) {
    // test data
    data = getStructreData();
    sido = '서울시';

    let jisas = [];

    for ( let item in data ) {
        let row = data[item];
        if ( row[0] == sido ) {
          jisas.push(row[1]);
        }
    }

    jisas = [...new Set(jisas)];

    console.log(jisas);
    return jisas;
}

 

 

/**
 * 시도 행의 Edit Event
 */
function getJisa(e) {
  const sheet = e.source.getActiveSheet();
  const row = e.range.getRow();
  const column = e.range.getColumn();

  if ( sheet.getName() == "입력시트" && row > 1 && column == 1 ) {
    const sido = sheet.getRange(row,1,1,1).getValue();
    const range = filterJisa(sido, getStructreData());

    const rule = SpreadsheetApp.newDataValidation().requireValueInList(range, true).build();
    const targetCell = sheet.getRange(row,3,1,1);    
    targetCell.setDataValidation(rule);
  }
}

 

 

 

/**
 * 명칭 리스트 가져오기.
 */
function filterName(sido, jisa, data) {
  let names = [];
  for ( let item in data ) {
    let row = data[item];
    if ( row[0] == sido && row[1] == jisa ) {
      names.push(row[2]);
    }
  }

  names = [... new Set(names)];
  return names;
}

/**
 * 명칭 행의 Edit Event
 */
function getNames(e) {
  const sheet = e.source.getActiveSheet();
  const row = e.range.getRow();
  const column = e.range.getColumn();

  if ( sheet.getName() == "입력시트" && row > 1 && column == 2 ) {
    const sido = sheet.getRange(row,1,1,1).getValue();
    const jisa = sheet.getRange(row,2,1,1).getValue();
    const range = filterName(sido, jisa, getStructreData());

    const rule = SpreadsheetApp.newDataValidation().requireValueInList(range, true).build();
    const targetCell = sheet.getRange(row,3,1,1);
    targetCell.setDataValidation(rule);
  }
}

똑같은 방식으로, 트리거를 추가해준다.

 

 

 

/**
 * Cell 효과 변경
 */
function getCellState(targetCell, type) {
  if ( type == 'Pending' ) {
    targetCell.setValue('');
    targetCell.setBackground('#d8d8d8');
  } else if ( type =='Done' ) {
    targetCell.setBackground('#fff');
  }
}

/**
 * 시도 행의 Edit Event
 */
function getJisa(e) {
  const sheet = e.source.getActiveSheet();
  const row = e.range.getRow();
  const column = e.range.getColumn();

  if ( sheet.getName() == "입력시트" && row > 1 && column == 1 ) {
    const targetCell = sheet.getRange(row,2,1,1);
    getCellState(targetCell, 'Pending');

    const sido = sheet.getRange(row,1,1,1).getValue();
    const range = filterJisa(sido, getStructreData());

    const rule = SpreadsheetApp.newDataValidation().requireValueInList(range, true).build();
    targetCell.setDataValidation(rule);
    getCellState(targetCell, 'Done');
  }
}

/**
 * 지사 행의 Edit Event
 */
function getNames(e) {
  const sheet = e.source.getActiveSheet();
  const row = e.range.getRow();
  const column = e.range.getColumn();

  if ( sheet.getName() == "입력시트" && row > 1 && column == 2 ) {
    const targetCell = sheet.getRange(row,3,1,1);
    getCellState(targetCell, 'Pending');

    const sido = sheet.getRange(row,1,1,1).getValue();
    const jisa = sheet.getRange(row,2,1,1).getValue();
    const range = filterName(sido, jisa, getStructreData());

    const rule = SpreadsheetApp.newDataValidation().requireValueInList(range, true).build();
    targetCell.setDataValidation(rule);
    getCellState(targetCell, 'Done');
  }
}

 

 

 

 

 

 

Edit event 를 하나로 묶어서 처리해도 되고, 나눠서 처리해도 된다.

아래와 같은 식으로

function onEdit3(e) {
  const sheet = e.source.getActiveSheet();
  const row = e.range.getRow();
  const column = e.range.getColumn();
	const inputSheetName = "입력시트";

  if ( sheet.getName() == inputSheetName && row > 1 && column > 3 ) {
    const targetCell = sheet.getRange(row,(column+1),1,1);
    getCellState(targetCell, 'Pending');

    const sido = sheet.getRange(row,column,1,1).getValue();
    var range;
    if ( column > 1 ) {
      const jisa = sheet.getRange(row,column(+1),1,1).getValue();
      range = filterName(sido, jisa, getStructreData());
    } else {
      range = filterJisa(sido, getStructreData());
    }

    const rule = SpreadsheetApp.newDataValidation().requireValueInList(range, true).build();
    targetCell.setDataValidation(rule);
    getCellState(targetCell, 'Done');
  }
}