I'm having a tough time here, and I know it can't be that difficult. I have a for loop that runs over a range of cell values in a Google Apps script I'm trying to write, and each time it loops, it checks to see if a certain condition is true. If it's true, for the time being, it's logging the results to Logger.log().

I need the total number of results that show up in Logger.log() (in this case, it would be 10) and the best way I was able to think of doing that would be to count up by 1 each time that conditional is true in the for loop. It should be very simple to get a numerical value of how many results, but I'm inexperienced and have been searching for hours for anything that I think might be able to help me and I just can't figure it out. Here's the code I'm using.

var currentsheet = SpreadsheetApp.getActiveSheet();
var values = currentsheet.getRange("A28:E").getValues();

for (i=0; i<values.length; i++) {
if (values[i][3] === "Section 179 Depreciation") {
  var date = values[i][0];
  var amount = values[i][1];
  var desc = values[i][2];
  var expType = values[i][3];
  var notes = values[i][4];

 }
}

If someone could fill me in on what I'm missing, I would appreciate it very much!

I'm having a tough time here, and I know it can't be that difficult. I have a for loop that runs over a range of cell values in a Google Apps script I'm trying to write, and each time it loops, it checks to see if a certain condition is true. If it's true, for the time being, it's logging the results to Logger.log().

I need the total number of results that show up in Logger.log() (in this case, it would be 10) and the best way I was able to think of doing that would be to count up by 1 each time that conditional is true in the for loop. It should be very simple to get a numerical value of how many results, but I'm inexperienced and have been searching for hours for anything that I think might be able to help me and I just can't figure it out. Here's the code I'm using.

var currentsheet = SpreadsheetApp.getActiveSheet();
var values = currentsheet.getRange("A28:E").getValues();

for (i=0; i<values.length; i++) {
if (values[i][3] === "Section 179 Depreciation") {
  var date = values[i][0];
  var amount = values[i][1];
  var desc = values[i][2];
  var expType = values[i][3];
  var notes = values[i][4];

 }
}

If someone could fill me in on what I'm missing, I would appreciate it very much!

• javascript
• if-statement
• for-loop

• 4 Get a counter, set it to zero before for and inside if increment it by one. After for, you'll get what you want – Tushar Commented Dec 30, 2015 at 8:31
• 1 Putting a counter inside if closure does not work for you? Or is there another problem that you are faceing? It should be really straightforward. – Tornike Shavishvili Commented Dec 30, 2015 at 8:50
• @blindProgrammer I was going to point out that I mentioned I'm inexperienced, but someone decided to edit that out so you had no way of knowing. I rolled back though. But yes, I'm fairly inexperienced and it's been a while since I've last done anything with javascript. – Soundfx4 Commented Dec 30, 2015 at 19:05
• @Tushar thank you, that really was simple and straightforward. I must have been more tired than I realized. – Soundfx4 Commented Dec 30, 2015 at 19:11
• 1 @Soundfx4 My apologies! I use a counter. – Tornike Shavishvili Commented Dec 30, 2015 at 19:43

5 Answers 5

You can use Array.prototype.filter to filter values and get its length:

var filtered = values.filter(function(x) { 
    return x[3] === "Section 179 Depreciation"; 
});
var count = filtered.length;

or using ES6 arrow functions:

var filtered = values.filter(x => x[3] === "Section 179 Depreciation");
var count = filtered.length;

It will be more convenient to reuse these filtered values, for example to log them:

filtered.forEach(function(x) { 
    var date = x[0];
    var amount = x[1];
    var desc = x[2];
    var expType = x[3];
    var notes = x[4];
    // log
});

It now iterates through your array twice, which can affect performance, but it is definitely improving readability. Use this if performance is not critical.

If you need to use the data I would save it to an array so you get the array methods to use.

var currentsheet = SpreadsheetApp.getActiveSheet();
var values = currentsheet.getRange("A28:E").getValues();
var section179 = [];

for (i=0; i<values.length; i++) {
if (values[i][3] === "Section 179 Depreciation") {
  var date = values[i][0];
  var amount = values[i][1];
  var desc = values[i][2];
  var expType = values[i][3];
  var notes = values[i][4];
    
  section179.push({
    date: date,
    amount: amount,
    desc: desc,
    expType: expType,
    notes: notes
  });
  
 }
}

console.log( section179.length );
console.log( total( section179, 'amount' ) );

// example total up all the results of a number property
function total( arr, prop ){
  return arr.reduce(function( ret, curr ){
    return ret + curr[ prop ];
  }, 0);
}

Take a global variable counter and initialize it with 0 and increase the value of counter whenever it goes into the if condition. It will give you how many times the condition is true. Whenever you want to start the count just initialize the counter to 0.

var currentsheet = SpreadsheetApp.getActiveSheet();
var values = currentsheet.getRange("A28:E").getValues();

var counter = 0;
for (i = 0; i < values.length; i++) {
    if (values[i][3] === "Section 179 Depreciation") {
        var date = values[i][0];
        var amount = values[i][1];
        var desc = values[i][2];
        var expType = values[i][3];
        var notes = values[i][4];
        counter++;
    }
}

var currentsheet = SpreadsheetApp.getActiveSheet();
var values = currentsheet.getRange("A28:E").getValues();
var j=0;
for (i=0; i<values.length; i++) {
if (values[i][3] === "Section 179 Depreciation") {
  var date = values[i][0];
  var amount = values[i][1];
  var desc = values[i][2];
  var expType = values[i][3];
  var notes = values[i][4];
   j++;
 }
}
alert(j);

so you can get the total value after true the condition in the loop

This is Javascript For Loop

In this case i would use counter following way:

var currentsheet = SpreadsheetApp.getActiveSheet();
var values = currentsheet.getRange("A28:E").getValues();

var counter = 0; // counter
for (i=0; i<values.length; i++) {
if (values[i][3] === "Section 179 Depreciation") {
  var date    = values[i][0];
  var amount  = values[i][1];
  var desc    = values[i][2];
  var expType = values[i][3];
  var notes   = values[i][4];
  counter++; //we encrease counter every time condition is true 
 }
}