CODEWITHSUNDEEP
google-apps-scriptgoogle-sheets
Josh
Josh

Reputation: 339

Counting the number of times a string appears in a range

Quite new to programming and have gotten stuck after much searching. I'm trying to make a Google script that can search a particular column and count how many times values 'YES' or 'NO' appear in the column (after that it would act based on those numbers).

Tried various ways to do this but all unsuccessful, what I think is the closest example is below:

   function billChecker() {
var ss = SpreadsheetApp.getActiveSpreadsheet();
 var sheet = ss.getSheets()[0];

 var values = sheet.getSheetValues(3, 5, 20, 1);
 Logger.log(values);

  var unpaidEntries = values.indexOf("YES"); 

 var email = Session.getActiveUser().getEmail();

  var subject = "Test";

  var body = "The number of unpaid items is " + unpaidEntries + "." + "The values contained are: " + values;

 GmailApp.sendEmail(email, subject, body);
}

As you can tell, the purpose of the script is to say how many unpaid bills there are, I am emailing myself results, it's the only way I can think to check it at the moment! I thought the best way to do it was probably to use example.indexOf() method, however that always produces '-1'.

Upvotes: 1

Views: 4581

Answers (2)

ReyAnthonyRenacia
ReyAnthonyRenacia

Reputation: 17613

Use this code that I made. It does exactly what you're looking for:

Sample data:

Range Start: A5 (5,1)

enter image description here

function countRepitition() {

   var ss = SpreadsheetApp.openById("ENTER_SPREADSHEET_ID");
   var sheetname = ss.getSheetByName("ENTER_SHEET_NAME");

   var count = 0;
   var values = sheetname.getRange(5, 1, 9, 2).getValues(); //Start of Range (5,1) -> A5, End of Range (9,2) -> B9

   for (var row in values) {
     for (var col in values[row]) {

       if (values[row][col] == "YES") {
         count++; //increment count everytime we find the string 'YES' upon given range
       }

     }
   }

  Logger.log('count is '+ count); //print the valuoe of count in the Logs
}

This is the result in the console:

enter image description here

Upvotes: 2

user6655984
user6655984

Reputation:

The method getSheetValues returns a double array: an array in which each element is an array of elements in the corresponding row. For example, if the range is filled with 

1 2
3 4

you are going to get [[1,2], [3,4]]. If the range has just one column, as in your example, this structure still applies: 

1
3

is represented as [[1], [3]].

So, you are never going to find a string "YES" as an element of an array, since there would be ["YES"] instead. Additionally, indexOf is meant for finding the first occurrence of some value, not for counting them. Here is how you can count them:

var unpaid = values.reduce(function(a, b) {
  return a + (b[0] == 'YES' ? 1 : 0);
}, 0);

Here, a is the counter, initially set to 0. The reduce method traverses the values array, serving up each of its elements, such as ["YES"] or ["NO"], as the argument b. The reducing function adds 1 to the counter when b[0] is "YES".

Upvotes: 2

Related Questions