Apps Script로 Google Sheets의 조건부서식 설정하기

💡 Apps Script를 이용해 Google Sheets의 조건부서식을 적용하는 방법을 알아본다.

 

 

서식에 대한 요구사항이 들어왔다.

 

TransactionCurrency가 “KRW” 이고,

NetAmount, TaxAmount 값이 250,000 이상인 데이터들에 대해 강조를 하고싶다.

 

Class ConditionalFormatRuleBuilder | Apps Script | Google Developers

Class ConditionalFormatRuleBuilder  |  Apps Script  |  Google Developers

 

해당 사이트에서, 원하는 옵션을 사용하면 된다.

첫번째 요구사항인 “KRW” 값들을 강조해본다.

whenTextEqualTo 를 이용해 일치하는 데이터를 찾는다.

/**
 * 조건부서식 적용 - 일치하는 텍스트 강조
 * @param {obj} sheet         = 작업할 sheet
 * @param {string} compareTxt = 비교할 값
 */
function setConditionalFormatCompareText (sheet, compareTxt) {
  const applyRange = sheet.getRange('C2:C' + sheet.getLastRow());
  const rule = SpreadsheetApp.newConditionalFormatRule()
                            .whenTextEqualTo(compareTxt)
                            .setBackground("#ffcdc0")
                            .setBold(true)
                            .setRanges([applyRange])
                            .build();
  var rules = sheet.getConditionalFormatRules();
  rules.push(rule);
  sheet.setConditionalFormatRules(rules);
}

 

/**
 * BigQuery에서 가져온 데이터를 Sheets에 뿌려주기
 */
function getDataFromBigquery() {
  const datasetId = global_datasetId;
  const tableId = "standard_long";
  const columns = "BillingDocument, BillingQuantityUnit, TransactionCurrency, NetAmount, TaxAmount, BillingDocumentItemText"
  const queryString = 'SELECT  ' + columns + ' FROM `'
              +  GLOBAL_PROJECT_ID + "." + datasetId + "." + tableId + '` WHERE BillingDocumentItem = "82" ORDER BY BillingDocument ASC ;';

  const sheetUrl = SpreadsheetApp.getActiveSpreadsheet().getUrl();
  const sheetName = "Sheet2"
  const dataRange = queryAndBinding(sheetUrl, sheetName, queryString, 2, 1, "N", null, "Y");
  
  const sheet = SpreadsheetApp.openByUrl(sheetUrl).getSheetByName(sheetName);
  if ( dataRange != null ) {
    // 조건부서식 제거
    sheet.clearConditionalFormatRules();
    // 테두리 설정
    setTableBorder(sheet, dataRange, true);
    // 조건부서식 적용 - 일치하는 텍스트 강조
    setConditionalFormatCompareText(sheet, "KRW");
  }
}

 

 

 

이제 두번째 요구사항인, 숫자 비교를 진행한다.

/**
 * 조건부서식 적용 - 특정수치 이상 값 강조
 * @param {obj} sheet         = 작업할 sheet
 * @param {int} compareNumber = 비교할 값
 */
function setConditionalFormatCompareNumber (sheet, compareNumber) {
  const applyRange = sheet.getRange('D2:E' + sheet.getLastRow());
  const rule = SpreadsheetApp.newConditionalFormatRule()
                            .whenNumberGreaterThanOrEqualTo(compareNumber)
                            .setFontColor("red")
                            .setBold(true)
                            .setRanges([applyRange])
                            .build();
  var rules = sheet.getConditionalFormatRules();
  rules.push(rule);
  sheet.setConditionalFormatRules(rules);
}

/**
 * BigQuery에서 가져온 데이터를 Sheets에 뿌려주기
 */
function getDataFromBigquery() {
  const datasetId = global_datasetId;
  const tableId = "standard_long";
  const columns = "BillingDocument, BillingQuantityUnit, TransactionCurrency, NetAmount, TaxAmount, BillingDocumentItemText"
  const queryString = 'SELECT  ' + columns + ' FROM `'
              +  GLOBAL_PROJECT_ID + "." + datasetId + "." + tableId + '` WHERE BillingDocumentItem = "82" ORDER BY BillingDocument ASC ;';

  const sheetUrl = SpreadsheetApp.getActiveSpreadsheet().getUrl();
  const sheetName = "Sheet2"
  const dataRange = queryAndBinding(sheetUrl, sheetName, queryString, 2, 1, "N", null, "Y");
  
  const sheet = SpreadsheetApp.openByUrl(sheetUrl).getSheetByName(sheetName);
  if ( dataRange != null ) {
    // 조건부서식 제거
    sheet.clearConditionalFormatRules();
    // 테두리 설정
    setTableBorder(sheet, dataRange, true);
    // 조건부서식 적용 - 일치하는 텍스트 강조
    setConditionalFormatCompareText(sheet, "KRW");
    // 조건부서식 적용 - 특정수치 이상 값 강조
    setConditionalFormatCompareNumber(sheet, 250000);
  }
}

조건부서식을 적용할때마다 (조회할 때마다) clearConditionalFormatRules 를 실행해줘야한다.

안그러면 조건부 서식이 꼬일 수 있다.

sheet.clearConditionalFormatRules();