Why does my function return type turns into a promise once I read cell.value or put in console.log

Viewed 141

I started coding a validation of my Excel Sheet. I implemented quite a bit so I try to keep the code short.

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 usefull 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 left out the Builders as they just create the objects.

The above code works and validates the file like I wish.

The generated code for the Method UniqueSortOrderChecker.isValid looks like:

function (value, rowIndex) {
                            ExcelScript.engine.traceLine(undefined);
                            return true;
                        }

So the interesting part is, once I change the UniqueSortOrderChecker into:

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

The generated code looks now totally different and returns a promise:

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];
                                    }
                                });
                            });
                        }  

Same happens when I try to read cell value like:

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

turns it into a promise return type like:

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];
                                    }
                                });
                            });
                        }

Can anyone help me out here? As once it is turned into a promise the return value is always true and my validation does not work.

Hope the question is not to long, but I wanted to give context.

1 Answers

Office Script relies on some complex logic underneath to give inherently asynchronous code the appearance of synchronous execution.

As of 2021-07-01 underneath the covers every Office Script method including console.log returns a promise. As you have discovered!

The complex logic applies awaits to these pieces of code to make them match the IntelliSense and appear synchronous.

The majority of common cases as of 2021-07-01 are covered by this logic, however there are some gaps in the complex transformation logic that may miss some scenarios.

Use of Office Script methods and console.log are not yet supported in class methods.

If you must use Office Script methods or console.log in classes today, you can mark these class methods as async and await them.

If this feature is important for your scenario I recommend sending feedback in the Office Script Editor click the ... and then click Send Feedback.

Related