Get Column rowCount with Office-JS even if Column is empty

Question

Note: As per MSFT recommendation, I'd like to avoid an extra context.sync(), but I think that might be the only way? Or error catching inside of a function?

I need to get the current rowCount for a function, but if the column is empty I get an "ItemNotFound" error.

How can I check the rowCount of a column that may or may not have data?

Note: I'm testing on a brand new WS and this is what I have so far

var ws = context.workbook.worksheets.getActiveWorksheet();
var col_one_rng = ws.getRange("A:A").getUsedRange(true)
col_one_rng.load(['rowCount'])
console.log('BeforeSync')
await context.sync(); //Error
console.log('AfterSync')
var col_one_pre_row_count = col_one_rng.rowCount
console.log('col_one_pre_row_count:' + col_one_pre_row_count)

Full Error Stack:

ItemNotFound: The requested resource doesn't exist.
   {
      [functions]: ,
      __proto__: { },
      code: "ItemNotFound",
      data: undefined,
      debugInfo: { },
      description: "The requested resource doesn't exist.",
      httpStatusCode: 404,
      innerError: null,
      message: "The requested resource doesn't exist.",
      name: "RichApi.Error",
      stack: "ItemNotFound: The requested resource doesn't exist.
   at Anonymous function (https://appsforoffice.microsoft.com/lib/beta/hosted/excel-win32-16.01.js:26:306238)
   at Anonymous function (http://localhost:3000/yo/dist/polyfill.js:1:76119)
   at e (http://localhost:3000/yo/dist/polyfill.js:1:31843)",
      Symbol()_7.yx7nmjnyzes: undefined,
      Symbol(nodejs.util.inspect.custom)_j.yx7nmjnyzho: undefined,
      traceMessages: [ ]
   }

Update:

If I remove the getUsedRange(true) then I get rowCount = 1048576. But don't try to work with Entire Column Ranges as Excel Engineers have decided you shouldn't be able to do that, because they know best and you shouldn't do that, so you have to "workaround" via hacks --> Can't Use Entire Column Range with Excel JavaScript API

score 0 · Answer 1 · answered Apr 03 '23 at 19:35

Well, so far, this is my workaround:

async function Get_Col_Row_Count(context, ws, Col_Letter) {
    try {
        var col_rng = ws.getRange(Col_Letter + ":" + Col_Letter).getUsedRange(true)
        col_rng.load('rowCount')
        await context.sync();
        var rowCount = col_rng.rowCount
        return rowCount
    } catch (error) {
        return 0
    }
}

Get Column rowCount with Office-JS even if Column is empty

1 Answers1