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

user_10867713 0
Viewed 117

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

By default Figure

Figure 2 = what i want.

Intended work

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

google-apps-script google-sheets automation javascript
2 Answers

Try this in your conditional formatting

=MOD(ROUND(SUMPRODUCT(1/COUNTIF($A$2:$A2,$A$2:$A2)),0),2)=1

enter image description here

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

enter image description here

user_150193800

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): enter image description here

Documentation:
user_142716330
Related