[Excel] Undoing conditionalFormatRule.delete() causes the format rule to be corrupted.

[DavidVanDeursen]

When undoing a conditionalFormatRule.delete() action using ctrl+z, loading any property of the format rule or calling any of its methods causes the following error Uncaught (in promise) RichApi.Error: The requested resource doesn’t exist.

Reopening the workbook fixes the issue.

Your Environment

• Platform: PC
• Host: Excel
• Office version number: 2511
• Operating System: Windows 11

Expected behavior

When undoing a conditionalFormatRule.delete() , the format rule should be usable.

Current behavior

Undoing conditionalFormatRule.delete() makes the conditional format rule that was added back unsuable as it causes a crash.

Steps to reproduce

1. Load the code snippet below.
2. Press the setup button to add a conditional format rule to the sheet.
3. Press the clear button to delete it.
4. Press CTRL+Z to undo the delete action.
5. Press the check button to try and load a property of the format rule and observe that it throws an error.

Link to live example(s)

Script lab code:

name: Undo delete conditional format error.
description: A snippet to show how to reproduce a conditional format error
host: EXCEL
api_set: {}
script:
  content: >
    document.getElementById("setup").addEventListener("click", () =>
    tryCatch(setup));

    document.getElementById("clear").addEventListener("click", () =>
    tryCatch(clear));

    document.getElementById("check").addEventListener("click", () =>
    tryCatch(check));


    async function setup() {
      Excel.run(async (context) => {
        const worksheet = context.workbook.worksheets.getFirst();
        const range = worksheet.getRange("A1");

        const conditionalFormat = range.conditionalFormats.add(Excel.ConditionalFormatType.custom);
        conditionalFormat.custom.format.fill.color = "#FEF07B";
        conditionalFormat.custom.rule.formula = "=TRUE";
        await context.sync();
      });
    }


    async function clear() {
      Excel.run(async (context) => {
        const worksheet = context.workbook.worksheets.getFirst();
        const range = worksheet.getRange();
        range.conditionalFormats.load("items");
        await context.sync();

        for (let conditionalFormat of range.conditionalFormats.items) {
          conditionalFormat.delete();
          await context.sync();
        }
      });
    }


    async function check() {
      Excel.run(async (context) => {
        const worksheet = context.workbook.worksheets.getFirst();
        const range = worksheet.getRange();
        range.conditionalFormats.load("items");
        await context.sync();

        for (let conditionalFormat of range.conditionalFormats.items) {
          conditionalFormat.load("custom/rule");
          console.log(conditionalFormat);
          await context.sync().catch((error) => console.log(error));
        }
      });
    }


    /** Default helper for invoking an action and handling errors. */

    async function tryCatch(callback) {
      try {
        await callback();
      } catch (error) {
        // Note: In a production add-in, you'd want to notify the user through your add-in's UI.
        console.error(error);
      }
    }
  language: typescript
template:
  content: "<button id=\"setup\">Setup</button>\r\n<button id=\"clear\">Clear</button>\r\n<button id=\"check\">Check</button>"
  language: html
style:
  content: |-
    body {
        font-family: 'Segoe UI', Tahoma, Geneva, Verdana, sans-serif;
        font-size: 14px;
        line-height: 1.5;
        padding: 10px;
    }

    section {
        margin-bottom: 20px;
    }

    h3 {
        margin-top: 0;
        margin-bottom: 10px;
        font-size: 16px;
    }

    p {
        margin: 0 0 10px 0;
    }

    button {
        background-color: #f0f0f0;
        color: #333333;
        border: 1px solid #8a8a8a;
        padding: 8px 16px;
        font-size: 14px;
        cursor: pointer;
        border-radius: 2px;
        margin-left: 20px;
        margin-bottom: 5px;
        min-width: 80px;
        display: block;
    }

    button:hover {
        background-color: #e0e0e0;
    }

    button:active {
        background-color: #d0d0d0;
    }

    input {
        padding: 8px;
        margin: 5px 0;
        border: 1px solid #ccc;
        border-radius: 2px;
        font-size: 14px;
    }

    .header {
        text-align: center;
        background-color: #f3f2f1;
        padding: 10px;
    }
  language: css
libraries: >-
  https://appsforoffice.microsoft.com/lib/1/hosted/office.js

  https://raw.githubusercontent.com/DefinitelyTyped/DefinitelyTyped/master/types/office-js/index.d.ts

Context

We are making an addin to automatically add/remove conditional formats to highlight interesting information in a worksheet. If a user presses CTRL+Z whilst we have just removed conditional formats this error occurs. The only way to fix this is for a user to reopen their spreadsheet.

[github-actions]

This issue was automatically labeled as a regression based on dual-LLM analysis of the issue content and comments.

Reason: The issue described involves functionality that was expected to work correctly based on previous behavior. The expected behavior is that undoing a delete action should restore the conditional format rule to a usable state. However, the current behavior results in an error when attempting to access the restored rule, indicating that the undo operation does not correctly restore the rule's state. This suggests a regression because the undo functionality, which is a standard feature, is not working as intended after a recent change or update. The fact that reopening the workbook fixes the issue further supports the idea that the problem is related to the current session's state management, which may have been altered by recent changes.

[lindalu-MSFT]

@DavidVanDeursen Hi David, this is your actual human triage person! Thanks for letting us know about this. A lot of folks are out for the holidays but will flag this as Needs Attention and it will get to the right people. Happy solstice, Linda

[pravkuma]

Hi @DavidVanDeursen , thanks for reporting this issue. I’m able to reproduce it, and we’ve logged it internally under ticket #725830246. Our engineering team will review it and follow up with updates