office js - What is the proper order of loading and syncing to conditionally delete an offset range of rows from a table?

Question

Trying to do maintenance on a table via an add-in. If the table has more than N rows, I want to delete all but N from the end.

I finally got it to work in Script Lab as demonstrated in this gist: https://gist.github.com/70d3d62d32d4fe8f34279ab240f7b110

but it doesn't feel like I actually understand why it is working.

1. I have to call sync, but doesn't matter if I call it before or after the delete call.
2. I thought it made sense to wrap the delete inside a context.sync().then(()=>obj.delete);, but that doesn't work.
3. I haven't seen samples that make a bare call to context.sync(), they usually call await context.sync(), but when I try to do that, IntelliSense says it isn't in an async function. If I add the async keyword to that nested closure, then the script throws a runtime error saying that doesn't work and I need to manage the binding manually...

Could someone please review this code and help clarify these questions?

Answer 1

If you haven't already done so, I'd suggest that you review Excel JavaScript API core concepts, which contains info about Excel.run(...), context.sync(), and load().

Additional comments/info:

• As described in the article, you must call load() before you can read properties of an object. You don't need to call load() if you're simply setting property values or accessing other methods off of a property. If you do need to call load() (because you're intending to read the properties of the object), you'll need to subsequently call sync() so that the load() instruction gets executed, before you can read the object's properties.

• As described in the article, you can queue up multiple instructions on the context before you call context.sync() to execute those instructions. In the deleteRows() function of your snippet, the context.sync() that you've prefaced with a comment isn't necessary because you can simply add the lastThreeRows.delete() instruction to the queue first, and then all of the instructions will be executed at once at the end of Excel.run. (It's best practice to explicitly call context.sync() at the end of an Excel.run, but technically you don't have to because any queued instructions will automatically get executed when Excel.run concludes.)

• If you're using TypeScript, you should always await the context.sync() (e.g., await context.sync();), and the function definition for the function that contains await context.sync() must begin with the keyword async (e.g., async function foo() {...}). Within the body of a function, you can only use the keyword await if the function is defined as async.

Here's an updated gist that you can import to and run in Script Lab: https://gist.github.com/kbrandl/74e64d74f396cbe6047b3247e3c066d7. I've updated code within the deleteRows() function to reflect the concepts that I've described above.

async function deleteRows() {
    try {
        await Excel.run(async (context) => {

            const expensesTable = context.workbook.tables.getItem("ExpensesTable")

            const bodyRows = expensesTable.getDataBodyRange().load("rowCount");

            await context.sync();

            if (bodyRows.rowCount == 7) {
                const lastThreeRows = bodyRows.getOffsetRange(4, 0).getResizedRange(-4, 0);
                lastThreeRows.delete(null);
                await context.sync();
            }
        });
    }
    catch (error) {
        OfficeHelpers.UI.notify(error);
        OfficeHelpers.Utilities.log(error);
    }
}