excel - Find table intersect with selection

Question

I'm looking for a Javascript equivalent of the Excel VBA Intersect method. I need to find the active cell table. Basically everything I do with tables is based on which table the user currently has selected. IIRC, there is currently there is no way to do this directly. So what I'm trying to do is basically this:

• Get the selected range (still not perfect, as I really only want the ActiveCell, not the Selection).
• Get the worksheet.
• Loop through all tables on the worksheet.
• Check each table and see if the selected range is in the table range.

I've monkey'd around a bit, and this is what I currently have which doesn't work...

Excel.run(function(ctx) {

    var Selection = ctx.workbook.getSelectedRange();
    var Tables = ctx.workbook.tables;
    var TableNames = ctx.workbook.tables.load("name");

    for (var i = 0; i < TableNames.items.length; i++)
    {
        var Table = ctx.workbook.tables.getItem(TableNames.items[i].name);
        Table.getRange().load('address');
        var Intersect = Selection.getBoundingRect(Table.getRange().address);
        if (Intersect != null) {return ctx.sync().then(function() {
            TableNames.items[i].name;
        })};
    }

    return ctx.sync();
}).catch(function(error) {
    console.log(error);
    if (error instanceof OfficeExtension.Error) {
        console.log("Debug info: " + JSON.stringify(error.debugInfo));
    }
});

This would be a major step forward for the API if it was native. ;)

Thanks, Zack

Answer 1

There are several ways to check whether the current selection intersects with a table. This snippet demonstrates two of them.

Both of the examples below are written with TypeScript 2.1+'s async/await syntax. The second method is made significantly simpler through the use of "await", but both are possible with just regular promise-chaining as well.

1. The ExcelApi 1.4+ one is vastly more efficient, but it will only run on newer builds of Excel (on subscription-based, not on 2016 MSI/RTM). It does all of its intersection-checks simultaneously.
2. The ExcelApi 1.1 version is less efficient if you have hundreds of tables, or if you're running on Excel Online. It requires more roundtrips to the server, as it checks every table intersection one-by-one, and relies on a thrown error to inform it that there is no intersection found.

ExcelApi 1.4+ approach:

$('#check-intersection-preview').click(function() {
    // Note: this function uses a "Preview" API ("range.getIntersectionOrNull"),
    // which is only available on the Beta CDN right now, and is subject to change.
    // Do not rely on this for production. Instead, use the alternate
    // (albeit less neat) version.

    Excel.run(async function(context) {
        var selection = context.workbook.getSelectedRange();
        var tables = context.workbook.tables.load("name");
        await context.sync();

        var intersections: { [email: string]: Excel.Range } = { };
        tables.items.forEach((table) => {
            intersections[table.name] = table.getRange().
                getIntersectionOrNullObject(selection).load("address");
        });
        await context.sync();

        var found = false;
        for (var tableName in intersections) {
            var rangeOrNull = intersections[tableName];
            if (!rangeOrNull.isNullObject) {
                found = true;
                console.log(`Intersection found with table "${tableName}". ` +
                    `Intersection range: "${rangeOrNull.address}".`);
            }
        }
        if (!found) {
            console.log("Selection does not intersect any table");
        }
    }).catch(errorHandler);
});

ExcelApi 1.1 approach:

$('#check-intersection-prod').click(function() {
    Excel.run(async function(context) {
        var selection = context.workbook.getSelectedRange();
        var tables = context.workbook.tables.load("name");
        await context.sync();

        var found = false;        
        for (var i = 0; i < tables.items.length; i++) {
            try {
                var table = tables.items[i];
                var intersectionRange = table.getRange()
                    .getIntersection(selection).load("address");
                await context.sync();

                // If reached past the sync, it means that "getIntersection"
                // did not throw an error, and so the intersection must be valid.
                found = true;
                console.log(`Intersection found with table "${table.name}". ` +
                    `Intersection range: "${intersectionRange.address}".`);

            } catch (e) {
                var isExpectedError = e instanceof OfficeExtension.Error &&
                    (<OfficeExtension.Error>e).code === Excel.ErrorCodes.itemNotFound;

                if (!isExpectedError) {
                    throw e;
                } 
            }
        }

        if (!found) {
            console.log("Selection does not intersect any table");
        }
    }).catch(errorHandler);
});

Common errorHandler helper:

function errorHandler(error) {
    console.log(error);
    if (error instanceof OfficeExtension.Error) {
        console.log("Debug info: " + JSON.stringify(error.debugInfo));
    }
}

TRY IT LIVE: You can try the Excel 1.4+ approach live in literally five clicks in the new Script Lab (https://aka.ms/getscriptlab). Simply install the Script Lab add-in (free), then choose "Import" in the navigation menu, and use the following GIST URL: https://gist.github.com/Zlatkovsky/3ebdf5587cdc56d23b289fb6a5645030. See more info about importing snippets to Script Lab.