Currently, I am developing a macro in Office Scripts for Excel that has several requirements:
- Verify the existence of a column header in a specified table
- If the column does not exist, add it and set the desired header
- Write values to that newly added column
Unfortunately, the validation process for checking the non-existence of the column header seems to be ineffective. The code continues execution even when the header is missing, leading to a crash when trying to write to a non-existent variable.
I previously sought help on excelForum but did not receive any responses. Should I provide a link to my previous post?
I expected that:
let columnScore = table.getColumn("Score");if (!columnScore) { ... }should return TRUE and execute the if statement if the column object is not found.
According to the Office Scripts Documentation, the getColumn() method should return 'undefined' if a column is not found. Despite testing various conditions for an undefined variable, all have returned false.
What would be the most effective method to validate the presence of a table header?
I came across a discussion on optional chaining vs non-null assertions, but I am unsure how to apply it. Optional chaining appears to avoid execution if there is an undefined value, whereas I want it to execute if there is an undefined.
Below is a snippet of the relevant code:
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 the complete code: Please note that the script: -searches for sheets with names containing "QA Checklist", -in those sheets, searches for a Table named "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());
}
}