Count Numbers Gsheet / Javascript

#1

Can someone help me and tell me why it doesn’t work with bigger or smaller?
The function should count how many numbers are below 10.
thank you

This code doesn’t work and give me a 43 back.

function calculationFirstColumn() { 
  var sheet = SpreadsheetApp.getActiveSheet();
  var originalData = sheet.getRange(2, 2, sheet.getLastRow() -1, 1).getValues();
  var total = 0;
  for (var i = 0; i < originalData.length; i++){
    if(originalData[i][0] <= 10){
      total += originalData[i][0];
    }
    Logger.log(total);
  }
  sheet.getRange("B18").setValue(total); 
}

This code works and give me a 3 back.

function calculationFirstColumn() { 
  var sheet = SpreadsheetApp.getActiveSheet();
  var originalData = sheet.getRange(2, 2, sheet.getLastRow() -1, 1).getValues();
  var total = 0;
  for (var i = 0; i < originalData.length; i++){
    if(originalData[i][0] == 1){
      total += originalData[i][0];
    }
    Logger.log(total);
  }
  sheet.getRange("B18").setValue(total); 
}

wh3y
wh3y410×608 30.1 KB

#2

what should your code do? what it does instead?

if you don’t explain that, we don’t know what to check. We can’t read your mind.

1 Like
#3

Right. The function should count the numbers below 10. If the function = 1 I get the right number.

BUT If the function is “<=” or “>=” the function does not work. It is the first if statement.

#4

