Forum Discussion

jsears775's avatar
jsears775
Copper Contributor
Jul 25, 2024

Missing Instruction/Error in Automation Script

Hi all,

 

I'm sure this is going to be some basic thing, but I am getting an error when my automation script runs, specifically this portion below. What is missing or not formatted correctly? It is intended to apply a conditional rule across column A that uses function RIGHT to find any number ending in 2 or 7 and apply custom formatting. 

 

Specific error in this case is:

Line 58: ConditionalFormatRule setFormula: The argument is invalid or missing or has an incorrect format.

 

Please let me know what other info is needed if any, thank you in advance!

 

 

 

 

	// Create custom from range A:A on selectedSheet
	conditionalFormatting = selectedSheet.getRange("A:A").addConditionalFormat(ExcelScript.ConditionalFormatType.custom);
	conditionalFormatting.getCustom().getRule().setFormula("=OR(RIGHT(A1,1)=\"\"2\"\",RIGHT(A1,1)=\"\"7\"\")");
	conditionalFormatting.getCustom().getFormat().getFill().setColor("#92d050");
	conditionalFormatting.getCustom().getFormat().getFont().setBold(true);
	conditionalFormatting.getCustom().getFormat().getFont().setItalic(false);
	conditionalFormatting.setStopIfTrue(false);
	conditionalFormatting.setPriority(0);
}

 

 

 

  • jsears775 

    Try removing some of the double quotes:

    "=OR(RIGHT(A1,1)=\"\"2\"\",RIGHT(A1,1)=\"\"7\"\")"
    "=OR(RIGHT(A1,1)=\"2\",RIGHT(A1,1)=\"7\")"

     

    I'm also not entirely sure, but couldn't you wrap in single quotes to make it easier to read?

     

    conditionalFormatting.getCustom().getRule().setFormula('=OR(RIGHT(A1,1)="2",RIGHT(A1,1)="7")');

     

    What line is line 58 - it'd probably help if you posted the entire code.

  • BA_Max's avatar
    BA_Max
    Iron Contributor

    jsears775 

    Try removing some of the double quotes:

    "=OR(RIGHT(A1,1)=\"\"2\"\",RIGHT(A1,1)=\"\"7\"\")"
    "=OR(RIGHT(A1,1)=\"2\",RIGHT(A1,1)=\"7\")"

     

    I'm also not entirely sure, but couldn't you wrap in single quotes to make it easier to read?

     

    conditionalFormatting.getCustom().getRule().setFormula('=OR(RIGHT(A1,1)="2",RIGHT(A1,1)="7")');

     

    What line is line 58 - it'd probably help if you posted the entire code.

    • jsears775's avatar
      jsears775
      Copper Contributor
      You were 100% right! The double quotes seemed to messing it up, it ran without error when I replaced it your correction.

      And apologies, that was lines 56-64 of my script, you ended up right in line 58 anyways. Thank you so much for your help!

Resources