How to count frequency of an Identifying number in Google sheets

Question

we are requesting assistance with creating a script to calculate how frequent transactions are processed for an identifying number per month.

On the (December) sheet, in column (A) we have all the transactions requested that month listed by identifying numbers (ex...888). We would like the script to identify the current Month sheet (ex..December), calculate how frequent (888) comes up in column A, and generate that number in the (Data) sheet under the current month column (ex..December), and in addition aligned with the identify number (888) and name(John Doe) by row number.

We have created and tried a formula (=COUNTIF('December 2020'!A2:A600, 888), but it is time consuming, for we have over 300+ identifying numbers. Please reference to the attached screenshots for better understanding. Thank you in advance , and we anticipate you response.

enter image description here enter image description here

Answer 1

function countOccurencesInColumn1() {
  const months=['January','February','March','April','May','June','July','August','September','October','November','December'];
  const m=new Date().getMonth();
  const y=new Date().getFullYear();
  const name=Utilities.formatString('%s %s',months[m],y);
  const ss=SpreadsheetApp.getActive();
  const sh=ss.getSheetByName(name);
  const sr=2;//start row
  const rg=sh.getRange(sr,1,sh.getLastRow()-sr+1,1);
  const v=rg.getValues().flat();
  let obj={pA:[]};
  v.forEach(function(p,i){
    if(!obj.hasOwnProperty(p)) {
      obj[p]=1;
      obj.pA.push(p);
    }else{
      obj[p]+=1;
    }
  });
  let html='<style>tr,td{border:1px solid black;}</style><table><tr><th>String</th><th>Count</th></tr>';
  obj.pA.forEach(function(p){
    html+=Utilities.formatString('<tr><td>%s</td><td>%s</td></tr>',p,obj[p]);
  });
  html+='</table>';
  SpreadsheetApp.getUi().showModelessDialog(HtmlService.createHtmlOutput(html), "Occurrences");
  //Loading the sheet
  const dsh=ss.getSheetByName('DataSheetName');//don't know name
  const hA=dsh.getRange(1,1,1,dsh.getLastColumn()).getValues().flat();//data sheet header array
  const col={};
  hA.forEach((h,i)=>{col[h]=i+1;});//converst column names to column numbers
  const pnames=dsh.getRange(2,1,sh.getLastRow()-1,1).getValues().flat();
  const values=dsh.getRange(2,col[name],dsh.getLastRow()-1,1).getValues();
  pnames.forEach(function(n,i){
    if(obj.hasOwnProperty(n)) {
      values[i][0]=obj[n];
    }else{
      values[i][0]='';
    }
  });
  dsh.getRange(2,col[name],values.length,1).setValues(values);
}