CODEWITHSUNDEEP
google-apps-script
Steve
Steve

Reputation: 67

Google Script with Spreadsheet - Function to Count Column Values

I'm building a Google spreadsheet where I'll enter a bunch of data into that spreadsheet, then on another sheet, I want to pull statistics from it. I'm having a hard time counting the values correctly.

What I'm attempting to accomplish - Build a function that I can add to any cell to get the statistics of a certain column by counting repeating values based on the target/argument in the function.

Example:

Sheet 1 has the data I need to calculate in column E (holds 1 or 2 letters to indicate a status. Sheet 2 is where I will pull all statistical data. I add a function like so: =getData("A")

The function itself would already point to sheet1 and column E. What the function is saying here is, "I want you to pull all of the data in column E and tell me how many cells are marked as A" and then give me the count.

I have about 5 different values I would independantly search for here. We'll say A, B, C, D and E. So if I go to search for C, I would pull the stats into another cell on my statistics sheet using: =getData("C")

What I've gotten so far (that doesn't work) - I'm pretty sketchy at coding this stuff, so I'm still learning a lot. Here's what I was attempting so far:

function getData(target) {
  var ss = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("SHEET1");
  var lrow = ss.getLastRow();
  var r = ss.getRange(2, 5, lrow, 1);
  var data = r.getValues();
  var count;

  for (var i = 0; i < lrow ; i++) {
    var count = 1;
    for (var j = 0; j < i; i++) {
      if (data[j][0].value(target) == data[i][0].value(target)) {
        count++;
      }
    }
  return (count);
  }
}

I also tried: if (data.value() == target) { count++; }

But that didn't seem to help either. What am I doing wrong? Either I'm not counting correctly and/or not pulling the data to count it correctly. I've searched quite a few posts and can't seem to pull together what I need.

I appreciate any help anyone could offer. Thanks.

Resolved Code ( Thanks @ScampMichael ) - This slight variation actually allows me to search for multiple values using the format =getData("Example1","Example2") and counting the total:

function getData(target1, target2) {
  var ss = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("SHEET1");
  var lrow = ss.getLastRow();
  var r = ss.getRange(2, 5, lrow-1, 1); // lrow-1 to not count header
  var data = r.getValues();
  var count = 0; // 0 must be declared here or you'll get error: Overflow on the spreadsheet

  for (var i=0; i<data.length; i++) {
    if (data[i][0] == target1) { // can repeat this part depending how many values you want to search for
      count++;
    }
    if (data[i][0] == target2) {
      count++;
    }
  }
  return (count);
}

Upvotes: 2

Views: 21798

Answers (1)

ScampMichael
ScampMichael

Reputation: 3728

Edited: I missed var r = ss.getRange(2, 5, lrow, 1); should be 

 var r = ss.getRange(2, 5, lrow - 1, 1);  // because starting at row 2

Because your source data is only one column wide you do not need a loop within a loop but I have left it so in case you desire to have more than one column in your source.

  for (var i = 0; i < data.length ; i++) {
    for (var j = 0; j < data[i].length; j++) {
      if (data[i][j] == target) {
        count++;
      }
    }
  }

for a single column you could:

  for (var i = 0; i < data.length ; i++) {
      if (data[i][0] == target) {
        count++;
    }
  }

There is also the built in function: =COUNTIF(SHEET1!E2:E,"A")

Upvotes: 2

Related Questions