Table's worksheet/id doesn't load on certain Excel Desktop builds

Question

We have built an Excel Task pane add in that primarily works with tables. In few versions of Excel we get an error when we try to load tables from workbook.

Sample code to load tables from workbook:

$scope.findTables = function () {
    let tables;
    Excel.run(function (ctx) {
        let xlTables = ctx.workbook.tables;
        xlTables.load(["count", "items"]);
        return ctx.sync(xlTables).then(function (xlTables) {
            tables = new Array(xlTables.count);

            let loader = OfficeExtension.Promise.resolve({});
            xlTables.items.forEach(function (xlTable, i) {

                loader = loader.then(function () {
                    xlTable.load(["id", "name", "worksheet/id"]);
                    let tblRange = xlTable.getRange();
                    prepare(tblRange);
                    return ctx.sync(tblRange);

                }).then(function (tblRange) {

                    let keyCellRow = tblRange.rowIndex + rowOffset,
                        keyCellColumn = tblRange.columnIndex + colOffset;

                    let keyRange = ctx.workbook.worksheets.getItem(xlTable.worksheet.id).getCell(keyCellRow, keyCellColumn); //error thrown at this line
                    keyRange.load(["formulas", "values"]);

                    return ctx.sync().then(function () {
                        return keyRange.formulas[0][0] ? keyRange.formulas[0][0] : keyRange.values[0][0];
                    });

                }).then(function (keyValue) {

                    if (!keyValue) return;

                    let tableRef = {
                        id: xlTable.id,
                        sheet: xlTable.worksheet.id,
                        name: xlTable.name,
                        key: keyValue
                    };
                    tables[i] = tableRef;
                });
            });
            return loader;
        });
    }).then(function () {
        console.log("No of Tables", tables.length);
        app.showNotification("No of Tables detected: " + tables.length);
    }).catch(function (error) {
        app.showNotification("Error in detecting tables");
        console.log("Error: " + error);
        logError(JSON.stringify(error));
        if (error instanceof OfficeExtension.Error) {
            console.log("Debug info: " + JSON.stringify(error.debugInfo));
        }
    });
}

The error we get is property id of worksheet not loaded. Note that here in sample code xlTable.load(["id", "name", "worksheet/id"]) and context.sync both are called only after that worksheet.id is accessed. But it still throws the error.

Here is the detailed error:

{
    "name": "OfficeExtension.Error",
    "code": "PropertyNotLoaded",
    "message": "The property 'id' is not available. Before reading the property's value, call the load method on the containing object and call \"context.sync()\" on the associated request context.",
    "traceMessages": [],
    "innerError": null,
    "debugInfo": {
        "code": "PropertyNotLoaded",
        "message": "The property 'id' is not available. Before reading the property's value, call the load method on the containing object and call \"context.sync()\" on the associated request context.",
        "errorLocation": "Worksheet.id"
    },
    "stack": "PropertyNotLoaded: The property 'id' is not available. Before reading the property's value, call the load method on the containing object and call \"context.sync()\" on the associated request context.\n   at t.throwIfNotLoaded (https://appsforoffice.microsoft.com/lib/1/hosted/excel-win32-16.01.js:21:261181)\n   at id.get (https://appsforoffice.microsoft.com/lib/1/hosted/excel-win32-16.01.js:21:286763)\n   at Anonymous function (https://smart-client.ngrok.io/app/Home.js:44:29)\n   at yi (https://appsforoffice.microsoft.com/lib/1/hosted/excel-win32-16.01.js:21:246431)\n   at st (https://appsforoffice.microsoft.com/lib/1/hosted/excel-win32-16.01.js:21:246518)\n   at d (https://appsforoffice.microsoft.com/lib/1/hosted/excel-win32-16.01.js:21:246338)\n   at c (https://appsforoffice.microsoft.com/lib/1/hosted/excel-win32-16.01.js:21:244924)"
}

Note that this happens only on specific build versions of Excel. We have observed this issue on 16.0.4498.1000 and 16.0.4549.1000 build versions.

I've also provided a sample add-in demonstrating this issue.

Can you help us as to why this is happening and possible way to fix this?

Answer 1

The answer to this question is that Table.worksheet was added in the 1.2 requirement set and this one is not supported in MSI builds.

MSI builds only support Excel 1.1 requirement set.
16.0.4266.1003 16.0.4498.1000 16.0.4549.1000 16.0.4619.1000 16.0.4639.1000