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, });
}

Answer 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);
  }
}