Empresas
Empleos
  • Sobre nosotros
  • Soluciones
    • Publicación de vacantes
      Publica tu vacante y recibe candidatos calificados en 48h.
    • Evaluación de candidatos
      500+ pruebas técnicas y psicológicas, más anti-fraude.
    • Headhunting
      Búsqueda ejecutiva a la medida de principio a fin.
    • Nómina + EOR
      Dispersión de nómina y EOR en más de 15 países de LATAM.
  • Precios
  • Empleos

0

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

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

about 4 years ago · Juan Pablo Isaza
2 Respuestas
Responde la pregunta

0

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

about 4 years ago · Juan Pablo Isaza Denunciar

0

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:
  • setBackgroundRGB(red, green, blue)
  • Array.prototype.map()
about 4 years ago · Juan Pablo Isaza Denunciar
Responde la pregunta
Encuentra empleos remotos

¡Descubre la nueva forma de encontrar empleo!

Top de empleos
Top categorías de empleo
Empresas
Publicar vacante Precios Comercial
Legal
Términos y condiciones Política de privacidad
© 2026 PeakU Inc. All Rights Reserved.
Andres GPT
Recomiéndame algunas ofertas
Necesito ayuda