Apps Script로 Google Sheets의 소계 데이터 만들기

 💡Apps Script로 Google Sheets에서 소계를 생성해본다.

 

 

데이터들을 가져와서 뿌려줬다.

원가요소 별 소계 subtotal 를 생성해 달라는 요구 사항이 들어왔다.

쿼리로 만들 수 있지만, SUM을 이용해서 만들어 달라는 요구 사항이 들어왔다.

 

 

 

 

원본 데이터를 조회 해 sheet에 뿌려준 후,

원가요소들을 비교하여 행추가를 하고 sum 수식을 생성하여 추가해준다.

 

/**
 * BigQuery에서 가져온 데이터를 Sheets에 뿌려주기
 */
function getDataFromBigquery() {
    const datasetId = global_datasetId;
    const tableId = "standard_long";
    const columns = 'BillingDocument, BillingQuantityUnit, TransactionCurrency, NetAmount, TaxAmount, "" AS NULLTXT, 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 ) {
        // 소계 row 생성
        setSubTotal(sheet, 10, 4);
    }
}

 

 

 

1. 데이터를 비교해, 행추가 할 부분을 찾는다.

2. 빈 행을 추가한다.

3. F부터 J 까지 각 열별로 SUM하는 수식을 배열로 만들고, 해당 수식을 array에 담는다.

4. setFormulas() 를 이용해 수식을 적용하고

5. backgorund color, 셀병합, 중앙정렬 등의 서식을 적용한다.

/**
 * 소계 row 생성
 * @param {string}  sheet     = 처리 할 spreadsheet
 * @param {integer} startRow  = 시작 행
 * @param {integer} targetIdx = 비교할 column index
 */
function setSubTotal(sheet, startRow, targetIdx) {
  var endRow  = sheet.getLastRow()-startRow+1  // 마지막 열
  var data    = sheet.getSheetValues(startRow, targetIdx, endRow, 1);

  var addRowCnt = 0;          // 행추가
  var topIdx    = startRow;   // top 영역

  for ( i in data ) {
    if ( i > 0 ) {
      const upV   = String(data[i-1][0]).substring(0, 1);
      const thisV = String(data[i][0]).substring(0, 1);

      if ( upV != thisV ) {

        var rowNum = startRow + i + addRowCnt;

        sheet.insertRowBefore(rowNum);
        sheet.getRange("A"+rowNum).setValue("소계");
        
        const formularF = "=SUM(F"+topIdx+":F"+(rowNum-1)+")";
        const formularG = "=SUM(G"+topIdx+":G"+(rowNum-1)+")";
        const formularH = "=SUM(H"+topIdx+":H"+(rowNum-1)+")";
        const formularI = "=SUM(I"+topIdx+":I"+(rowNum-1)+")";
        const formularJ = "=SUM(J"+topIdx+":J"+(rowNum-1)+")";

        const items       = [formularF, formularG, formularH, formularI, formularJ];
        const arrFormulas = Array.apply(null, Array(1)).map(_ => items);

        sheet.getRange("F"+rowNum+":J"+rowNum).setFormulas(arrFormulas);
        sheet.getRange("A"+rowNum+":J"+rowNum).setBackground("#f0f0f0");
        sheet.getRange("A"+rowNum+":E"+rowNum).merge().setHorizontalAlignment("center");

        addRowCnt++;
        topIdx = rowNum+1;

      } else if ( (i+1) == data.length ) {

        var rowNum = startRow + i + addRowCnt + 1;

        sheet.insertRowBefore(rowNum);
        sheet.getRange("A"+rowNum).setValue("소계");

        const formularF = "=SUM(F"+topIdx+":F"+(rowNum-1)+")";
        const formularG = "=SUM(G"+topIdx+":G"+(rowNum-1)+")";
        const formularH = "=SUM(H"+topIdx+":H"+(rowNum-1)+")";
        const formularI = "=SUM(I"+topIdx+":I"+(rowNum-1)+")";
        const formularJ = "=SUM(J"+topIdx+":J"+(rowNum-1)+")";

        const items       = [formularF, formularG, formularH, formularI, formularJ];
        const arrFormulas = Array.apply(null, Array(1)).map(_ => items);
        
        sheet.getRange("F"+rowNum+":J"+rowNum).setFormulas(arrFormulas);
        sheet.getRange("A"+rowNum+":J"+rowNum).setBackground("#f0f0f0").setBorder(true, true, true, true, true, true);
        sheet.getRange("A"+rowNum+":E"+rowNum).merge().setHorizontalAlignment("center");
      }
    }
  }
}