Sunday, September 22, 2013

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:
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,

4 comments:

  1. can i use this script so, at i no need every time run again this, if i change someting.
    I have difficult doit self script- my english is bad.
    Thank you very much!!!

    ReplyDelete
    Replies
    1. Yes, you have to just put this code for once, and then this script will auto run whenever any cell is edited which is inside the provided range...

      Delete
    2. Does not work automatically, when I have cells a1: a9 formula already in.

      Delete
    3. Are you editing any of the cell from A1:A9 ?? If you will edit any of the cell from this range only then the script will execute....

      If still it's not working, then I would suggest to share your spreadsheet and let me have a look at it...

      Delete