Apps Script로 Google Sheets의 특정 셀 값을 계산해 다른 셀에 값 뿌려주기

 

아래의 Google Sheets에서,

max 값과 min 값의 차이를 diff 컬럼에 계산 해 넣으려고 한다.

 

 

 

행 수 만큼 max와 min값을 가져와, 연산한 뒤 셀에 계산된 값 할당

 

문제는 한 셀 씩 값을 채우기 때문에, 한 셀 씩 값이 채워지는게 눈에 보임.

느려보인다.

function calcDataSingle() {
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("tempature_kr");
  for (var i=2; i<sheet.getLastRow()+1; i++) {
    const minValue = sheet.getRange(i, 4).getValue();
    const maxValue = sheet.getRange(i, 5).getValue();
    
    sheet.getRange(i, 6).setValue(maxValue - minValue);
  }
}

 

 

위방법을 보완하고자,

계산한 값들을 배열에 담은 뒤, setValues로 한번에 값을 할당

function calcDataMulti() {
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("tempature_kr");
  
  const startCol = 2;
  const lastRow = sheet.getLastRow();

  let arrValue = [];
  for (var i=startCol; i<lastRow+1; i++) {
    const minValue = sheet.getRange(i, 4).getValue();
    const maxValue = sheet.getRange(i, 5).getValue();

    arrValue.push([maxValue - minValue]);
  }
  
  sheet.getRange(startCol, 6, (lastRow-1), 1).setValues(arrValue);
}

 

 

위에 두 방법은, Apps Script에서 값을 계산 해 입력하는 방식이고

Google Sheets에서 해당 값이 어떻게 나왔는지 확인하기위해 수식으로 셀을 채워본다.

 

 

 

 

이것도 속도가 마음에 들지 않는다.

Array에 map을 이용해 값을 가공

function calcData() {
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("tempature_kr");
  
  const startCol = 2;
  const lastRow = sheet.getLastRow();
  
  const data = sheet.getRange(2, 4, (lastRow-1), 2).getValues();
  const setData = data.map(function(row) {
    return [row[1]-row[0]];
  });

  sheet.getRange(startCol, 6, (lastRow-1), 1).setValues(setData);
}

 

 

R1C1 을 이용해, 수식으로 값을 할당

function calcDataMultiR1C1() {
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("tempature_kr");
 
  const startCol = 2;
  const lastRow = sheet.getLastRow();
  
  let arrValue = [];
  for (var i=startCol; i<lastRow+1; i++) {
    arrValue.push(["=R[0]C[-1]-R[0]C[-2]"]);
  }
  sheet.getRange(startCol, 6, (lastRow-1), 1).setFormulas(arrValue);
}

 

 

셀서식 생성후 복사하기

첫 행(F2)에 서식을 생성 후,

아래 셀들에 복사를 한다.

function calcDataCopyR1C1() {
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("tempature_kr");
 
  const startCol = 2;
  const lastRow = sheet.getLastRow();
  
  sheet.getRange(startCol, 6, 1, 1).setFormula("=R[0]C[-1]-R[0]C[-2]");

  const fillRange = sheet.getRange(startCol, 6, (lastRow-1), 1);
  sheet.getRange(startCol, 6, 1, 1).copyTo(fillRange);
}

 

- 참고

 

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