Refreshing conditional formatting

[redcirkle]

Article URL

https://docs.microsoft.com/en-us/javascript/api/excel/excel.application?view=excel-js-preview

Issue

We are building an Excel add-in that applies conditional formatting to some columns. Depending on input data, we shade the cells green. However, we notice that, if we copy-and-paste data into the sheet, the conditional formatting doesn't calculate right away. Here's an example artifact:

The white portion of the column is visible as the user copies the data, and when we scroll over we see the rest of the column in green. It seems like the off-screen columns are formatting correctly, but not the on-screen part.

Note: My issue is not with this artifacting. It's just to highlight how the conditional formatting doesn't update automatically, and our need for a programmatic solution.

We'd like to update the conditional formatting programmatically. In general, here are the solutions we've found:

• Switch context to another application (not sure why this one works, and definitely not an ideal solution, but interesting to note)
• run app.suspendScreenUpdatingUntilNextSync();

app.suspendScreenUpdatingUntilNextSync(); does update the in-sheet conditional formatting, but it causes a "flicker" in our taskpane (because it suspends the screen update for a moment, hiding what was on the pane). Is there a different method we should call to update all in-sheet conditional formatting?

[AlexJerabek]

Hi @redcirkle,

Can you please share some code to help reproduce the issue (perhaps a Script Lab snippet)? Off-hand, I would expect the copy-and-paste to overwrite the conditional formatting, since, by default, copy-and-paste copies over the formatting. You'd have to explicitly copy the values only.

Thanks!

[redcirkle]

Hi @AlexJerabek,

Apologies in advance, I can't provide a Script Lab snippet at the moment. We're using this function to create our custom conditional formatting though (though we're seeing the artifacts across all conditional formatting).

For the copy-and-paste, that's good to call out. We are copy-pasting for value only, selecting the following option from the paste menu:

We still see the behavior though, that copy-pasting values (and only values) doesn't update the conditional formatting immediately. If we fill data in cell by cell, it does, but not when pasting data in bulk.

We see the same behavior when deleting data as well. Choosing "Yes" for this column causes these other cells to turn red. This is our baseline, expected behavior.

When I click on that "Yes" cell though, and hit Delete, the conditional formatting doesn't reload correctly all the way (turning all the cells white again).

Part of this probably comes down to the complexity of our formula, but what we note here is that the conditional formatting didn't update completely. To update completely, we still need to:

• edit another cell that also has conditional formatting rules, or
• switch context to another application.

My suspicion is that conditional formatting doesn't recalculate when users add/delete from cells without clicking into them. I was able to confirm this by copy-pasting a value into another cell that has conditional formatting (which looked like this):

and then clicking into that same cell that had conditional formatting to select that value in-cell (which looked like this):

When I paste without clicking into the cell, no re-calculation occurs, and the artifacts remain. When I click into the cell itself, the re-calculation occurs, updating all conditional formatting.

We want to run that re-calculation programmatically somehow, so users can see the correct conditional formatting without clicking into any cells. We want to attach a re-calculation call to a taskpane button so users can click it and "refresh" the conditional formatting, removing any lingering artifacts.

Do you know if there's a method in the API to accomplish this, or would app.suspendScreenUpdatingUntilNextSync(); be our best bet for the time being?

[AlexJerabek]

@redcirkle, Thanks for the detailed walkthrough. Does Application.calculate trigger the conditional formatting update for you? If that doesn't work, I'll transfer this issue to the product repo, as it may be an issue in Office-JS or Excel.

[redcirkle]

@AlexJerabek thanks for the quick response! We tried that method based on your recommendation, but it doesn't seem like any of the calculate options include the conditional formatting. We also tried the suspendApiCalculationUntilNextSync method, to no avail.

Please transfer the issue if you can, that would be great! We just want to make sure we're updating this the most effective way possible. Thanks!

[AlexJerabek]

Hi @lumine2008 and @MandytMSFT, Could either of you please take a look at this conditional formatting issue? It seems like the conditional formatting is not being reliably re-applied when new data is copy-and-pasted. Thanks!

[lumine2008]

@redcirkle could you kindly share us the scriptlab gist/snippet? thanks

[lumine2008]

i am closing this issue, due to not repro in our side, please reopen, if you still observe this issue, thanks

[stenly311]

I ran into a very similar issue a couple of days back and I can confirm that still happening.

The only detail I found to be crucial for being able to replicate this issue is to bulk-paste data into the cell which then makes the table exceed the current number of rows.

https://user-images.githubusercontent.com/8539921/162118304-334eb255-0be6-4a97-a863-7d536a9c2943.mp4

Any idea why this is happening? Is there any way how to "listen" to what the Excel app currently doing when it comes to conditional formatting? Is there any way how to force the Worksheet to re-calculate with conditional formatting (I tried all options I found in the Office.js documentation in MSDN and none of them worked for me. Btw. clicking on "Calculate Worksheet" button in the "Formulas" tab in the Excel ribbon did the trick )?

[AlexJerabek]

Hi @stenly311,

Thanks you the details. I'll reopen this issue. @guliums, could you please take a look?

[stenly311]

No worries. Thanks guys for taking a second look at this.

However, this may actually be an issue caused by Excel itself as demonstrated in the video. Moreover, I am able to run into the same faulty behaviour by populating new rows into the worksheet table from the code in Web Add-in app (Task pane).

Any help on codebases of how to force the worksheet to re-calculate conditional formatting is appreciated.

[AlexJerabek]

If you're able to recreate the issue in Excel alone, I'd recommend using Excel's "Send Feedback" mechanism. That'll capture some product data and route it to the right people.

[stenly311]

I will do. Thanks. However, I still do think that there must be an issue in Office.js lib because of the inconsistency of how the button in Excel ribbon works compering to the function called by using Office.js (I am assuming that the same logic should be invoked). Clicking on "Calculate Sheet" button in the ribbon fixes the conditional formatting issue.

let sheet = context.workbook.worksheets.getActiveWorksheet(); sheet.calculate()

vs

[JinghuiMS]

@stenly311 Could you provide your Excel workbook and steps to reproduce this issue by using sheet.calculate() API to help us repro this issue? Better to attach a repo video which used sheet.calculate() API. Thanks!

[stenly311]

Hi @JinghuiMS thanks for taking a look at this issue. I will appreciate any working idea of how to get over it :)

