CODEWITHSUNDEEP
google-apps-scriptgoogle-sheetsgoogle-sheets-formulacustom-function
SamP
SamP

Reputation: 437

How to count the number of cells which contain specific text using Google Sheet Scripts?

How to count the number of cells which contain specific text using Google sheet scripts?

Currently I'am using the following script to count the number of cells which contain specific text, for example cells which contain: 'Complete' or 'Not Complete'.

function COUNT_TEXT_ACROSS_SHEETS(sheetNames, range, text) {
  sheetNames = sheetNames.split(',');
  var count = 0;
  sheetNames.forEach(function(sheetName) {
    var sheet = SpreadsheetApp.getActive().getSheetByName(sheetName);
    var values = sheet.getRange(range).getValues();
    values.forEach(function(row) {
      row.forEach(function(cell) {
        if (cell.indexOf(text) !== -1 ) count++;
      });
    });
  });
  return count;
};

Currently the script counts both cells which contain the text 'Complete' and 'Not Complete' is there a way to modify the script to only count cells which contain the full word 'Complete' instead of also counting cells which contain 'Not Complete' because the cell also contains the keyword: 'Complete'?

Example usage of the current script: 

=COUNT_TEXT_ACROSS_SHEETS("Sheet1", "C1:C1000", "NOT COMPLETE")

Upvotes: 0

Views: 2093

Answers (3)

Raph the raph
Raph the raph

Reputation: 121

=COUNTIF(Sheet1!C1:C1000;"=NOT COMPLETE")

Upvotes: 0

Cooper
Cooper

Reputation: 64140

How about this:

function COUNT_TEXT_ACROSS_SHEETS(sheetNames, range, text) {//text=['Complete','Not Complete']
  var nA=sheetNames.split(',');
  var tA=text.split(',');
  var count = 0;
  var cObj={};
  cObj[tA[0]]=0;
  cObj[tA[1]]=0;
  nA.forEach(function(name) {
    var sh=SpreadsheetApp.getActive().getSheetByName(name);
    var vA = sheet.getRange(range).getValues();
    vA.forEach(function(r) {
      r.forEach(function(c) {
        var idx0=c.indexOf(tA[0]);
        var idx1=c.indexOf(tA[1]);
        if(idx0!=-1 && idx1==-1)cObj[tA[0]]+=1;
        if(idx0!=-1 && idx1!=-1)cObj[tA[1]]+=1  
      });
    });
  });
  return Utilities.formatString('Count-%s:%s Count-%s:%s',tA[0],cObj[tA[0]],tA[1],cObj[tA[1]]);
};

Upvotes: 1

TheMaster
TheMaster

Reputation: 50890

Use TextFinder class.

Sample script:

/**
 * @customfunction
 * @return count of given text in spreadsheet
 * @param {string} text text to search
 * @param {boolean} mec whether to match entire cell
 * @param {boolean} mc whether to match case
 */
function COUNT_SS(text = 'NOT COMPLETE', mec = true, mc = true) {
  return SpreadsheetApp.getActive()
    .createTextFinder(text)
    .matchEntireCell(mec)
    .matchCase(mc)
    .findAll()
    .length;
}

Usage:

=COUNT_SS()

Upvotes: 3

Related Questions