OfficeScript中为表格列使用COUNTIF公式的实现问题

问题描述 #

我没法在特定单元格里设置COUNTIF公式。先是试了给固定单元格范围（比如某一列）用COUNTIF，之后想用表格列名，但COUNTIF里条件的引号和setFormula调用的引号冲突，导致公式设置失败。

出错代码 #

function main(workbook: ExcelScript.Workbook) {

  // 添加表格，排除前5行
  const sheet = workbook.getActiveWorksheet();
  const range = sheet.getUsedRange();
  const rowcount= range.getRowCount()-5;
  const rangetable = range.getRowsBelow(-rowcount);
  const table = sheet.addTable(rangetable, true);
  table.setPredefinedTableStyle(null)

// 统计未完成的网络安全任务数量
  const cyberTaskColumn = table.getColumnByName("All Cybersecurity Tasks Complete");
// cyberTaskColumn.getFilter().applyValuesFilter(["No"]);
  sheet.getRange("N5").setFormula("=COUNTIF(N7:N24,\"No\")");

}

报错信息 #

Errors:(Line 15 is the error line):
[15, 53] Cannot find name 'No'.
[15, 53] Expected 1 arguments, but got 3.
[15, 53] ',' expected.
[15, 55] ',' expected.

解决方案 #

问题根源 #

在TypeScript中，用双引号包裹字符串时，\"的转义写法会被解析器误判，导致字符串提前闭合，把No当成了代码变量而非公式的一部分，引发语法错误。

方法1：单引号包裹公式 #

把外层的双引号换成单引号，公式内的双引号无需转义，直接书写：

sheet.getRange("N5").setFormula('=COUNTIF(N7:N24,"No")');

方法2：模板字符串（反引号）包裹 #

用反引号`包裹整个公式，内部双引号不用转义，写法更直观：

sheet.getRange("N5").setFormula(`=COUNTIF(N7:N24,"No")`);

进阶：使用表格列名（结构化引用） #

如果要调用表格列名，直接用表格的结构化引用，同样用模板字符串或单引号包裹：
先获取表格名称，再代入公式：

const tableName = table.getName();
sheet.getRange("N5").setFormula(`=COUNTIF(${tableName}[All Cybersecurity Tasks Complete],"No")`);

内容的提问来源于stack exchange，提问作者Su Myint