so you mean that this line doesn’t work?

  if(originalData[i][0] == 1){

what happens if you change that?

can you add console.log statements for originalData[i][0] and total and your if condition inside the loop? and show what happens with == and <=?

1 Like
#5

No exactly the other way around.

Unbenan123123nt
Unbenan123123nt1276×672 86.8 KB

  if(originalData[i][0] == 1){...................... work
  if(originalData[i][0] <= 10){................... doesn't work
#6

please log the values to the console. Or somewhere.

1 Like
#7

I just noticed something. Thank you in advance for your help.
The numbers are summed up. But what I actually want is that the function counts how often a number occurs in a column.

Example:
if (originalData [i] [0] == 13) {
The function writes a 13 on the sheet with this line.
But what I want him to do is write a 1 because the number 13 is only once in the first column.

#8

if you don’t want to sum them, why do you do this?

1 Like
#9

that does not work either. I just do not understand -.-’
originalData [i] [0] <= 10) { total++;}

#10

what output do you get with this?

As I do not have your sheet, I can’t try it myself, so please add console.log() statements as I asked before and show what is printed to the console.

1 Like
#11

I solved the problem. Unfortunately I can not explain why I need the second condition.
I once gave you a vivid picture.

Again what I want. I want to filter out the number of numbers that are less than 10 in the first column.

12 is the correct number because 12 numbers are under 10. I do not know exactly what the 32 are and do not understand why I get this number.
now
now1039×567 61.3 KB

// With this code I get the right number 12.
if(originalData[i][0] <= 10 && originalData[i][0] !='')

// With this code I get the wrong number.
 if(originalData[i][0] <= 10)
#12

just a picture of the code is not enough to debug.
I need to see stuff printed to the console. Which you have never provided.
It is fine if you don’t know how that works, but you need to say so.
the console.log statement should be your best friend when you debug.

I need to see what each originalData[i][0] is
if you also could log the whole of originalData it would be awesome.
You also don’t know what the value of originalData is.

Probably you have many empty cells over which you are iterating.
I have no idea, as I would need to see what the values are.

1 Like
#13

Okay, I looked at how it works. I hope you can understand it better now. I still don’t understand why the array doesn’t have the defined area.

function calculationFirstColumn() { 
  var sheet = SpreadsheetApp.getActiveSheet();
  var originalData = sheet.getRange(2, 2, sheet.getLastRow() -1, 1).getValues();
  var total = 0; 
  for (var i = 0; i < originalData.length; i++){ 
    
    
      if(originalData[i][0] <= 10 && originalData[i][0] !=''){  
         total ++;

Logger.log(originalData[i][0]);
--------------------------------
Protokoll:
[20-05-13 22:11:54:061 CEST] 6.0
[20-05-13 22:11:54:064 CEST] 4.0
[20-05-13 22:11:54:066 CEST] 1.0
[20-05-13 22:11:54:068 CEST] 5.0
[20-05-13 22:11:54:071 CEST] 1.0
[20-05-13 22:11:54:073 CEST] 7.0
[20-05-13 22:11:54:075 CEST] 2.0
[20-05-13 22:11:54:077 CEST] 2.0
[20-05-13 22:11:54:078 CEST] 1.0
[20-05-13 22:11:54:080 CEST] 9.0
[20-05-13 22:11:54:083 CEST] 3.0
[20-05-13 22:11:54:085 CEST] 2.0
-------------------------------------------

function calculationFirstColumn2() { 
  var sheet = SpreadsheetApp.getActiveSheet();
  var originalData = sheet.getRange(2, 2, sheet.getLastRow() -1, 1).getValues();
  var total = 0; 
  for (var i = 0; i < originalData.length; i++){
    
    
      if(originalData[i][0] <= 10){    
        total ++;

Logger.log(originalData[i][0]);

Protkoll:
[20-05-13 22:13:49:523 CEST] 6.0
[20-05-13 22:13:49:527 CEST] 4.0
[20-05-13 22:13:49:530 CEST] 1.0
[20-05-13 22:13:49:533 CEST] 5.0
[20-05-13 22:13:49:536 CEST] 1.0
[20-05-13 22:13:49:538 CEST] 7.0
[20-05-13 22:13:49:541 CEST] 2.0
[20-05-13 22:13:49:544 CEST] 2.0
[20-05-13 22:13:49:546 CEST] 1.0
[20-05-13 22:13:49:548 CEST] 9.0
[20-05-13 22:13:49:551 CEST] 3.0
[20-05-13 22:13:49:554 CEST] 2.0
[20-05-13 22:13:49:556 CEST] 
[20-05-13 22:13:49:559 CEST] 
[20-05-13 22:13:49:562 CEST] 
[20-05-13 22:13:49:565 CEST] 
[20-05-13 22:13:49:568 CEST] 
[20-05-13 22:13:49:570 CEST] 
[20-05-13 22:13:49:574 CEST] 
[20-05-13 22:13:49:577 CEST] 
[20-05-13 22:13:49:580 CEST] 
[20-05-13 22:13:49:582 CEST] 
[20-05-13 22:13:49:585 CEST] 
[20-05-13 22:13:49:588 CEST] 
[20-05-13 22:13:49:591 CEST] 
[20-05-13 22:13:49:593 CEST] 
[20-05-13 22:13:49:599 CEST] 
[20-05-13 22:13:49:602 CEST] 
[20-05-13 22:13:49:605 CEST] 
[20-05-13 22:13:49:608 CEST] 
[20-05-13 22:13:49:610 CEST] 
[20-05-13 22:13:49:613 CEST] 
----------------------------------------

Logger.log(originalData);
Protokoll:
[20-05-13 22:18:00:838 CEST] [[6.0], [4.0], [1.0], [5.0], [1.0], [7.0], [2.0], [2.0], [12.0], [15.0], [1.0], [9.0], [13.0], [3.0], [2.0], [], [12.0], [], [], [], [], [], [], [], [], [], [], [], [], [], [], [], [], [], [], []]
[20-05-13 22:18:00:841 CEST] [[6.0], [4.0], [1.0], [5.0], [1.0], [7.0], [2.0], [2.0], [12.0], [15.0], [1.0], [9.0], [13.0], [3.0], [2.0], [], [12.0], [], [], [], [], [], [], [], [], [], [], [], [], [], [], [], [], [], [], []]
[20-05-13 22:18:00:842 CEST] [[6.0], [4.0], [1.0], [5.0], [1.0], [7.0], [2.0], [2.0], [12.0], [15.0], [1.0], [9.0], [13.0], [3.0], [2.0], [], [12.0], [], [], [], [], [], [], [], [], [], [], [], [], [], [], [], [], [], [], []]
[20-05-13 22:18:00:844 CEST] [[6.0], [4.0], [1.0], [5.0], [1.0], [7.0], [2.0], [2.0], [12.0], [15.0], [1.0], [9.0], [13.0], [3.0], [2.0], [], [12.0], [], [], [], [], [], [], [], [], [], [], [], [], [], [], [], [], [], [], []]
[20-05-13 22:18:00:846 CEST] [[6.0], [4.0], [1.0], [5.0], [1.0], [7.0], [2.0], [2.0], [12.0], [15.0], [1.0], [9.0], [13.0], [3.0], [2.0], [], [12.0], [], [], [], [], [], [], [], [], [], [], [], [], [], [], [], [], [], [], []]
[20-05-13 22:18:00:847 CEST] [[6.0], [4.0], [1.0], [5.0], [1.0], [7.0], [2.0], [2.0], [12.0], [15.0], [1.0], [9.0], [13.0], [3.0], [2.0], [], [12.0], [], [], [], [], [], [], [], [], [], [], [], [], [], [], [], [], [], [], []]
[20-05-13 22:18:00:849 CEST] [[6.0], [4.0], [1.0], [5.0], [1.0], [7.0], [2.0], [2.0], [12.0], [15.0], [1.0], [9.0], [13.0], [3.0], [2.0], [], [12.0], [], [], [], [], [], [], [], [], [], [], [], [], [], [], [], [], [], [], []]
[20-05-13 22:18:00:851 CEST] [[6.0], [4.0], [1.0], [5.0], [1.0], [7.0], [2.0], [2.0], [12.0], [15.0], [1.0], [9.0], [13.0], [3.0], [2.0], [], [12.0], [], [], [], [], [], [], [], [], [], [], [], [], [], [], [], [], [], [], []]
[20-05-13 22:18:00:853 CEST] [[6.0], [4.0], [1.0], [5.0], [1.0], [7.0], [2.0], [2.0], [12.0], [15.0], [1.0], [9.0], [13.0], [3.0], [2.0], [], [12.0], [], [], [], [], [], [], [], [], [], [], [], [], [], [], [], [], [], [], []]
[20-05-13 22:18:00:854 CEST] [[6.0], [4.0], [1.0], [5.0], [1.0], [7.0], [2.0], [2.0], [12.0], [15.0], [1.0], [9.0], [13.0], [3.0], [2.0], [], [12.0], [], [], [], [], [], [], [], [], [], [], [], [], [], [], [], [], [], [], []]
[20-05-13 22:18:00:856 CEST] [[6.0], [4.0], [1.0], [5.0], [1.0], [7.0], [2.0], [2.0], [12.0], [15.0], [1.0], [9.0], [13.0], [3.0], [2.0], [], [12.0], [], [], [], [], [], [], [], [], [], [], [], [], [], [], [], [], [], [], []]
[20-05-13 22:18:00:858 CEST] [[6.0], [4.0], [1.0], [5.0], [1.0], [7.0], [2.0], [2.0], [12.0], [15.0], [1.0], [9.0], [13.0], [3.0], [2.0], [], [12.0], [], [], [], [], [], [], [], [], [], [], [], [], [], [], [], [], [], [], []]

Thanks for the hint with the log.

#14