Google Script subtract an entire column from the other

Question

So I'm currently working on a meal planner that looks at my current inventory of ingredients and then looks through the recipes in the spreadsheet to determine what I do/don't need to make those recipes that week and then returns a shopping list. I want to add a button to update that inventory by adding the ingredients from the shopping list. I have the values set up in different columns, but I can't figure out the code to add the values respectively. I have never done any coding, so this is based off of what I found on Google. Here's the code I currently have:

function AddtoInventory() {

var ss = SpreadsheetApp.getActiveSheet();
  var num1 = ss.getRange('AA3:AA').getValue();
  var num2 = ss.getRange('E3:E').getValue();
  ss.getRange('E3:E').setValue(num1+num2);
  
}

But this just adds the first set of values (AA3+E3) and changes the current inventory for every ingredient to that same value. I want it to add AA3+E3 and change the value of E3 to that sum and then AA4+E4 and change the value of E4 to that sum and so on if that makes sense.

Answer 1

• Use getValues() and setValues() instead of getValue() and setValue()

• Use .map() for addition:

function AddtoInventory() {
  const ss = SpreadsheetApp.getActiveSheet(),
    lr = ss.getLastRow(),
    valuesAA = ss.getRange('AA3:AA' + lr).getValues(),
    rngE = ss.getRange('E3:E' + lr),
    valuesE = rngE.getValues(),
    add = (num1,num2) => num1 + num2;
  rngE.setValues(valuesE.map((row, idx) => [add(row[0],valuesAA[idx][0])]));  
}

Answer 2

Subtract and entire column

function AddtoInventory() {
  const ss = SpreadsheetApp.getActive();
  const sh = ss.getActiveSheet();
  let col27 = sh.getRange(3, 27, sh.getLastRow() - 2).getValues();
  let col5 = sh.getRange(3, 5, sh.getLastRow() - 2).getValues();
  col5.forEach((r, i) => {
    r[0] -= col27[i][0];
  });
  sh.getRange(3, 5, col5.length, col5[0].length).setValues(col5);
}