Google App Script give multiple cellRange same conditional format based on 1 cell reference relative to each cellRange

Question

I'm writing a script that set conditional formatting for each group of cells, based on the 1st cell of each group.

For(i=0,i<10,i++)
/*
other part of the script
*/.whenFormulaSatisfied('=INDEX(A1)<0,5')

Instead of A1 I want to use a notation different than A1notation. I need that A1 becomes after each loop C1, then E1, then G1 and so on.

thanks in advance

score 0 · Answer 1 · answered Dec 28 '20 at 06:44

0

.whenFormulaSatisfied(`=INDEX(${String.fromCharCode(65+i)}1)<0,5`)

65 is the starting code for uppercase alphabets and codes for A. So this should work upto Z. If you need more than Z, See this answer

answered Dec 28 '20 at 06:44

TheMaster

Thanks for your answer! I need more than `Z`. I find difficult to implement in my code: `X=lambda n:~n and X(n/26-1)+chr(65+n%26)or'' ;` I'm new to coding and I find difficult to get the meaning of this syntax and apply it correctly in my script code. Anyway I understood the general meaning of your answer and the code to use for uppercase alphabet. – Bertocks Dec 28 '20 at 23:51
@Bertocks There were plenty of other answers in that thread. Anyone of them would've worked. – TheMaster Dec 29 '20 at 04:15

score 0 · Accepted Answer · answered Dec 28 '20 at 15:25

0

You also can use getA1Notation()

  for (var i = 0; i<10; i+=2){
    var cellNotation = sh.getRange(1,i+1).getA1Notation();
    console.log(cellNotation)
  }

Result

3:59:01 PM  Info    A1
3:59:01 PM  Info    C1
3:59:01 PM  Info    E1
3:59:01 PM  Info    G1
3:59:01 PM  Info    I1

answered Dec 28 '20 at 15:25

fullfine

1

This looks like easy to understand and implement, thanks a lot! – Bertocks Dec 28 '20 at 23:39

2 Answers2