Office Scripts error - the property is not available

Question

I've created a script with the new Office Scripts in Excel on the web. The script simply sets the fill color for a range in the current worksheet, gets the used range, and then tries to write the range's address to the console.

async function main(context: Excel.RequestContext) {
  let workbook = context.workbook;
  let worksheets = workbook.worksheets;
  let selectedSheet = worksheets.getActiveWorksheet();
  selectedSheet.getRange("B3:E6").format.fill.color = "E2EFDA";

  // write used range to console
  let usedRange = selectedSheet.getUsedRange();
  usedRange.load("address");
  context.sync();
  console.log("used range = " + usedRange.address);
}

Running this script generates the following error message:

The property 'address' 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.

Seems like I'm already doing as the error message guidance suggests -- i.e., calling the load method to load the address property and then calling context.sync() after the load. What am I missing here?

Answer 1

You'll need to add await before context.sync(); (so it becomes await context.sync();).

context.sync() is an asynchronous action, meaning the script will continue running before the sync finishes. Since the next line of your script requires information from the workbook, that sync needs to be completed before continuing. Changing the line to await context.sync(); ensures the synchronization between the workbook and your script is done and successful before proceeding.

There's more about this in the article Scripting fundamentals for Office Scripts in Excel on the web.

---

It's worth noting that this can happen even in code that has await context.sync() in it. For instance, if you drop this code into an Excel add-in and trigger it, it'll cause the error described in the question:

async function capCells(range: Excel.Range) {
    range.load("values");
    await range.context.sync();
    range.values = range.values.map((row) =>
        row.map((cell) => String(cell).toLocaleUpperCase())
    );
    await range.context.sync();
}

// ...

Excel.run(async (conn) => {
    const sheet = conn.workbook.worksheets.getActiveWorksheet();
    const range = sheet.getRangeByIndexes(1, 1, 1, 1);
    capCells(range);
//  ^------------------------- The bug is here
    const item = sheet.names.getItemOrNullObject("name-that-does-not-exist");
    item.context.sync();
    if (item.isNullObject) {
//      ^^^^^^^^^^^^^^^^^----- but shows up here, even though there's a `sync` call
        console.log(`It's null`);
    } else {
        console.log(`It's not null`);
    }
});

The bug in that code is that we're missing out an await before capCells(range);, so we're just starting that asynchronous function without waiting for it to finish; it runs and starts a sync but then returns its promise immediately, and our code continues and starts another overlapping sync (in the Excel.run callback). Excel doesn't like you doing that; it gives you the same error it would if you just hadn't done a sync at all.

So if you get this error but you see you do have a sync call in the code it seems to be related to, look at other code you recently changed — you'll probably find a missing await.