Apply Conditional Formatting - Duplicates - Excel Javascript

Question

I'm trying to add Conditional Highlighting to a column in Excel via Office-JS. I used the automate feature for the first time, but I can't seem to get it to work in Excel.

This is what automate spat out:

function main(workbook: ExcelScript.Workbook) {
    let conditionalFormatting: ExcelScript.ConditionalFormat;
    let selectedSheet = workbook.getActiveWorksheet();
    // Change preset criteria from range A:A on selectedSheet
    conditionalFormatting = selectedSheet.getRange("A:A").addConditionalFormat(ExcelScript.ConditionalFormatType.presetCriteria);
    conditionalFormatting.getPreset().getFormat().getFont().setColor("#9C0006");
    conditionalFormatting.getPreset().getFormat().getFill().setColor("#FFC7CE");
    conditionalFormatting.getPreset().setRule({criterion: ExcelScript.ConditionalFormatPresetCriterion.duplicateValues,});
}

This is my attempt at transcribing:

function Do_ApplyHighlightDupsConditionalFormatting(rng) {
    ConditionalFormat.rng.addConditionalFormat(ExcelScript.ConditionalFormatType.presetCriteria);
    ConditionalFormat.getPreset().getFormat().getFont().setColor("#9C0006");
    ConditionalFormat.getPreset().getFormat().getFill().setColor("#FFC7CE");
    ConditionalFormat.getPreset().setRule({ criterion: ExcelScript.ConditionalFormatPresetCriterion.duplicateValues, });
}

This is Office Scripts code. Are you trying to use this code in Excel Online? Or are you trying to use this code with Office.js e.g. with ScriptLab? If you're trying to use the Office Scripts code with Office.js it may not work. Office Scripts and Office.js have separate APIs. So code from one to the other may not work. — Brian Gonzalez, Feb 20 '22 at 16:38
I am trying to use w/ Visual Studio and Excel on Windows. I am used to transposing VBA from Macro Recorder, thought this might be a good place to start (Automate) but so far not working. Is this feature not available in Office.JS? @BrianGonzalez — FreeSoftwareServers, Feb 20 '22 at 16:43
Ah that's what I suspected. The feature is available in Office.js. But your example is from the Office Scripts API. They are similar but different. You can see a detailed breakdown here: https://learn.microsoft.com/en-us/office/dev/scripts/resources/add-ins-differences — Brian Gonzalez, Feb 20 '22 at 18:34
@BrianGonzalez Read that already, doesn't help me resolve OP tho. How can this be accomplished in Office.JS? Is there docs relating to `ConditaionalFormatting` I can reference? — FreeSoftwareServers, Feb 20 '22 at 18:46
Have you seen this doc? https://learn.microsoft.com/en-us/office/dev/add-ins/excel/excel-add-ins-conditional-formatting — Brian Gonzalez, Feb 20 '22 at 19:23
@BrianGonzalez I have, but perhaps I will attempt again, the word `duplicate` doesn't show on that page at all, but I'm guessing I can transpose one of the examples using `ConditionalFormatPresetCriterion.duplicateValues`. Which `FormatType` would that be? — FreeSoftwareServers, Feb 20 '22 at 19:35
I think you want to use the ConditionalPresetCriteriaRule. You can read more here: https://learn.microsoft.com/en-us/javascript/api/excel/excel.conditionalpresetcriteriarule?view=excel-js-preview. I will post an example of how to use this in a comment. — Brian Gonzalez, Feb 20 '22 at 19:57
@BrianGonzalez Thanks :) I'm working on Fabric UI Taskpane stuff today but will test at work next Monday or asap! — FreeSoftwareServers, Feb 20 '22 at 20:02

Brian Gonzalez · Answer 1 · 2023-01-11T18:15:27.803

1

You can use ConditionalFormatPresetCriterion to do this. Please see the example below:


    $("#run").click(() => tryCatch(run));
    
    async function run() {
      await Excel.run(async (context) => {
        const wb: Excel.Workbook = context.workbook;
        const ws: Excel.Worksheet = wb.worksheets.getActiveWorksheet();
        const rang: Excel.Range = ws.getRange("A1:A5");
        const cf: Excel.ConditionalFormat = rang.conditionalFormats.add(Excel.ConditionalFormatType.presetCriteria);
        cf.preset.format.font.color = "red";
      cf.preset.rule = {criterion:Excel.ConditionalFormatPresetCriterion.duplicateValues};
    
        await context.sync();
    
      });
    }
    
    /** 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);
      }
    }

edited Jan 11 '23 at 18:15

answered Feb 20 '22 at 20:04

Brian Gonzalez

1,178
1
3
15

Thats `typescript` correct? No problem if it is. I'll have to learn to transpose at some point! Thanks a bunch! My first time converting `Automate` to `JS` and it'll take some learning. – FreeSoftwareServers Feb 20 '22 at 22:50
Yup, it's typescript. And no problem. – Brian Gonzalez Feb 21 '22 at 00:35
Figured it out, looking at the `Automate` output.... Its pretty damn far off, but it had the correct keywords `ConditionalFormatType.presetCriteria`. I'll have to learn moving forward to NOT use 99% of the `Automate` code.... Bummer. – FreeSoftwareServers Mar 02 '22 at 23:37
I didn't accept due to having to transcribe to JS, was a bit harder then expected and OP wasn't in TS, so I'm not sure why answer would be. BUt, thank you again!! Def still +1 – FreeSoftwareServers Mar 02 '22 at 23:38

score 0 · Accepted Answer · answered Mar 02 '22 at 23:31

After transposing the MS Docs and @BrianGonzalez Typescript answer, I got it working!

Func:

function Set_Dup_Conditional_Formatting(rng) {
    //https://stackoverflow.com/questions/71189904/apply-conditional-formatting-duplicates-excel-javascript
    var conditionalFormat = rng.conditionalFormats.add(
        Excel.ConditionalFormatType.presetCriteria
    );
    conditionalFormat.preset.format.font.color = "red";
    conditionalFormat.preset.rule = {
        criterion: Excel.ConditionalFormatPresetCriterion.duplicateValues
    };
    return true;
}

Apply Conditional Formatting - Duplicates - Excel Javascript

2 Answers2