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.

CodePudding user response：

• 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])]));  
}

CodePudding user response：

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);
}