I use the following code to update cell A1 but it does not work:

function main(workbook: ExcelScript.Workbook) { 

    let worksheet = workbook.getWorksheet("Sheet1");
    var i: number = 0; 

    while (true) {
        worksheet.getRange("A1").setValue(i);
        i = i   1;
    }
}

Nothing happened when the code is run.

However, if I add an irrelevant line

console.log("abc")

at the end of the loop, as you can see below:

function main(workbook: ExcelScript.Workbook) { 

    let worksheet = workbook.getWorksheet("Sheet1");
    var i: number = 0; 

    while (true) {
        worksheet.getRange("A1").setValue(i);
        i = i   1;
        console.log("abc");
    }
}

everything will work smoothly.

So, what is actually happening here?

CodePudding user response：

To improve performance, Office Scripts only publishes the results of your script after you "synchronize" with it. console.log() is one of the ways of forcing that, but there are others.

From Office Scripts in Excel > Troubleshooting > Improve script performance (emphasis mine):

Remove unnecessary console.log statements

Console logging is a vital tool for debugging your scripts. However, it does force the script to synchronize with the workbook to ensure the logged information is up-to-date. Consider removing unnecessary logging statements (such as those used for testing) before sharing your script. This typically won't cause a noticeable performance issue, unless the console.log() statement is in a loop.

Likewise, from the article Avoid using the context.sync method in loops:

It's generally a good practice to put have a final context.sync just before the closing "}" character of the application run method (such as Excel.run, Word.run, etc.). This is because the run method makes a hidden call of context.sync as the last thing it does if, and only if, there are queued commands that have not yet been synchronized. The fact that this call is hidden can be confusing, so we generally recommend that you add the explicit context.sync. However, given that this article is about minimizing calls of context.sync, it is actually more confusing to add an entirely unnecessary final context.sync. So, in this article, we leave it out when there are no unsynchronized commands at the end of the run.

Your Office document is only visibly updated when the workbook is synchronized. This happens when context.sync is run, which happens implicitly at the end of your script; it also happens when you make a synchronized command like console.log. If you were to make your loop finite rather than infinite, then your script would automatically synchronize when your loop's limit is reached, and your workbook would appropriately update exactly once.

If you'd like to see partial progress, you can also force an update in the middle of your script with console.log or context.sync, but you should be aware that those calls are expensive and you might want to be careful to make minimal use of them as in the linked articles.

CodePudding user response：

The answer above is correct, though I wanted to offer some clarification on the context.sync. Office Scripts presents a synchronous API which does not require the user to explicitly perform the sync operations (and does not expose the context object). Consecutive writes are indeed all batched up and sent once. In your initial sample, you are just adding to the batch forever. Any console.log or read operation to the workbook will force the sync to happen and you'll see the write operation(s) you've batched up to that point.