Google Spreadsheet Script to Change Color of Cell(s) Having Minimum Value

Question:

( by Kaido Põder )

I have range A1:A9 numbers. In Google Spreadsheet, How can I change color of cell having minimum value.

Solution:

Have a look at the following screenshot:

Have a look at the following code:

///////////////////////////////////////

// This script has been developed by Kishan
// For more visit: iGoogleDrive.blogspot.com

var ss = SpreadsheetApp.getActiveSpreadsheet();
var s = ss.getSheetByName("Sheet1");
var r = s.getRange("A1:A9");

function onEdit(e) {
  var activeSheet = ss.getActiveSheet();
  var activeRange = activeSheet.getActiveRange();
  var sortedValues;
  if( s.getName() == activeSheet.getName() &&
    activeRange.getLastRow() >= r.getRow() && 
    activeRange.getRow() <= r.getLastRow() &&
    activeRange.getLastColumn() >= r.getColumn() && 
    activeRange.getColumn() <= r.getLastColumn() )
    { 
      check();
    }
};

function check() {
  var v = r.getValues();
  var minValue;
  var flag=false;
  
  for(var i=0;i<v.length;i++)
    for(var j=0;j<v[0].length;j++) {
      if(v[i][j]!="" && flag==false) {
        minValue=v[i][j];
        flag=true;
      }
      else if(v[i][j]!="" && minValue>v[i][j]) minValue=v[i][j];
    }
  
  var rowcol = [];
  for(var i=0;i<v.length;i++)
    for(var j=0;j<v[0].length;j++)
      if(minValue == v[i][j])
        rowcol.push([i,j]);
  
  r.setBackground("White");
  for(var k=0;k<rowcol.length;k++)
    s.getRange(r.getRow()+rowcol[k][0],r.getColumn()+rowcol[k][1]).setBackground("Yellow");
};

///////////////////////////////////////

put the above code in your script editor and change Sheet1 with your sheet name and A1:A9 with the range that you want to include to check for the minimum value. If you want to check for whole column A then put "A:A" and if you want to check for column A,B,C then put "A:C", so it will work for any range you provide...


And If you are not much familiar with scripts then check out the following link:
http://igoogledrive.blogspot.com/2012/08/how-to-write-script-in-google.html 

I hope the above solution will help you, and if you need more help then please do comment below on this blog itself, I will try to help you out.

I also take up private and confidential projects:

http://igoogledrive.blogspot.com/p/paid-projects.html

If this blog post was helpful to you, and if you think you want to help me too and make my this blog survive then please donate here: http://igoogledrive.blogspot.com/2012/09/donate.html 

Thanks,

Kishan.

iGoogleDrive