How to count an amount of comments or specific words written by Accounts in Google sheet

Question

I am trying to count comments or specific words written by users in Google Sheet table. for example 5 accounts have permission on sheet and i want to count how many comments or yes/no is written in table by each user. is there any solution for this task?

Answer 1

This will get the yes/no count case insensitive and it also has two arrays (yays and nays) which contain the row and column of each yes/no response. Also gets the Users email address.

function getYesAndNo() 
{
  var ss=SpreadsheetApp.getActiveSpreadsheet();
  var sht=ss.getActiveSheet();
  var rng=sht.getDataRange();
  var rngA=rng.getValues();
  var yays=[];
  var nays=[];
  var yay=/yes/i;
  var nay=/no/i;
  var user = Session.getActiveUser().getEmail();
  for(var i=0;i<rngA.length;i++)
  {
    for(var j=0;j<rngA[i].length;j++)
    {
      if(String(rngA[i][j]).match(yay))
      {
        yays.push([Number(i+1),Number(j+1)]); //rows and columns not indexes
      }
      if(String(rngA[i][j]).match(nay))
      {
        nays.push([Number(i+1),Number(j+1)]); //rows and columns not indexes
      }
    } 
  }
  Logger.log(yays);
  Logger.log(nays);
  SpreadsheetApp.getUi().alert('User Email = ' + user + ' Yes Count = ' + yays.length + ' No Count = ' + nays.length);
}