Apps Script로 Google Sheets의 영역(카테고리) 별 음영 처리

 

💡 Apps Script로 BigQuery의 데이터를 Google Sheets에 뿌려주고, 데이터의 case에 따른 음영을 처리하는 예제이다.

 

 

 

 

Apps Script로 BigQuery 데이터를 Google Sheets에 뿌려주기

 

BigQuery에서 데이터를 가져와서 Google Sheet에 뿌려주었다.

 

추가 요구 사항이 들어왔다.

• [매출실적]
• [매출분석]
• [원가]

별로 동일한 음영 처리를 해야 한다.

 

 

흠...

 

Apps Script로 처리를 해보려 한다.

하지만 구분 지을 수 있는 방법이 없다.

 

그래서 구분할 수 있는 코드를 조회해 뿌려준다.

 

조회할 때 더미 컬럼을 가져왔으니, 테두리 처리에서 컬럼 두개를 빼고

테두리 처리를 해준다.

/**
 * 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 ) {
    // 테두리 두칸 빼고 처리
    dataRange.endColumn = dataRange.endColumn - 2;
    // 테두리 설정
    setTableBorder(sheet, dataRange, true);
  }
}

function setTableBorder(sheet, dataRange, flag) {
  try {
    sheet.getRange(dataRange.startRow, dataRange.startColumn, dataRange.endRow, dataRange.endColumn).setBorder(flag, flag, flag, flag, flag, flag);
  } catch (error) {
    const msg01 = error.message;
    alertMessage("❌ Error", "(setTableBorder) Error Message : " + msg01);
    Logger.log(msg01);
  }
}

 

 

 

 

/**
 * 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 ) {
    // 테두리 두칸 빼고 처리
    dataRange.endColumn = dataRange.endColumn - 2;
    // 테두리 설정
    setTableBorder(sheet, dataRange, true);
	// 값 비교해서 음영 처리
    setBackGroundColorCompareData(sheet, 10, 7, 9, "#E2EFDA", "4", "equal");
    setBackGroundColorCompareData(sheet, 10, 7, 9, "#FFF2CC", "9", "equal");
    setBackGroundColorCompareData(sheet, 10, 7, 2, "#d0d0d0", "소계", "like");
  }
}

/**
 * 데이터 비교하여 해당 행 background 색상 칠하기
 * @param {object} spreadSheet  = 해당 function을 호출한 spreadsheet
 * @param {string} startRow     = 시작 행번호
 * @param {string} lastColumn   = 마지막 열번호       - 조회조건(헤더)이 데이터(바디) 보다 더 넓을경우 별도지정. 아니면 null
 * @param {string} targetIdx    = 비교할 열 인덱스
 * @param {string} color        = 칠할 색상
 * @param {string} type         = 타입 "equal" / "like"
 */
function setBackGroundColorCompareData(spreadSheet, startRow, lastColumn, targetIdx, color, valueToCompare, type) {
  if ( isNull(lastColumn) ) {
    lastColumn = spreadSheet.getLastColumn();
  }
  const endRow  = spreadSheet.getLastRow() - startRow + 1  // 마지막 열
  const data    = spreadSheet.getSheetValues(startRow, targetIdx, endRow, 1);

  for ( i in data ) {
    const value = String(data[i][0]);

    if ( type == "equal" ) {
      if ( value == valueToCompare ) {
        const currentRow = parseInt(startRow) + parseInt(i);
        spreadSheet.getRange(currentRow, 1, 1, lastColumn).setBackground(color);
      }
    } else if ( type == "like" ) {
      if ( value.indexOf(valueToCompare) >= 0 ) {
        const currentRow = parseInt(startRow) + parseInt(i);
        spreadSheet.getRange(currentRow, 1, 1, lastColumn).setBackground(color);
      }
    }
  }
}
function setTableBorder(sheet, dataRange, flag) {
  try {
    sheet.getRange(dataRange.startRow, dataRange.startColumn, dataRange.endRow, dataRange.endColumn).setBorder(flag, flag, flag, flag, flag, flag);
  } catch (error) {
    const msg01 = error.message;
    alertMessage("❌ Error", "(setTableBorder) Error Message : " + msg01);
    Logger.log(msg01);
  }
}

 

그리고 해당 컬럼 숨김 처리 해주었다.

 

뭔가 더 좋은 방법이 있을 것 같긴 한데,

setBackGroundColorCompareData() 함수를 공통에서 실행하게 분리하였다.

다른데서도 쓰기위해…