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?
Note: This answer is for the preview version of Office Scripts that still required context.sync()
. Before the general availability release, the API was changed so you didn't have to do context.sync()
calls. So this answer (and question) only apply to the preview version of Office Scripts.
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.