I currently have a spreadsheet that is running the appendRows function. I need to continue using appendRows as the data source is deleted and updated with fresh data monthly. The goal is for this spreadsheet to provide us with a month-to-month overview of specific stats.

I want to use the countIf function to populate cells with the number of times a staff members name appears in the report pulled but the countIf function I have written isn't working (appendRows is).

I've created an example spreadsheet for reference, confidential details have been removed: Test Sheet

appendRows is triggered by the custom menu I've created. I'm wondering if a lack of trigger for the countIf function is why it's not working or if the script is the issue (see below or spreadsheet). I'm very new to scripting/coding so I assume it's my lack of knowledge, apologies if it's a blindingly obvious solution :)

countIf script:

function countif() {
var ss = null;
try {
ss = SpreadsheetApp.openById("181L-19VZf6qyI_Fa7u6SchLReQ7AxGPFQKZAdlCNtgw");
} catch (ex) {
ss = SpreadsheetApp.getActiveSpreadsheet();
}
var sheet = ss.getSheetByName("Source");
var lastLine = sheet.getLastRow();
var data = sheet.getRange("B27:B").getValues();
var count = 0;
for (var i = 0 ; i < lastLine ; i  ){
var count = 1;
for ( var j = 0 ; j < i ; i   ) {
var data = sheet.getRange("B27:B" lastLine).getValues();
{
count  ;
}
}
sheet.getRange('John Smith', 'Jane Doe'   (i 1).toString()).setValue(count);
}
}

Any help and/or advice would be greatly appreciated!

CodePudding user response：

Loop the data array, and store the result in an object for later use:

const ssid = '1B0YeClVG_sLfpMGBtcjcj7q9-10BQqH5XqRy6c_3YxQ';
const source = 'Source';
const rangeOfDate = 'A26:Q';

function countif() {
  const sss = SpreadsheetApp.openById(ssid);
  const sourceSheet = sss.getSheetByName(source);
  const data = sourceSheet.getRange(rangeOfDate).getValues();
  const result = {};
  for (const [index,row] of data.entries()) {
    if (!index || !row[1]) continue;
    !result[row[1]] ? result[row[1]] = 1 : result[row[1]]  
  }
  console.log(result);
}

/**Output:
{ 'John Smith': 5,
  'Jane Doe': 4,
  'Steve Jobs': 1,
  'Steve Black': 8,
  'Jim Bob': 16,
  'Alice Cooper': 13,
  'Alice Alice': 4,
  'Brad Pitt': 8 }
*/