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.
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().