How can I apply alternate color on cells with same values in app script

Question

I have google sheet having 2 columns called start date and end date. while some date has repeated values. I want to find how many entries have same values. if it 2 or more cell has same values apply alternate colors.

All these i want to achive through app script/java script for google sheets.

please see two figures

Figure 1 = normal Figure

Figure 2 = what i want.

So far, i have tried different method using conditional formatting by assigning column value to true and false. but no success yet. any help will be appreciated

Answer 1

This implementation assumes that the dates are exactly the same, down to the second. From what I see in the example you show, the seconds vary in the start-date. Possible modifications to adjust for these problems:

1. Change the range from which they are evaluated from A2:A to B2:B. This assumes that the end-date is in column B. Simply change it to suit your needs.
2. Change the evaluation from equal to a subtraction, so that it does not evaluate if they are equal but that the difference between the two is no more than 5 seconds (or whatever amount you consider). Changing from crrArr[idx - 1] !== crrArr[idx] to Math.abs(crrArr(idx-1) - crrArr[idx] > 5000) for example.

Code.gs

const sS = SpreadsheetApp.getActiveSheet()
function conditionalColors() {
  /* Filter the values to obtain true when the time changes */
  const values = sS
    /* Evaluate end-date `B2:B` */
    .getRange('A2:A')
    .getValues()
    .filter(n => n[0] !== '')
    .flat()
    .map(n => new Date(n).getTime())
    /* Math.abs(crrArr[idx-1] - crrArr[idx]) > 5000 */
    .map((n, idx, crrArr) => idx === 0 ? true : crrArr[idx-1] !== crrArr[idx])

  /* defining the colors*/
  const color1 = [204, 222, 188]
  const color2 = [238, 232, 170]
  let color = color1
  /* Loop to change the color every time we have a true */
  values.forEach((c, idx) => {
    if (c) {
      color = color === color1 ? color2 : color1
    }
    /* In the example Start Date is in A column (A===1) */
    /* And End Date in B Column */
    sS.getRange(idx + 2, 1).setBackgroundRGB(...color)
    sS.getRange(idx + 2, 2).setBackgroundRGB(...color)
  })
}

Example result using Math.abs (Notice A6):

Documentation:

• setBackgroundRGB(red, green, blue)
• Array.prototype.map()