CODEWITHSUNDEEP
javascriptgoogle-apps-scriptgoogle-sheets
StarSpirit_2000
StarSpirit_2000

Reputation: 125

highlighting maximum values in a specific column using Google App Script

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);
}

Upvotes: 3

Views: 1142

Answers (1)

fullfine
fullfine

Reputation: 1461

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.

Upvotes: 5

Related Questions