Add one Column to Another, Then Delete the Column

google sheetsgoogle-apps-scriptgoogle-sheets-macro

This is a messy function but it works. I want to input values into column D, use the macro, have it add D values to C Values across from one another, then Delete all D values. I could write out C3:C246 and D3:246 like shown below but one, that would take forever, and two, there has to be a cleaner and quicker way.

function addValues() {
  
var ss = SpreadsheetApp.getActiveSheet();

var num1 = ss.getRange("C3").getValue();

var num2 = ss.getRange("D3").getValue();

  ss.getRange("C3").setValue(num1+num2);

  var num3 = ss.getRange("C4").getValue();

  var num4 = ss.getRange("D4").getValue();

  ss.getRange("C4").setValue(num3+num4);

  var num5 = ss.getRange("C5").getValue();

  var num6 = ss.getRange("D5").getValue();

  ss.getRange("C5").setValue(num5+num6);

  ss.getRange("D3:D246").clear();
};

Best Answer

Use this map-reduce pattern to calculate the row-by-row sum:

function replaceColumnsWithRowSum() {
  const ss = SpreadsheetApp.getActive();
  const sourceRange = ss.getRange('Sheet1!C3:D246');
  const targetRange = ss.getRange('Sheet1!C3:C246');
  const values = sourceRange.getValues();
  const rowSum = values.map(row => [row.reduce((sum, value) => sum + value)]);
  sourceRange.clearContent();
  targetRange.setValues(rowSum);
}
Related Topic