1

I write about the card game bridge, so I need to automate this formatting all the time:

Find all "♣" and color the text green (#00b700).
Find all "♦" and color the text orange (#ff8100).
Find all "♥" and color the text red (#ff0000).
Find all "♠" and color the text blue (#0000ff).

Screenshot of what the script should do.

@Mark built me a script to do this, but I'm getting this error:

"TypeError: Cannot find function setFontColor in object Text."

Would someone please have a look at the script and try to spot the solution? Thanks

  • Please a add a brief description of you search/research efforts made regarding fixing the referred error message as we as adding the textual error message (the code line number is missing). A good practice is to create a minimal complete example and add the code to the question as the external resource could be unavailable in the future. – Rubén Oct 8 at 5:24
  • Hi Ruben. My search/research efforts include hours of googling, trawling forums and stackexchange and reading tutorials. The error message doesn't specify a code line number, here's a screenshot. I opted not to include the example and code in the question itself, as it becomes visually overwhelming, but you'll find that the links are working correctly. I'd be very grateful if someone could offer a suggestion as to how I can fix the code. To test it, just make a google doc with the symbols ♦♣♠ and see if your updated code colours them. Thanks! – J M N Oct 10 at 4:49
0
enter preformatted text here

function testColor() { var spreadsheet = SpreadsheetApp.getActive(); var sheet = spreadsheet.getActiveSheet(); sheet.getRange(1, 1, sheet.getMaxRows(), sheet.getMaxColumns()).activate(); var conditionalFormatRules = spreadsheet.getActiveSheet().getConditionalFormatRules(); conditionalFormatRules.push(SpreadsheetApp.newConditionalFormatRule() .setRanges([spreadsheet.getCurrentCell().offset(0, 0, 1000, 26)]) .whenCellNotEmpty() .setBackground('#B7E1CD') .build()); spreadsheet.getActiveSheet().setConditionalFormatRules(conditionalFormatRules); conditionalFormatRules = spreadsheet.getActiveSheet().getConditionalFormatRules(); conditionalFormatRules.splice(conditionalFormatRules.length - 1, 1, SpreadsheetApp.newConditionalFormatRule() .setRanges([spreadsheet.getCurrentCell().offset(0, 0, 1000, 26)]) .whenTextContains('♣') .setBackground('#B7E1CD') .build()); spreadsheet.getActiveSheet().setConditionalFormatRules(conditionalFormatRules); conditionalFormatRules = spreadsheet.getActiveSheet().getConditionalFormatRules(); conditionalFormatRules.splice(conditionalFormatRules.length - 1, 1, SpreadsheetApp.newConditionalFormatRule() .setRanges([spreadsheet.getCurrentCell().offset(0, 0, 1000, 26)]) .whenTextContains('♣') .build()); spreadsheet.getActiveSheet().setConditionalFormatRules(conditionalFormatRules); conditionalFormatRules = spreadsheet.getActiveSheet().getConditionalFormatRules(); conditionalFormatRules.splice(conditionalFormatRules.length - 1, 1, SpreadsheetApp.newConditionalFormatRule() .setRanges([spreadsheet.getCurrentCell().offset(0, 0, 1000, 26)]) .whenTextContains('♣') .setFontColor('#FF0000') .build()); spreadsheet.getActiveSheet().setConditionalFormatRules(conditionalFormatRules); spreadsheet.getCurrentCell().offset(10, 9).activate(); conditionalFormatRules = spreadsheet.getActiveSheet().getConditionalFormatRules(); conditionalFormatRules.push(SpreadsheetApp.newConditionalFormatRule() .setRanges([spreadsheet.getActiveRange()]) .whenCellNotEmpty() .setBackground('#B7E1CD') .build()); spreadsheet.getActiveSheet().setConditionalFormatRules(conditionalFormatRules); sheet = spreadsheet.getActiveSheet(); sheet.getRange(1, 1, sheet.getMaxRows(), sheet.getMaxColumns()).activate(); conditionalFormatRules = spreadsheet.getActiveSheet().getConditionalFormatRules(); conditionalFormatRules.splice(conditionalFormatRules.length - 1, 1, SpreadsheetApp.newConditionalFormatRule() .setRanges([spreadsheet.getCurrentCell().offset(0, 0, 1000, 26)]) .whenCellNotEmpty() .setBackground('#B7E1CD') .build()); spreadsheet.getActiveSheet().setConditionalFormatRules(conditionalFormatRules); conditionalFormatRules = spreadsheet.getActiveSheet().getConditionalFormatRules(); conditionalFormatRules.splice(conditionalFormatRules.length - 1, 1, SpreadsheetApp.newConditionalFormatRule() .setRanges([spreadsheet.getCurrentCell().offset(0, 0, 1000, 26)]) .whenCellNotEmpty() .build()); spreadsheet.getActiveSheet().setConditionalFormatRules(conditionalFormatRules); spreadsheet.getCurrentCell().offset(10, 9).activate(); conditionalFormatRules = spreadsheet.getActiveSheet().getConditionalFormatRules(); conditionalFormatRules.splice(conditionalFormatRules.length - 1, 1, SpreadsheetApp.newConditionalFormatRule() .setRanges([spreadsheet.getCurrentCell().offset(-10, -9, 1000, 26)]) .whenTextContains('♦') .build()); spreadsheet.getActiveSheet().setConditionalFormatRules(conditionalFormatRules); conditionalFormatRules = spreadsheet.getActiveSheet().getConditionalFormatRules(); conditionalFormatRules.splice(conditionalFormatRules.length - 1, 1, SpreadsheetApp.newConditionalFormatRule() .setRanges([spreadsheet.getCurrentCell().offset(-10, -9, 1000, 26)]) .whenTextContains('♦') .setFontColor('#FFFF00') .build()); spreadsheet.getActiveSheet().setConditionalFormatRules(conditionalFormatRules); spreadsheet.setCurrentCell(spreadsheet.getCurrentCell()) .setRichTextValue(SpreadsheetApp.newRichTextValue() .setText('♦') .setTextStyle(0, 1, SpreadsheetApp.newTextStyle() .setForegroundColor('#000000') .build()) .build()); spreadsheet.getCurrentCell().offset(2, 5).activate(); conditionalFormatRules = spreadsheet.getActiveSheet().getConditionalFormatRules(); conditionalFormatRules.push(SpreadsheetApp.newConditionalFormatRule() .setRanges([spreadsheet.getActiveRange()]) .whenCellNotEmpty() .setBackground('#B7E1CD') .build()); spreadsheet.getActiveSheet().setConditionalFormatRules(conditionalFormatRules); sheet = spreadsheet.getActiveSheet(); sheet.getRange(1, 1, sheet.getMaxRows(), sheet.getMaxColumns()).activate(); conditionalFormatRules = spreadsheet.getActiveSheet().getConditionalFormatRules(); conditionalFormatRules.splice(conditionalFormatRules.length - 1, 1, SpreadsheetApp.newConditionalFormatRule() .setRanges([spreadsheet.getCurrentCell().offset(0, 0, 1000, 26)]) .whenCellNotEmpty() .setBackground('#B7E1CD') .build()); spreadsheet.getActiveSheet().setConditionalFormatRules(conditionalFormatRules); conditionalFormatRules = spreadsheet.getActiveSheet().getConditionalFormatRules(); conditionalFormatRules.splice(conditionalFormatRules.length - 1, 1, SpreadsheetApp.newConditionalFormatRule() .setRanges([spreadsheet.getCurrentCell().offset(0, 0, 1000, 26)]) .whenTextContains('♥') .setBackground('#B7E1CD') .build()); spreadsheet.getActiveSheet().setConditionalFormatRules(conditionalFormatRules); conditionalFormatRules = spreadsheet.getActiveSheet().getConditionalFormatRules(); conditionalFormatRules.splice(conditionalFormatRules.length - 1, 1, SpreadsheetApp.newConditionalFormatRule() .setRanges([spreadsheet.getCurrentCell().offset(0, 0, 1000, 26)]) .whenTextContains('♥') .setBackground('#B7E1CD') .setFontColor('#FF0000') .build()); spreadsheet.getActiveSheet().setConditionalFormatRules(conditionalFormatRules); conditionalFormatRules = spreadsheet.getActiveSheet().getConditionalFormatRules(); conditionalFormatRules.splice(conditionalFormatRules.length - 1, 1, SpreadsheetApp.newConditionalFormatRule() .setRanges([spreadsheet.getCurrentCell().offset(0, 0, 1000, 26)]) .whenTextContains('♥') .setFontColor('#FF0000') .build()); spreadsheet.getActiveSheet().setConditionalFormatRules(conditionalFormatRules); conditionalFormatRules = spreadsheet.getActiveSheet().getConditionalFormatRules(); conditionalFormatRules.push(SpreadsheetApp.newConditionalFormatRule() .setRanges([spreadsheet.getCurrentCell().offset(0, 0, 1000, 26)]) .whenCellNotEmpty() .setBackground('#B7E1CD') .build()); spreadsheet.getActiveSheet().setConditionalFormatRules(conditionalFormatRules); conditionalFormatRules = spreadsheet.getActiveSheet().getConditionalFormatRules(); conditionalFormatRules.splice(conditionalFormatRules.length - 1, 1, SpreadsheetApp.newConditionalFormatRule() .setRanges([spreadsheet.getCurrentCell().offset(0, 0, 1000, 26)]) .whenTextContains('♠') .setBackground('#B7E1CD') .build()); spreadsheet.getActiveSheet().setConditionalFormatRules(conditionalFormatRules); conditionalFormatRules = spreadsheet.getActiveSheet().getConditionalFormatRules(); conditionalFormatRules.splice(conditionalFormatRules.length - 1, 1, SpreadsheetApp.newConditionalFormatRule() .setRanges([spreadsheet.getCurrentCell().offset(0, 0, 1000, 26)]) .whenTextContains('♠') .setBackground('#B7E1CD') .setFontColor('#FF0000') .build()); spreadsheet.getActiveSheet().setConditionalFormatRules(conditionalFormatRules); conditionalFormatRules = spreadsheet.getActiveSheet().getConditionalFormatRules(); conditionalFormatRules.splice(conditionalFormatRules.length - 1, 1, SpreadsheetApp.newConditionalFormatRule() .setRanges([spreadsheet.getCurrentCell().offset(0, 0, 1000, 26)]) .whenTextContains('♠') .setFontColor('#FF0000') .build()); spreadsheet.getActiveSheet().setConditionalFormatRules(conditionalFormatRules); };

  • Thanks @Steve ! Would you mind pasting it here and posting the link, so that your code retains the line-breaks? When I tried pasting it into Google Scripts it all appeared on one line. – J M N Oct 14 at 1:23

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.