Forum Discussion

AAsc49's avatar
AAsc49
Copper Contributor
Jan 14, 2025

Office Scripts - How to use "RC" in a Script

Hello,

 

I tried to use ROW(RC) in a conditionnal formula in an Office Script but the result is ROW('RC') and the formula doesn't work.

If I use only ROW() my conditonnal formula doesn't work.

How can I create a conditionnal formula that refers to an RC reference in Office Script ?

Thank you,

PS: I use french XL so it's LIGNE(LC) instead of ROW(RC).

 

Arnaud (from France)

excel
Office Scripts

4 Replies

  • JKPieterse's avatar
    JKPieterse
    Silver Contributor
    Jan 17, 2025

    The formula property of a conditionlFormat object expects a formula in A1 mode, so:

    Replace this:

    const formula = '=EST.PAIR(LIGNE(LC))';

    with this:

    const formula = '=EST.PAIR(LIGNE(' + range.getCell(0,0).getAddress() + '))';

     

    • AAsc49's avatar
      AAsc49
      Copper Contributor
      Jan 20, 2025

      thank you, it's better but I've to open the Conditionnal Panel and open the rule then click on [Apply] otherwise the Formula doesn't apply (but the formula is good !).

    • AAsc49's avatar
      AAsc49
      Copper Contributor
      Jan 16, 2025

      Hello,

      Yes, my script (French) is :

      function main(workbook: ExcelScript.Workbook) {

        let conditionalFormatting: ExcelScript.ConditionalFormat;

        let selectedSheet = workbook.getActiveWorksheet();

        let range = workbook.getSelectedRange();

        conditionalFormatting = range.addConditionalFormat(ExcelScript.ConditionalFormatType.custom);

        const formula = '=EST.PAIR(LIGNE(LC))';

        conditionalFormatting.getCustom().getRule().setFormula(formula);

        conditionalFormatting.getCustom().getFormat().getFill().setColor("#e2eefa");

        conditionalFormatting.setStopIfTrue(false);

        conditionalFormatting.setPriority(0);

      }

      Formula in english should be : const formula = '=ISEVEN(ROW(RC))';

       

      And the result is 

      =EST.PAIR(LIGNE('LC'))  instead of =EST.PAIR(LIGNE(LC)) (the script adds quotes !?)

       

      Thank you for your help,

       

      Arnaud

Resources