Search code examples
google-apps-script

How do I fix this AppScript Array Value Comparison error with values of different lengths


I have a google sheet appscript macro that is comparing weekly scores across a set of teams in a league. It's checking which team would have beat each other each week by comparing scores across a set of categories. There is some logic to set whether some categories score for being higher or lower. The macro is correctly comparing scores in nearly all cases, but I eventually found a bug that I can't sort out.

  // Get all relevant data from the stat sheet in one go
  const statsRange = statSheet.getRange('C8:C234').offset(0, weeks);
  const statsData = statsRange.getValues();

  // Iterate through teams (assuming 12 teams)
  for (let i = 0; i <= 11; i++) {
    for (let j = 0; j <= 11; j++) {
      if (i===11&&j===11) {break}
      if (j===i) {j=j+1;}
      let score = 0;

      // Iterate through stats for each team pair, comparing and calculating score. 
     Scores are across 19 rows per team in the data set. There are 15 relevant rows. 

      for (let k = 0; k <= 15; k++) { 
        var homeStat = statsData[(19 * i) + k]; 
        var awayStat = statsData[(19 * j) + k];

        // Skip some stats and handle some differently
        if (k === 0 || k === 1 || k === 3 || k === 4) {continue}
        else if (k === 13||k === 14) {
          score += (homeStat < awayStat) ? 1 : (homeStat > awayStat) ? -1 : 0;
        } else {
          score += (homeStat > awayStat) ? 1 : (homeStat < awayStat) ? -1 : 0;
        }
        console.log(i, j, k, homeStat, awayStat, score)
      }

An example input data set

Cell Value
C198 180
C199 409
C200 0.44
C201 114
C202 148
C203 0.77
C204 68
C205 542
C206 43
C207 174
C208 107
C209 28
C210 14
C211 55
C212 0
C213 0
C214 7
C215
C216
C217 130
C218 273
C219 0.476
C220 65
C221 82
C222 0.793
C223 35
C224 360
C225 42
C226 142
C227 87
C228 31
C229 25
C230 52
C231 0
C232 1
C233 7

For some reason, if HomeStat and AwayState of different orders of magnitude this comparison produces inverse results. An example log/output: i and j are teams in the sheet. k is the stat category. Home and away stats are the inputs from each team for the category. The score should increment up or down based on the logic. k = 10 is the error. It increments up when home score is less than away score.

|time|log|i|j|k|home stat|away stat|score|
6:59:04 PM  Info    11 10 2 [ 0.476 ] [ 0.44 ] 1
6:59:04 PM  Info    11 10 5 [ 0.793 ] [ 0.77 ] 2
6:59:04 PM  Info    11 10 6 [ 35 ] [ 68 ] 1
6:59:04 PM  Info    11 10 7 [ 360 ] [ 542 ] 0
6:59:04 PM  Info    11 10 8 [ 42 ] [ 43 ] -1
6:59:04 PM  Info    11 10 9 [ 142 ] [ 174 ] -2
6:59:04 PM  Info    **11 10 10 [ 87 ] [ 107 ] -1**
6:59:04 PM  Info    11 10 11 [ 31 ] [ 28 ] 0
6:59:04 PM  Info    11 10 12 [ 25 ] [ 14 ] 1
6:59:04 PM  Info    11 10 13 [ 52 ] [ 55 ] 2
6:59:04 PM  Info    11 10 14 [ 0 ] [ 0 ] 2
6:59:04 PM  Info    11 10 15 [ 1 ] [ 0 ] 3

The logic always applies +1 to the score that's a shorter length (2 vs. 3 numbers), regardless if it's home or away. If both scores are 2 or 3 numbers long it scores correctly.


Solution

  • The logic always applies +1 to the score that's a shorter length (2 vs. 3 numbers), regardless if it's home or away.

    Chances are that those values aren't numbers but text strings that just look like numbers. Text strings compare lexicographically so "10" < "2".

    To make it work, format the column as Format > Number > Automatic instead of Format > Number > Plain text, or use Number(), like this:

          for (let k = 0; k <= 15; k++) { 
            const homeStat = Number(statsData[(19 * i) + k]); 
            const awayStat = Number(statsData[(19 * j) + k]);
    

    See Number().