Recently, I embarked on the journey of coding a validation process for my Excel Sheet. To keep the code concise, I implemented it in a straightforward manner.

Here is a snippet of my source code:

function main(workbook: ExcelScript.Workbook) {

  console.log("starting...");
  let cs = createVariantCategorySheet(workbook.getWorksheet("variant categories"));
  cs.validateSheet();
  for (let m of cs.logMessages) {
    console.log(m);
  }
  console.log("finished...");
}

function createVariantCategorySheet(worksheet: ExcelScript.Worksheet): ImportSheet {

  let sheet = new ImportSheetBuilder()
    .name("variant categories")
    .index(1)
    .worksheet(worksheet)
    .addColumnInfo(
      new ColumnInfoBuilder()
        .index(4)
        .name("Sort Order")
        // .addChecker(new NotEmptyChecker())
        .addChecker(new UniqueSortOrderChecker(worksheet, worksheet.getRange("B:B")))
      .build()
    )
    .build();
  return sheet;
}


class ImportSheet {
  public name: string;
  public index: number;
  public worksheet?: ExcelScript.Worksheet
  columns: ColumnInfo[];
  public logMessages: string[];

  constructor() {
    this.index = -1;
    this.name = "";
    this.columns = [];
    this.logMessages = [];
  }

  // ... Some useful private methods ...

  validateSheet() {
    let rowCount = this.getRowCount();

    for (let rowIndex = 1; rowIndex < rowCount; rowIndex++) {      
      if (this.isRowEmpty(rowIndex)) {
        this.logMessages.push("no more lines... ")
        return;
      }
      this.validatingRow(rowIndex);
    }
  }

  private validatingRow(rowIndex: number) {
    this.logMessages.push("iterating over line [" + rowIndex + "]")
    for (let columnItem of this.columns) {
      this.validatingColumn(columnItem, this.rangeToValidate(rowIndex, columnItem.index));
    }
  }

  private validatingColumn(columnItem: ColumnInfo, rangeToValidate: ExcelScript.Range){
    for (let validator of columnItem.validator) {
      let stringToValidate = rangeToValidate.getValue().toString();
      this.logMessages.push("###### " + validator.isValid)
      if (!validator.isValid(stringToValidate, rangeToValidate.getRowIndex())) {
        this.logMessages.push("## ## ERROR: Failed Validation " + validator.getName() +
          " in '" + rangeToValidate.getAddress() + "' for column '" +
          columnItem.name + "'.");
      } else {
        this.logMessages.push("## ## INFO: Success Validation " + validator.getName() +
          " in '" + rangeToValidate.getAddress() + "' for column '" +
          columnItem.name + "'.");
      }
    }
  }
}

class ColumnInfo {
  name: string;
  index: number;
  validator: Checker[];
}

interface Checker {
  isValid(value: string, rowIndex: number): boolean
  getName(): string
}

class NotEmptyChecker implements Checker {

  isValid(value: string): boolean {
    if (value)
      return true;
    else
      return false;
  }

  getName(): string {
    return NotEmptyChecker.name;
  }
}

class UniqueSortOrderChecker implements Checker {
  sortOrderMap: Map<string, Set<string>>;

  range: ExcelScript.Range
  worksheet: ExcelScript.Worksheet

  constructor(worksheet: ExcelScript.Worksheet, range: ExcelScript.Range) {
    this.range = range;
    this.worksheet = worksheet;
    this.sortOrderMap = new Map<string, Set<string>>();
  }

  isValid(value: string, rowIndex: number): boolean {
    return true;
  }

  getName(): string {
    return UniqueSortOrderChecker.name;
  }
}

I decided to omit the Builders as they mainly focus on object creation.

The provided code effectively achieves the file validation as intended.

However, when attempting to tweak the UniqueSortOrderChecker method like so:

  isValid(value: string, rowIndex: number): boolean {
    console.log("test");
    return true;
  }         

The resulting transformation impacts the generated code significantly, leading to a promise-based return:

function (value, rowIndex) {
                            return __awaiter(this, void 0, void 0, function () {
                                return __generator(this, function (_a) {
                                    switch (_a.label) {
                                        case 0:
                                            ExcelScript.engine.traceLine(264);
                                            return [4 /*yield*/, console.log("test")];
                                        case 1:
                                            (_a.sent());
                                            ExcelScript.engine.traceLine(undefined);
                                            return [2 /*return*/, true];
                                    }
                                });
                            });
                        }  

A similar outcome emerges when trying to extract cell value with:

  isValid(value: string, rowIndex: number): boolean {
    let cellValue = this.range.getRow(rowIndex).getValue().toString();
    return true;
  }

This change also results in a promisify return type:

function (value, rowIndex) {
                            return __awaiter(this, void 0, void 0, function () {
                                var cellValue;
                                return __generator(this, function (_a) {
                                    switch (_a.label) {
                                        case 0:
                                            ExcelScript.engine.traceLine(264);
                                            return [4 /*yield*/, this.range.getRow(rowIndex).getValue()];
                                        case 1:
                                            cellValue = (_a.sent()).toString();
                                            ExcelScript.engine.traceLine(undefined);
                                            return [2 /*return*/, true];
                                    }
                                });
                            });
                        }

If anyone has encountered a similar issue or can provide guidance, your insight would be greatly appreciated. The shift to a promise-based system seems to be affecting the validation results adversely.

Thank you for taking the time to read through the details provided above.