Question 383254 in Javascript Question posted in
A very good W3school tutorial can be found here.

Javascript – Iterate Through a Range in Google Sheets to Generate a Sum Based on Conditions

Apologies in advance if this has been asked already. With Mint shuttering and forcing us to Credit Karma, I’m hoping to build a semi-automated budget and spending tracker in Google Sheets, and would love if someone could help me figure out how to formulate a forEach loop script.

I have four columns in the range I want to loop through: Month, Expense, Amount, Category. I found a script that loops through the range (below). What I need help with is generating an if statement that will identify values in each row, and, if the conditions are met, add up the value in the Amount column for each met condition.

Ideally, the RETURN VALUES HERE line would execute something to the effect of: "if a row has April in the Month column and Groceries in the Category column, add the Amount cell from that row to the sum." Hopefully this makes sense. I’m new to scripting and appreciate any help on this I can get.

function logAprilGroceries() {
 var range = SpreadsheetApp.getActive().getRangeByName("Statements");
 var values = range.getValues();
 values.forEach(function(row) {
   row.forEach(function(col) {
     RETURN VALUES HERE;
   });
 });
}
Tags:

2

Answers


  1. 0

    As recommended on https://developers.google.com/apps-script/guides/sheets, you should learn that Google Apps Script uses JavaScript as a programming language and that handling arrays is very common when working with Sheets.

    I don’t think that you will need the inner loop (row.forEach(function(col){...}); instead, use array indices, i.e., row[0] for the first column, row[1] for the second column and so on.

    Regarding the if statement, the syntax is if(expression){...}, where the expression might use comparison operators, i.e., === (equal), !== (not equal).

    There are a lot of resources to learn the basics of JavaScript. I like the JavaScript course from FreeCodeCamp as, it’s free and it’s focused on "vanilla JavaScript", no APIs, HTML, and other stuff that is not used on Google Apps Script server-side scripts.

    Login or Signup to reply.
  2. 0

    This function assumes it will be passed the ‘month’ and ‘category’ arguments. You could alternatively declare them as constants prior to the loop.

    function totalByMonthAndCategory(month, category) {
  const range = SpreadsheetApp.getActive().getRangeByName("Statements");
  const values = range.getValues();
  let sum = 0;
  for (let i = 0; i < values.length; i++) {
    let cols = values[i];
    if ((cols[0] == month) && (cols[2] == category)) {
      sum += cols[3];
    }
  }
  return sum;
}
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search