I`m trying to write a script that can find the maximum value in a specific column (proportion column in the given dataset) and highlight the cell accordingly.
Below is where I am at so far
function myFunction() {
var ss = SpreadsheetApp.getActive();
var sh = ss.getSheetByName('Sheet1');
var range = sh.getRange("G2:G17");
var values = range.getValues();
var newRange = SpreadsheetApp.getActiveSheet().getRange(2, 7, values.length, 1).getValues();
var maximumproportion = Math.max.apply(Math, newRange);
var maxarr = [];
maxarr.push(maximumproportion)
var backgrounds = [];
var fontColors = [];
range.sort([{column:7, ascending: false}]); // sort by number is column 7
if (newRange === maxarr) {
backgrounds.push(["green"])
}else{
backgrounds.push([null])
}
SpreadsheetApp.getActiveSheet().getRange(2,7,values.length,1).setBackgrounds(backgrounds);
}
And below is the dataset I am using for this
so if the above code properly worked, it should`ve highlighted the first row in the last column which is 0.27% as this is the maximum value found in this column.
For the below part, I`ve also tried using for loop. But no luck there
Can someone please advise on how i can get this work?
// find the max value in Column G
for (var i = 1; i < 17; i++) {
console.log(newRange[i][0])
if (newRange[i] === maxarr[i]) {
backgrounds.push(["green"]);
} else {
backgrounds.push([null]);
}
}
SpreadsheetApp.getActiveSheet().getRange(2,7,16,1).setBackgrounds(backgrounds);
}