0
190
ViewsHow can I make a function ignore positive numbers in a Google Sheet using Apps Script?
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)
}
·
Juan Pablo Isaza
2 answers
Answer question0
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];
}
}
})
·
Juan Pablo Isaza
Report
0
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);
}
·
Juan Pablo Isaza
Report
Answer question