如何在Google Apps Script中用循环实现CountIf功能

需求说明 #

• 原Excel VBA宏用于统计状态，需转换为GAS（插件转换报错）
• 统计目标：在Ongoing Q3-Q4工作表中，利用第3行C到K列的表头值，对Plan DUCO Q3-Q4工作表F列（F2:F150）执行类似CountIf的统计
• 仅当Ongoing Q3-Q4中C列第一个空行对应的B列日期≤当前日期时，才执行统计并填充数据

原Excel VBA代码 #

Sub ContadorEstados()
    Dim TodayDate As Date
    Dim FirstEmptyDate As Date
    Dim counter As Double

    TodayDate = Date
    
    'Select the sheet where the table I need to fill with counts is
    Sheets("Ongoing Q3-Q4").Select
    
    'Select the last cell with content and offset 1 row to select the first empty cell in the C column
    ActiveSheet.Range("C3").End(xlDown).Offset(1, 0).Select

    'We pick the first date that has no values
    FirstEmptyDate = ActiveCell.Offset(0, -1).Value
    
    'Loop, if the first empty date is equal or less than today date we fill the data
    If FirstEmptyDate <= TodayDate Then
        'counter from 3 (the c column) to 11 (last column with the values i want to "countif" 
        For counter = 3 To 11
            '"Plan Duco Q3-Q4 is another sheet where i have the range with the values i want to countif
            ActiveCell.Value = Application.CountIf(Worksheets("Plan DUCO Q3-Q4").Range("$F$2:$F$150"), ActiveSheet.Cells(3, counter))
            ActiveCell.Offset(0, 1).Select
        Next
    End If
End Sub

自行编写的GAS代码（无法实现功能） #

function myFunction() {

  var ssValuesToCount = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Plan DUCO Q3-Q4').getRange(2,6,150,1).getValues();

  var ssTable= SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Ongoing Q3-Q4');

  var ValuesTable = ssTable.getRange(3,3,1,9).getValues();
  var lastrow = ssTable.getRange("B1:B").getValues().filter(String).length +1 ;
  var counter = 0 ;

  for (var i = 0; ValuesTable.length; i++){
    var Values = ValuesTable[0][i]
      for (var j =0; ssValuesToCount.length; j++){
        if (ssValuesToCount[j][0] == Values){
          counter +=1;} 
        }
      }
  }

修正后的GAS实现代码 #

核心逻辑说明 #

1. 摒弃select/activate操作（GAS不推荐且效率低），直接通过行号列号定位单元格
2. 提前统计Plan DUCO Q3-Q4工作表F列各值的出现次数，用对象映射替代嵌套循环，提升效率
3. 精准定位Ongoing Q3-Q4中C列的第一个空行，获取对应B列日期并做格式归一化
4. 日期符合条件时，批量填充统计结果到目标行

function contadorEstados() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  
  // 1. 获取F列数据并预处理统计各值出现次数
  const countSheet = ss.getSheetByName('Plan DUCO Q3-Q4');
  const fValues = countSheet.getRange(2, 6, 149, 1).getValues().flat(); // 转为一维数组
  const countMap = {};
  fValues.forEach(value => {
    countMap[value] = (countMap[value] || 0) + 1;
  });

  // 2. 定位目标工作表及关键位置
  const targetSheet = ss.getSheetByName('Ongoing Q3-Q4');
  // 计算C列从C3开始的最后一个非空行，下移一行得到第一个空行
  const lastFilledRowInC = targetSheet.getRange('C3:C').getValues().filter(String).length + 2;
  const targetRow = lastFilledRowInC + 1;
  // 获取对应B列的日期
  const firstEmptyDate = targetSheet.getRange(targetRow, 2).getValue();
  const today = new Date();
  
  // 归一化日期，去除时间部分避免判断误差
  const normalizeDate = date => new Date(date.getFullYear(), date.getMonth(), date.getDate());

  // 3. 日期符合条件时批量填充统计结果
  if (normalizeDate(firstEmptyDate) <= normalizeDate(today)) {
    // 获取第3行C到K列的表头值
    const headerValues = targetSheet.getRange(3, 3, 1, 9).getValues().flat();
    // 生成统计结果数组
    const result = headerValues.map(header => countMap[header] || 0);
    // 批量写入目标行
    targetSheet.getRange(targetRow, 3, 1, 9).setValues([result]);
  }
}

关键修正点 #

• 效率优化：用对象countMap一次性完成所有值的统计，避免嵌套循环的冗余遍历
• 日期处理：统一日期格式，消除时间部分对日期判断的干扰
• 最佳实践：避免使用选择操作，直接通过坐标定位单元格，符合GAS性能要求
• 批量写入：用setValues一次性写入结果，减少与服务器的交互次数，提升执行速度

内容的提问来源于stack exchange，提问作者Mario Diez Martínez