Apps Script로 Google Sheets의 Dropdown 생성하기

 

💡 Apps Script로 Google Sheets에서 Dropdown(Data Validation)을 생성하는 방법을 알아본다.

 

 

 

Google Sheets에서 데이터를 입력하거나 조회 할 때,

Dropdown 으로 입력 할 경우가 생긴다.

1. Google Sheets 데이터 영역을 Dropdown(Data validation) 으로 생성

function settingDropdown() {
    const sheet = SpreadsheetApp.getActive().getSheetByName("Sheet1");  

    // Dropdown 데이터 영역 설정
    const validationCell = sheet.getRange("C2:C12");
    // 생성할 영역 설정
    const setCell = sheet.getRange("E2");

    // 생성할 영역 초기화
    setCell.clearContent().clearDataValidations();

    // validation rule 생성
    const validationRule = SpreadsheetApp.newDataValidation()
					 .requireValueInRange(validationCell)
                                         .build();

    // 생성할 영역에 rule 할당
    setCell.setDataValidation(validationRule);
}

2. BigQuery 데이터를 Dropdown(Data validation) 으로 생성

function settingDropdownFromBigQuery () {
    const sheet = SpreadsheetApp.getActive().getSheetByName("Sheet1");  

    // Dropdown 데이터 영역 설정
    const setCell = sheet.getRange("F2");

    // 생성할 영역 초기화
    setCell.clearContent().clearDataValidations();

    // Query 생성
    var datasetId = global_datasetId;
    var tableId = "standard_long";
    var queryString = 'SELECT BillingDocumentItemText FROM `'
           +  GLOBAL_PROJECT_ID + "." + datasetId + "." + tableId 
           + '` WHERE TransactionCurrency = "KRW" AND BillingDocumentItem = "82" ;'
    
    // Query 호출
    var result = queryExcute(queryString);

    // 배열에 담기
    const arr = Array.from({length: result.length}, (undefined, i) => result[i].f[0].v );

    // validation rule 생성
    var rule = SpreadsheetApp.newDataValidation().requireValueInList(arr).build();

    // 생성할 영역에 rule 할당
    setCell.setDataValidation(rule);

    // 맨위 첫번째 값 세팅
    setCell.setValue(arr[0]);
}

 

3. 년도 / 월 dropdown 생성하기

/**
 * 년도 셋팅
 * @param {string} sheetUrl = 해당 function을 호출한 sheet의 URL
 */
function settingYearDropDownList(sheetUrl) {
    const thisYear  = common.getDateTime().substr(0,4);
    var arr         = new Array();

    for ( var i=thisYear; i>=2019; i-- ) {
    	arr.push(i);
    }

    const rule      = SpreadsheetApp.newDataValidation().requireValueInList(arr)
                        				.setAllowInvalid(false)
                                 			.build();
    const sheet	    = SpreadsheetApp.getActive().getSheetByName("Sheet1");  

    sheet.getRange("C4").setDataValidation(rule);
    sheet.getRange("C4").setValue(thisYear);
}

/**
 * 월 셋팅
 * @param {string} sheetUrl = 해당 function을 호출한 sheet의 URL
 */
function settingMonthDropDownList(sheetUrl) {
    const thisMonth = common.getDateTime().substr(4,2);
    const arr       = Array.from({length: 12}, (undefined, i) => i+1);
    const rule      = SpreadsheetApp.newDataValidation().requireValueInList(arr)
                                           	     .setAllowInvalid(false)
                                         	     .build();
    const sheet     = SpreadsheetApp.getActive().getSheetByName("Sheet1");

    sheet.getRange("D4").setDataValidation(rule);
    sheet.getRange("D4").setValue(thisMonth);
}