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

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

CodePudding user response：

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:

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.
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:

If you want less precision (for instance only minute), you will have to adjust the formula with round this way
```
=MOD(ROUND(SUMPRODUCT(1/COUNTIF(round($A$2:$A2*60*24),round($A$2:$A2*60*24))),0),2)=1
```