Office Script to Check if Table Header Exists Won't Detect Missing Header

Question

I'm working on a macro in Office Scripts for Excel. Among other things I need it to

• check if a column header exists in the given table
• add a column if it doesn't exist, and set the desired header
• write values will to that column

Validating the non existence of the header doesn't seem to work, so the code will proceed even when the header doesn't exist, then crash when it attempts to write to non-existant variable.

Please note that I had previously posted on excelForum, and failed to get a response. Not sure if I should link the post?

I'm expecting that let columnScore = table.getColumn("Score"); would return a column object and that if (!columnScore) { ... }
would return TRUE and execute the if statement if no such column object could be found.

The Office Scripts Documentation says that the getColumn() method should return 'undefined' if a column is not found. So I also tried number of tests for an undefined variable, but all return false.

So what is the best way to validate the existence of a table header?

PS I found this discussion on optional chaining vs non-null assertions but I'm not sure how I could apply that. Optional chaining seems to avoid executing if there is an undefined. I want TO excute if there is an undefined.

Here is the relevant code snippet:

function QAcolorTable(table: ExcelScript.Table){
  console.log("table: " + table.getName());
  
  //find or create column to for score formula
  let columnScore = table.getColumn("Score");
  console.log('null '+ (columnScore==null));
  console.log('type of ' + (typeof columnScore));
  console.log("undefined " + (columnScore==undefined));
  console.log("not " + !columnScore);
  console.log("void2 " + (columnScore == void 0));
  console.log("void3 " + (columnScore === void 0));
  if (!columnScore) {
    console.log("adding 'Score'");
    table.addColumn(-1, null, "Score");
    columnScore = table.getColumn("Score");
  }
}

and here is my full code. Please note that the script -searches for sheets whose name contains "QA Checklist", -in those sheets searches for a Table whose name contains "QAChecklist"

function main(workbook: ExcelScript.Workbook) {
  let sheets = workbook.getWorksheets();
  sheets.forEach(sheet => {
    let sheetName = sheet.getName();
    if(sheetName.search("QA Checklist") > -1){
      QA_color_Sheet(sheet);
    }
  });
}

function QA_color_Sheet(sheet : ExcelScript.Worksheet){
  console.log("sheet: " + sheet.getName() );
  let tables = sheet.getTables();
  tables.forEach(table => {
    let tableName = table.getName();
    if(tableName.search("QAChecklist") > -1) {
      QAcolorTable(table);
    }
  });
}

function QAcolorTable(table: ExcelScript.Table){
  console.log("table: " + table.getName());
  
  const scoreFormula = "=IF( [@[Consultant Checked]]=\"N/A\",NA(),SWITCH([@[Get Colour]],38, 1, 36, 0.5, 35, 0, \"#C6EFCE\", 0, \"#FFEB9C\", 0.5, \"#FFC7CE\", 1, 1))";
  let headers = table.getHeaderRowRange();

  //get the column object to read colours from Exit if not found.
  let columnRead = table.getColumn("Initial Reviewer Comments");
  if (columnRead === void 0) { columnRead = table.getColumn("Reviewer Comments"); }
  if (columnRead === void 0) {
    console.log("column [Initial Reviewer Comments]||[Initial Reviewer Comments] not found in " + table.getName());
    return;
  }
  let rngRead = columnRead.getRangeBetweenHeaderAndTotal();

  //find or create colum to write RGB color to. 
  let columnWrite = table.getColumn("Get Colour");
  if (columnWrite === void 0){
    console.log("Adding 'Get Colour'");
    table.addColumn(-1, null, "Get Colour");
    columnWrite = table.getColumn("Get Colour");
  }
  let rngWrite = columnWrite.getRangeBetweenHeaderAndTotal();

  //find or create column to for score formula
  let columnScore = table.getColumn("Score");
  console.log('null '+ (columnScore==null));
  console.log('type of ' + (typeof columnScore));
  console.log("undefined " + (columnScore==undefined));
  console.log("not " + !columnScore);
  console.log("void2 " + (columnScore == void 0)); 
  console.log("void3 " + (columnScore === void 0));
  if (columnScore === void 0) {
    console.log("adding 'Score'");
    table.addColumn(-1, null, "Score");
    columnScore = table.getColumn("Score");
  }
  console.log(scoreFormula)
  columnScore.getRangeBetweenHeaderAndTotal().getCell(0,0).setFormula(scoreFormula);

  let rows = table.getRowCount();
  //write colour values to the write cells
  for (let rw = 0; rw < rows; rw++) {
    let cellColorRead = rngRead.getCell(rw, 0);
    let cellWrite = rngWrite.getCell(rw, 0);
    cellWrite.setValue(cellColorRead.getFormat().getFill().getColor());
  }
}

Answer 1

Your code logic of validating column in a table appears correct. Simplify the test case for debugging.

function main(workbook: ExcelScript.Workbook) {
  let selectSheet = workbook.getActiveWorksheet();
  let selectTab = selectSheet.getTables()[0];
  let scoreCol =  QAcolorTable(selectTab);
  scoreCol.getRangeBetweenHeaderAndTotal().getRow(0).setValue('Done');
}

function QAcolorTable(table: ExcelScript.Table) {
  console.log("table: " + table.getName());
  let columnScore = table.getColumn("Score");
  if (!columnScore) {
    console.log("adding 'Score'");
    table.addColumn(-1, null, "Score");
    columnScore = table.getColumn("Score");
  }
  return columnScore
}

Console Output

table: tabDemo
adding 'Score'

---

You can catch undefined in Office Script as below.

function main(workbook: ExcelScript.Workbook) {
  let selectSheet = workbook.getActiveWorksheet();
  let selectTab = selectSheet.getTables()[0];
  let scoreCol = selectTab.getColumn("Score");
  console.log(typeof(scoreCol) === 'undefined');
}

---

Many use if (!columnScore) for validation but this also catches null/0, not just undefined. Refer to the table below for how if condition evaluates.

function main(workbook: ExcelScript.Workbook) {
  let selectSheet = workbook.getActiveWorksheet();
  let selectTab = selectSheet.getTables()[0];
  let colS = selectTab.getColumn("NonExistCol"); // colS is undefined
  console.log("== undefined ==");
  console.log("null " + (colS === null));
  console.log("typeof " + ((typeof colS) === "undefined"));
  console.log("=undefined " + (colS === undefined));
  console.log("not " + !colS);
  console.log("void2 " + (colS == void 0));
  console.log("void3 " + (colS === void 0));
  console.log("== null ==");
  colS = null;
  console.log("null " + (colS === null));
  console.log("typeof " + ((typeof colS) === "undefined"));
  console.log("=undefined " + (colS === undefined));
  console.log("not " + !colS);
  console.log("void2 " + (colS == void 0));
  console.log("void3 " + (colS === void 0));
  console.log("== 0 ==");
  let colT = 0;
  console.log("null " + (colT === null));
  console.log("typeof " + ((typeof colT) === "undefined"));
  console.log("=undefined " + (colT === undefined));
  console.log("not " + !colT);
  console.log("void2 " + (colT == void 0));
  console.log("void3 " + (colT === void 0));
}

Output

if condition	undefined	null	0
var === null	false	true	false
typeof(var) === 'undefined'	true	false	false
var === undefined	true	false	false
!var	true	true	true
var == void 0	true	true	false
var === void 0	true	false	false