3

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 enter image description here

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);
}
StarSpirit_2000
  • 125
  • 2
  • 11
  • 1
    Try Math.max(...newRange.flat()); [ref](https://developer.mozilla.org/en-US/docs/Web/JavaScript/Reference/Global_Objects/Math/max) – Cooper Dec 28 '20 at 04:29
  • 1
    See https://stackoverflow.com/questions/63720612/ Your comparison `if` statements are all wrong. Also see https://developer.mozilla.org/en-US/docs/Web/JavaScript/Equality_comparisons_and_sameness – TheMaster Dec 28 '20 at 04:49
  • @TheMaster - thanks for the link. That helped. I used the pasted code to make it work - for (var i = 0; i < newRange.length; i++) { if (newRange[i][0] === maximumproportion) { colors.push(["#FFDD88"]); } else { colors.push([null]); } } range.setBackgrounds(colors) } – StarSpirit_2000 Dec 28 '20 at 09:35
  • 1
    @Jason1993 Consider adding a answer with explanations. Glad it helped! – TheMaster Dec 28 '20 at 09:57

1 Answers1

4

Answer 1

You can highlight the maximum value in a specific column using Conditional formatting.

Steps

  1. Go to Format > Conditional formatting
  2. Select the range to apply the format
  3. Select Custom formula is in Format cells if
  4. Apply =(G:G)=MAX(G:G) to select the cell with highest value

Answer 2

You can get and highlight the highest value in a simple way:

function myFunction() {
  var ss = SpreadsheetApp.getActive();
  var sh = ss.getSheetByName('Sheet1');
  var range = sh.getRange("D2:D14")
  var values = range.getValues();
  var merged = [].concat.apply([], values);
  var max = Math.max.apply(Math,merged)
  var maxid = merged.indexOf(max)
  range.getCell(maxid+1,1).setBackgroundRGB(0,255,0)
}

Explanation

  1. var merged = [].concat.apply([], values) transforms an array of arrays into an array of integers.
  2. var max = Math.max.apply(Math,merged) gets the highest value of the range
  3. var maxid = merged.indexOf(max) gets the index of the highest value
  4. range.getCell(maxid+1,1).setBackgroundRGB(0,255,0) change the background of the desired cell. Keep in mind that the index of the array starts from 0 and the index from the cell from 1, so you have to sum 1 to the index. Furthermore, in RGB, green is equal to zero red, full green, zero blue.
fullfine
  • 1,371
  • 1
  • 4
  • 11
  • thanks for the help :) I really like your code approach here. This is much more simpler and straight to the point solution... – StarSpirit_2000 Dec 29 '20 at 03:14
  • 1
    If you find my solution useful please mark it as accepted so the community will know that it helped you. – fullfine Dec 29 '20 at 08:04