Set backgroud foreach cell based on condition google appscript-CodePudding

I have a range that has numeric values. I would like to set the background & font color based on greater than the cell value.

function createEdit() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName("DD");
  var range = sheet.getRange("A2:A"   sheet.getLastRow());
  var rangeN = sheet.getRange("B2:B").getValues();
  var { backgrounds, fontColors } = range.getValues().reduce((o, [a]) => {
    o.backgrounds.push([a > 40 ? "red" : null]);
    o.fontColors.push([a > 40 ? "white" : null]);
    return o;
  }, { backgrounds: [], fontColors: [] });
  range.setBackgrounds(backgrounds).setFontColors(fontColors);

var { backgrounds, fontColors } = range.getValues().reduce((o, [a]) => {
    o.backgrounds.push([a > rangeN ? "red" : null]);
    o.fontColors.push([a > rangeN ? "white" : null]);
    return o;
  }, { backgrounds: [], fontColors: [] });
  range.setBackgrounds(backgrounds).setFontColors(fontColors);
}

When I applied this, it applied to the whole range. Also looking based on greater than other range. I will use Time DrivenTrigger to run the script.

Expected Worksheet demo

CodePudding user response：

From your following reply,

I have a few changes to the question. I will use the function with Time Trigger. Also need to apply the font color as white where the red background is applied.

And, your showing script is my 1st proposed script. I understood that you wanted to add a function for changing the font color as a new request. In order to achieve your 2nd request, my 1st proposed script is required to be modified to your new goal. So, how about the following modified script?

Sample script:

This modified script is for your 2nd question.

function createEdit() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName("DD");
  var range = sheet.getRange("A2:A"   sheet.getLastRow());
  var { backgrounds, fontColors } = range.getValues().reduce((o, [a]) => {
    o.backgrounds.push([a > 40 ? "red" : null]);
    o.fontColors.push([a > 40 ? "white" : null]);
    return o;
  }, { backgrounds: [], fontColors: [] });
  range.setBackgrounds(backgrounds).setFontColors(fontColors);
}

Sample script:

References: