Is there a more efficient formula to apply conditional formatting using the ROW function?

Question

I am using a custom formula with conditional formatting on my MAIN tab of my Google Sheet which turns cells in Rows 2:22 RED when an entry is either a '4', an '✘', or outside the upper and lower limits defined for each row by variables on a second DATA sheet by checking for either a '±' or a '-' symbol and applying the appropriate limits defined for each one.

My issue is that this is taking a considerable amount of time for the conditional formatting to apply, as my actual sheet is at the point where there are hundreds of columns of data to process. I am wondering whether there is a more efficient formula which could provide the same outcome. This is my current formula:

=IF(NOT(ISBLANK(J2)),IF(OR(J2=4,J2="✘"),TRUE,IF(REGEXMATCH(INDIRECT("Data!$C"&ROW()),"±"),IF(OR(J2>$D2+REGEXEXTRACT(INDIRECT("Data!$C"&ROW()),".*\±(.*)"),J2<$D2-REGEXEXTRACT(INDIRECT("Data!$C"&ROW()),".*\±(.*)")),TRUE,FALSE), IF(REGEXMATCH(INDIRECT("Data!$C"&ROW()),"-"),IF(OR(J2>$D2+REGEXEXTRACT(INDIRECT("Data!$C"&ROW()),".*\+(.*)"),J2<$D2-REGEXEXTRACT(INDIRECT("Data!$C"&ROW()),".*\-(.*)")),TRUE,FALSE),FALSE))),FALSE)

Additionally, I am trying to figure out how to implement another formula using COUNTIF to have each cell in Row 24 count the number of times conditional formatting returns TRUE for each corresponding column, but all I can accomplish is to get it looking at the first cell in the range, possibly due to using the ROW function in my formula.

What would the best approach to this be?

Answer 1

From the following reply,

I hadn’t considered the option of Google Apps Script as I’m more familiar with formulas myself, but if that would help reduce the process cost, I would definitely be interested to see how that could help.

And, about

My issue is that this is taking a considerable amount of time for the conditional formatting to apply, as my actual sheet is at the point where there are hundreds of columns of data to process.

although I'm not sure about your actual Spreadsheet, in order to reduce process cost, as one direction, how about using Google Apps Script instead of the formula?

When your formula is converted to Google Apps Script, how about the following sample script?

Sample script:

Please set your data sheet name and source sheet name and save the script.

const dataSheetName = "DATA"; // Please set data sheet name.
const srcSheetName = "Sheet1"; // Please set source sheet name.

function sample() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const dataSheet = ss.getSheetByName(dataSheetName);
  const srcSheet = ss.getSheetByName(srcSheetName);
  const srcValues1 = srcSheet.getRange("D1:D" + srcSheet.getLastRow()).getValues();
  const dataValues = dataSheet.getRange("C1:C" + dataSheet.getLastRow()).getDisplayValues().map(([c], i) => {
    const t = Number(srcValues1[i][0]);
    if (c.includes("±")) {
      const v = Number(c.replace("±", ""));
      return [t - v, t + v];
    } else if (c.includes("+") || c.includes("-")) {
      const [p, m] = [c.match(/\+(.+)/), c.match(/\-(.+)/)].map(e => e ? Number(e[1]) : 0);
      return [t - m, t + p];
    }
    return null;
  });
  const range = srcSheet.getRange("J1:AA" + srcSheet.getLastRow());
  const currentColors = range.getBackgrounds();
  const colors = range.getValues().map((r, i) => {
    const t = dataValues[i];
    if (!t) {
      return r.map((c, j) => [4, "✘"].includes(c) ? "#ea9999" : (c.toString() ? "#b6d7a8" : currentColors[i][j]));
    }
    const [a, b] = t;
    return r.map((c, j) => (c.toString() && !(c >= a && c <= b)) ? "#ea9999" : (c.toString() ? "#b6d7a8" : currentColors[i][j]));
  });

  // I modified the below script for your new question.
  colors.pop();
  const lastRow = colors[0].map((_, c) => colors.map(r => r[c])).map(c => c.includes("#ea9999") ? "#ea9999" : null);
  range.setBackgrounds([...colors, lastRow]);
}

• When this script is manually run to your provided Spreadsheet, I can confirm that the same situation with your formula is obtained.

• For example, when you want to automatically run by editing sheets of the data sheet and source sheet, please add the following script. By this, when the data sheet and source sheet are edited, the script is automatically run. In this case, please don't directly run onEdit function.

  function onEdit(e) {
    const sheet = e.range.getSheet();
    if (![dataSheetName, srcSheetName].includes(sheet.getSheetName())) return;
    sample();
  }
  

• About your new question of is there an efficient way to have the cells in Row 24 turn red if any of the cells in the columns above them are red?, I updated the above script.

  • I understood your new question as follows.
  • In your provided Spreadsheet, for example, when one of the cells in column "J" is "red" color (#ea9999), you want to set "red" color (#ea9999) to column "J" of the bottom row (in your current situation, it's row 24.).

Note:

• This sample script is for your provided Spreadsheet. When you change the Spreadsheet, this script might not be able to be used. Please be careful about this.

References:

• map()
• setBackgrounds(color)