Video how to replicate: https://youtu.be/dD036p72Psc Task pane UI: Calculations solution from default github repo has been used in Script Lab The workbook is attached. UI Spreadsheet 1 016-MS version 2.xlsx

Let me know if having more questions. Martin.

[JinghuiMS]

@stenly311
Thanks for reporting. BUG 5990610 has opened for internal track.

[stenly311]

Hi @JinghuiMS how are you? Just following up on this issue, any luck with the issue investigation?

Thank you. M.

[guliums]

Hi @stenly311 sorry for late response. Thanks very much for the detailed repro video and file, that is very helpful for us to confirm a bug that conditional format is not refreshed in some case. For your scenario, could you try worksheet.calculate(true) API which should work like the calculate button in ribbon. Please let me know if it does not work. Thanks!

async function run() { await Excel.run(async (context) => { const sheet = context.workbook.worksheets.getActiveWorksheet(); sheet.calculate(true); await context.sync(); }); }

[ghost]

This issue has been automatically marked as stale because it is marked as needing author feedback but has not had any activity for 4 days. It will be closed if no further activity occurs within 3 days of this comment. Thank you for your interest in Office Add-ins!

[stenly311]

Hi @guliums , thanks for sharing this script with me.

However, the background colours of conditional formatting seem not to be working consistently after running it on the same workbook provided here initially.

Here is the vid of the test running by myself:

https://user-images.githubusercontent.com/8539921/168729562-c51ab735-afe6-4c96-85cd-c159e7be7284.mp4

Source code to use for ScriptLab: https://gist.github.com/stenly311/4c242adb32b9f1f62d4083469131ccf2

I was expecting to see conditional formatting working in the case of using this approach in the event of new rows in the worksheet table inserted/created.

A new row added in the table seems to me to be the trigger of this issue.

Otherwise, there is no way how to detect when to call a sheet.calculate(true) function programmatically.

Obviously doing it on every cell value commit would be a very bad design to follow.

Were you anyhow successful with the case investigation?

Thank you, Martin.

[ghost]

This issue has been automatically marked as stale because it is marked as needing author feedback but has not had any activity for 4 days. It will be closed if no further activity occurs within 3 days of this comment. Thank you for your interest in Office Add-ins!

[stenly311]

Hi @guliums any luck with the investigation? thanks

[ghost]

This issue has been automatically marked as stale because it is marked as needing author feedback but has not had any activity for 4 days. It will be closed if no further activity occurs within 3 days of this comment. Thank you for your interest in Office Add-ins!

[stenly311]

Hi @guliums any luck with the investigation? thanks

[guliums]

Hi @stenly311 , sorry for late response. Excel team are actively working on the bug you reported that conditional format does not refresh automatically in this case, internally tracked by VSO 6043693. I will follow up and let you know when there are update. From API side, unfortunately I currently don't have a better solution other than call worksheet.calculate(true) every time on worksheet data change event... I agree it is not a good design but you could use it as temporary workaround before the bug fixed. Thanks for your support!

[ghost]

This issue has been automatically marked as stale because it is marked as needing author feedback but has not had any activity for 4 days. It will be closed if no further activity occurs within 3 days of this comment. Thank you for your interest in Office Add-ins!

[stenly311]

Thanks @guliums . Looking forward to hearing from you on the ticket update.

[ghost]

This issue has been automatically marked as stale because it is marked as needing author feedback but has not had any activity for 4 days. It will be closed if no further activity occurs within 3 days of this comment. Thank you for your interest in Office Add-ins!

[ghost]

This issue has been closed due to inactivity. Please comment if you still need assistance and we'll re-open the issue.

[stenly311]

hi @guliums , I thought I am going to give you a short update on how using "worksheet.calculate(true)" works for us.

Firstly, I was surprised by the Excel application performance lagging after calling this function in the Task pane app compared to the Excel native calculate sheet feature found upon this UI: Calling "worksheet.calculate(true)" in web addin application (Task pane) takes between 5-15s, but (I am assuming this button invokes the same business logic behind the scenes) getting the active worksheet refreshed via clicking the button from the Excel ribbon only 1s.

Secondly, "worksheet.calculate(true)" does not fix the conditional formating issue every time compared to "calculate sheet" button in Excel.

Am I experiencing two separate on each other issues in the Office.js framework and the Excel application:

1. conditional formatting
2. "worksheet.calculate(true)" does not re-calculate the whole worksheet the same way as the functionality attached to the UI button "Calculate Sheet" from the Excel ribbon (Formulas tab)?

Either way, the proposed solution for this issue does not fix the problem but brought the inconsistency into the feature behavior (based on end-user experience, using Task pane web Add-in as a product).

Could you assist and help me to find a better solution for this issue, please?

Thank you, Martin.

[guliums]

Hi @stenly311 , sorry for the inconvenience. Do you have an example file that shows the worksheet.calculate(true) different with UI "Calculate Sheet"?

[sndurkin]

Hi @guliums, it looks like this issue was closed due to inactivity but it still seems to be a problem. Is there any update on when this will be fixed?