Apps Script로 Google Sheets의 수식 적용하기

 

요구사항이 또 들어왔다.

NetAmount 에서 TaxAmount 를 뺀 값을 보여달라고 한다.

 

쿼리를 이용하면 간단하다.

하지만 해당 Sheets에서 값들을 변경하면서 수치들의 변화를 보고싶다고한다.

 

 

 

 

 

 

 

일단 칸을 만든다.

반복되는 행들에 참조하는 셀들의 값이 달라야한다.

• F2 = D2 - E2
• F3 = D3 - E3
• F4 = D4 - E4

그렇기 때문에

setFormula 가 아닌,

var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getSheets()[0];

var cell = sheet.getRange("B5");
cell.setFormula("=SUM(B3:B4)");

setFormulaR1C1을 써야한다.

var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getSheets()[0];

var cell = sheet.getRange("B5");
// This sets the formula to be the sum of the 3 rows above B5
cell.setFormulaR1C1("=SUM(R[-3]C[0]:R[-1]C[0])");

Class Range | Apps Script | Google Developers

Class Range  |  Apps Script  |  Google Developers

 

R1C1이란 간단히

우리가 알고 있는 “A1”, “B2” 과 같은 범위가 아니라

Row Column 단위로 좌표값을 컨트롤 하는것이다.

엑셀에서는 [옵션] - [수식] - [R1C1 참조 스타일] 을 체크하면 확인 할 수 있다.

R1C1 스타일을 참조하면, 우측과 같이 바뀌는것을 확인할 수 있다.

참고는 여기서

An Ultimate Guide To Learn R1C1 Reference Style in Excel

An Ultimate Guide To Learn R1C1 Reference Style in Excel

 

 

 

 

내가 할당할 셀은 “F” 셀이고, 동일한 행의 “D”와 “E” 셀을 가져와야 한다.

그럼 F2열에는 “=D2-E2” 가 들어가야 하고,

D2는 F2 기준으로 Column이 -2, E2는 F2 기준으로 Column이 -1 이니까,

아래와 같이 설정하면 된다.

"=R[0]C[-2]-R[0]C[-1]"

 

/**
 * 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 ) {
    // 조건부서식 제거
    sheet.clearConditionalFormatRules();
    // 테두리 설정
    setTableBorder(sheet, dataRange, true);

    // R1C1 셀수식 적용
    var calculateCell = sheet.getRange("F2:F"+sheet.getLastRow());
    calculateCell.setFormulaR1C1("=R[0]C[-2]-R[0]C[-1]");

    // 조건부서식 적용 - 일치하는 텍스트 강조
    setConditionalFormatCompareText(sheet, "KRW");
    // 조건부서식 적용 - 특정수치 이상 값 강조
    setConditionalFormatCompareNumber(sheet, 250000);
  }
}