Using COUNTIF formula for a column in a table using OfficeScript

Question

I can not seem to set a COUNTIF formula in a specific cell. First, I am trying to just use the COUNTIF formula for a fixed range of cells (lets say a column), then I want to call a column name from a table. I can't seem to be able to set a formula because the quotations for the criteria in the COUNTIF formula itself is interfering with the setFormula function call. If someone knows how to implement a COUNTIF formula a different way please let me know.

function main(workbook: ExcelScript.Workbook) {

  //Add a table that excludes the first 5 rows
  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)

//trying to count how many cyber tasks have yes
  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.

Answer 1

• If there is a double qotation marker in the string, you could use either ` or ' to surround the string.

• Or use escape sign \

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

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

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

Answer 2

Alternatively, escape the double quotes ...

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

... that's pretty standard notation in these types of programming languages.