Search code examples
google-apps-scriptgoogle-sheetsarray-formulasconditional-formatting

slow conditional formatting rule (look for an alternative)

I am looking for an alternative to my current conditional formatting rule. The rule is as follows:

=SUM(ARRAYFORMULA(--ISNUMBER(SEARCH({" + grpOneStr + "}, A1))))

I build the variable according to how the formula should be used which is explained here: https://infoinspired.com/google-docs/spreadsheet/multiple-search-in-single-search-formula-google-sheets/#:~:text=We%20can%20use%20multiple%20search,more%20than%20one%20search%20string.

A snippet of the script I use is as follows:

// conditional formatting
  var grpOneStr = "Confirmed, Confirmed (Paid)";
  var grpTwoStr = "Sent, Posted (Threshold)";
  var grpThreeStr = "Cancelled, Complete";

  var grpOneFormula = "=SUM(ARRAYFORMULA(--ISNUMBER(SEARCH({\"Confirmed\",\"Confirmed (Paid)\"},  A1))))";
  var grpTwoFormula = "=SUM(ARRAYFORMULA(--ISNUMBER(SEARCH({\"Sent\",\"Posted (Threshold)\"},  A1))))";
  var grpThreeFormula = "=SUM(ARRAYFORMULA(--ISNUMBER(SEARCH({\"Cancelled\",\"Complete\"},  A1))))";

  var grpOneRule = SpreadsheetApp.newConditionalFormatRule()
  .whenFormulaSatisfied(grpOneFormula)
  .setBackground('#4fbbbf')
  .setRanges([promoSheet.getRange(1, 1, promoMaxRow, promoMaxCol)])
  .build();

The reason I am looking for an alternative is because the current formula slows down my script considerably at times. Is there any solution to this?

Solution

  • I believe your goal is as follows.

    • You want to look for a workaround for achieving your current situation using your script by reducing the process cost.

    As a workaround, how about using OnEdit trigger and TextFinder? In this workaround, the following flow is run.

    1. Edit a cell in a sheet.
    2. Check all cells of the data range using TextFinder. And, the background color is set.

    When this flow is reflected in a Google Apps Script, how about the following sample script?

    Sample script:

    Please copy and paste the following script to the script editor of Spreadsheet. And, please set your sheet name. In this script, "Sheet1" is used. And, please set obj.

    When you use this script, please edit the cell. For example, when "Confirmed" is put to the cell, the background of the cell is changed.

    function onEdit(e) {
  const { range } = e;
  const sheet = range.getSheet();
  if (sheet.getSheetName() != "Sheet1") return;

  // Please set patterns and colors.
  const obj = [
    { pattern: ["Confirmed", "Confirmed \\(Paid\\)"], color: '#4fbbbf' },
    { pattern: ["Sent", "Posted \\(Threshold\\)"], color: '#4fbbbf' },
    { pattern: ["Cancelled", "Complete"], color: '#4fbbbf' },
  ];

  const r = sheet.getDataRange().setBackground(null);
  obj.forEach(({ pattern, color }) => {
    const rangeList = r.createTextFinder(pattern.join("|")).useRegularExpression(true).findAll().map(r => r.getA1Notation());
    sheet.getRangeList(rangeList).setBackground(color);
  });
}

    • In this script, all cells of the data range are updated. If you want to update only the edited cell, please modify it as follows.

      • From

          const r = sheet.getDataRange().setBackground(null);

      • To

          const r = range.setBackground(null);

    Note:

    • If your sheet has still conditional format rules, please remove them and test this script.

    References: