Logging to console whenever a cell value exceeds character limit

Question

I have this pseudocode in mind to log an error whenever the length of a cell value within the ItemNumber column exceeds 10 characters. I would also like to display the offending row number as well.

Haven't got a chance to test this out fully, but is it possible?

Any help would be much appreciated!

let itemDesc = newTable.getColumnByName("ItemNumber")
for (let i = itemDesc.length - 1; i >= 0; i--) {
  let event = itemDesc[i];
  let rowNumber = ???
  if (event.length > 10) {
    console.log(`Character count exceeds 10 characters at Rows: ${rowNumber}`);
  }
}

score 2 · Accepted Answer · answered Jul 18 '21 at 18:12

This code should work. It assumes that the data is in an Excel table / ListObject:

function main(workbook: ExcelScript.Workbook) {
let ws = workbook.getActiveWorksheet();
let newTable = ws.getTable("Table1");
let itemDesc = newTable.getColumnByName("ItemNumber");
let rang = itemDesc.getRange();
let vals = rang.getValues();
let rowNumbers = []
vals.forEach((item, rowNumber) => {
    if (rowNumber > 0 && item[0].toString().length > 10) {
        rowNumbers.push(rowNumber)
    }
})
rowNumbers.forEach(row => console.log(`Character count exceeds 10 characters at Rows: ${row}`)) //prints each offending row on a separate line
console.log(`Character count exceeds 10 characters at Rows: ${rowNumbers.toString()}`); //prints all offending rows on one line

}

I would suggest an improvement to use the [TableColumn.getRangeBetweenHeaderAndTotal](https://learn.microsoft.com/en-us/javascript/api/office-scripts/excelscript/excelscript.tablecolumn?view=office-scripts#getRangeBetweenHeaderAndTotal__) instead of `getRange`. This would avoid the need for the check for `rowNumber > 0` — Jay Rathi - Microsoft, Jul 20 '21 at 17:01

Logging to console whenever a cell value exceeds character limit

1 Answers1