How can I make a function ignore positive numbers in a Google Sheet using Apps Script?-CodePudding

For my expense tracker, one of my custom functions in Google Sheets allows users to see how much they have spent on a clothing brand. Here is a picture from the Google Sheet:

The following function is used to achieve this:

function perCentBrand(brand){
   var sh = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
   var values = sh.getRange(2,1,sh.getLastRow()-1,sh.getLastColumn()).getValues();
   var total = 0;
   var sum = 0;
  values.forEach(function(row){
    total =row[1];
     if (row[2].toLowerCase() == brand.toLowerCase()){sum =row[1]}
  })
  var val = "You spent a total of "   (sum*-1)   " on "   brand   " out of "   (total*-1); 
  var ui = SpreadsheetApp.getUi();
  ui.alert(val)
}

However, this function also takes into account the positive numbers, which are supposed to be income figures, not expense figures. Therefore, when running the function, this message is displayed:

Since positive numbers are taken into account, the total says -270 when it should be 90. How can I make the function ignore the positive numbers?

Edit:

Alert after suggested answer:

Function after suggested answer:

function perCentBrand(brand){
   var sh = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
   var values = sh.getRange(2,1,sh.getLastRow()-1,sh.getLastColumn()).getValues();
   var total = 0;
   var sum = 0;
  values.forEach(function(row)
  {
    if(row[1] < 0 )
    {
      total =row[1];

    }
     else if (row[2].toLowerCase() == brand.toLowerCase()){sum =row[1]}
  })
  var val = "You spent a total of "   (sum*-1)   " on "   brand   " out of "   (total*-1); 
  var ui = SpreadsheetApp.getUi();
   ui.alert(val)
}

CodePudding user response：

The row[2] part needs to be a separate if inside the (row[1] < 0) part. Not an else if.

values.forEach(function(row) {
  if (row[1] < 0) {
    total =row[1];
    if (row[2].toLowerCase() == brand.toLowerCase()) {
      sum =row[1];
    }
  }
})

CodePudding user response：

Give this a shot:

function perCentBrand(brand) {
  const spreadSheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();

  const values = spreadSheet
    .getRange(2, 1, spreadSheet.getLastRow() - 1, spreadSheet.getLastColumn())
    .getValues();

  let total = 0;
  let sum = 0;

  values.forEach((row) => {
    const brandNamesMatch = row[2].toLowerCase() == brand.toLowerCase();
    const valueIsAnExpense = row[1] <= 0;

    if (valueIsAnExpense) {
      const expenseValue = Math.abs(row[1]);

      total  = expenseValue;

      if (brandNamesMatch) {
        sum  = expenseValue;
      }
    }
  });

  const alertText = `You spent $${sum} out of $${total} on ${brand}`;

  const modal = SpreadsheetApp.getUi();
  modal.alert(alertText);
}