Kishan's (advanced Google Spreadsheet) Conditional Formatting Script

As of now (July 2013), Google Spreadsheet doesn't support Conditional formatting by comparing two cells and setting format on any of the two cells or on any third cell. To overcome this I have developed a script.

To install this script go to Script Gallery from your Google Spreadsheet and then search for "Kishan's Conditional Formatting" and then install it.

Here is the screenshot of Sheet 'Conditions' of my spreadsheet, from which the script will copy this sheet to your spreadsheet:

And here is the link of this Spreadsheet.

Now, you have to set all the conditions on this Sheet. And make sure you never rename this sheet, as the script will take inputs of conditions from this sheet and perform its execution.

In Sheet 'Conditions' you have to input two cells for comparison, I will refer them as Cell1 and Cell2 for the rest of this post. And after comparing these cells with condition, you can change the background color of any one cell of the spreadsheet, and I will refer this cell as Cell3. 

How to input the Cell1 and Cell2?
You have to put the Sheet name of Cell1 in Column A and near to that cell you have to put Cell2, that is in Column B. Then you can input the condition in Column C. And similarly, you need to input Cell2 in Column D and E.

And in Column F and G, you need to input Cell3, (Cell3 can be the Cell1 or Cell2 or any other Cell from the spreadsheet). Then Column H contains the color that you need to format the background of Cell3 if condition is true. And put the color in Column I, for if condition is false.

NOTE: You need to input values only up to Column I and leave the rest. Don't try to edit the Columns J,K,L,M...

When the script executes, it will put the status for each condition in Column J. So if there is any error (like if you don't input correct values in Cell1 or Cell2 or Cell3) then you will get Error message in Column J. And if no error then it will show "Done!"

Column K is for getting the list of all sheet in your current Spreadsheet, so that we can have the drop down list (data validation) in Column A,D and F.

Note: This script will auto execute each and every time you make any edit on your spreadsheet, as I have called function ConditionalFormatting() in onEdit(e) event. If you notice that it has reduced the speed of your spreadsheet then you can comment this one line by having two forward slashes, that is "//", in front of the ConditionalFormatting().
By doing so script will stop execution for each and every edit and then you can manually call this function from the "Conditional Script" Menu on the "Tool Bar".

If you have any further questions, then let me know by posting it in the comments below...
Thanks,
Kishan.

Google Spreadsheet Sum values in hh:mm:ss.SSS format

Question:

( by Rick Robinson-Donnellan )

Hi guys

I've done a bit of searching but not been able to find the answer I was looking for.

Basically, I have two lap times (think racing) in the format of:

MM:SS:000 (tenths, hundreds, thousands)

so for example 1:37.456 

I then have another lap time, 1:37.978

I want to be able to add the two times together and come up with the total race time. Every time I do it, it comes up with a bad value and I can't see any option for it in the formatting. I know it must be easy to do but I don't know how.

1 rule however: The two lap times must be simple i.e. I need to be able to just type/copy and paste the numbers in without any formatting and then the Total field work out the rest for itself. I don't want to have to enter formulae into both each lap time fields if that makes sense. I just want the total to work it all out itself.

Thanks in advance

All the best

Rick

Solution:

Have a look at the following screenshot:

I have the following formula in Cell B4:

=KSUM(B1;B2)

the above formula is a custom function KSUM() that I have written and inserted in Script.

Have a look at the following code:

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

function KSUM() {
  var value1 = arguments[0].split(".");
  var value2 = arguments[1].split(".");
  var time1 = value1[0].split(":");
  var time2 = value2[0].split(":");
  var ms       = parseInt(value1[1],10) + parseInt(value2[1],10);
  var seconds1 = parseInt(time1[time1.length-1],10);
  var seconds2 = parseInt(time2[time2.length-1],10);
  var minutes1 = parseInt(time1[time1.length-2],10);
  var minutes2 = parseInt(time2[time2.length-2],10);
  var hours1   = parseInt(time1[time1.length-3],10);
  var hours2   = parseInt(time2[time2.length-3],10);
  var seconds=0,minutes=0,hours=0;
  
  if( seconds1 > 0 && seconds2 > 0 ) seconds = seconds1+seconds2;
  else if( seconds1 > 0 ) seconds = seconds1;
  else if( seconds2 > 0 ) seconds = seconds2;
  
  if( minutes1 > 0 && minutes2 > 0 ) minutes=minutes1+minutes2;
  else if( minutes1 > 0 ) minutes = minutes1;
  else if( minutes2 > 0 ) minutes = minutes2;
  
  if( hours1 > 0 && hours2 > 0 ) hours=hours1+hours2;
  else if( hours1 > 0 ) hours = hours1;
  else if( hours2 > 0 ) hours = hours2;
  
  var total_ms = parseInt(hours*60*60*1000,10)+parseInt(minutes*60*1000,10)+parseInt(seconds*1000,10)+parseInt(ms,10);
  var d = new Date(total_ms);
  
  if(d.getUTCHours()) return d.getUTCHours() + ":" + f00(d.getUTCMinutes()) + ":" + f00(d.getUTCSeconds()) + "." + f000(d.getUTCMilliseconds());
  else return f00(d.getUTCMinutes()) + ":" + f00(d.getUTCSeconds()) + "." + f000(d.getUTCMilliseconds());
};
function f00(n) { return n < 10 ? '0' + n : n; };

function f000(n) { return n < 10 ? '00' + n : n < 100 ? '0' + n : n; };

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

put the above code in your script editor, and then you can use it directly in your Google Spreadsheet or you can use its functionality in the Google Apps Script.


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

How to get list of Sundays within a range of two dates

Question:

( by Paul Winn )

I have a simple church schedule workbook with sheets for every month.  The 3 column headings are Date, Sound, Video.  Under the last 2 columns there will be just names of people assigned for either sound or video.  Since church is on every Sunday in a month, I would like to calculate the specific dates of the the 4 or 5 Sundays (and place them in 4 or 5 cells/rows beneath the "date" heading) for the corresponding month an year.  Maybe it is easier to just look at a calendar and copy the values but if there is a better way I'd love to know.
Thanks

Solution:

For example, if you have Start Date in Cell B1 And End Date in Cell B2

Have a look at the following animated screenshot of my Spreadsheet:

Insert the following formula in Cell A4:

=query(arrayformula(if(mod(weekday(if(B1+row(A:A)-1<=B2;B1+row(A:A)-1;""));7)=1;B1+row(A:A)-1;""));"select * where Col1 is not null";0)

the above formula will give you the list of dates that are all sundays between the dates inserted by you in Cells B1 and B2.

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

Google Spreadsheet Daily insert text at the bottom of a column

Question:

( by Roy Silverman )

Hi all, 

I wanted to ask if it is possible to have a function/formula run on one of my google spreadsheet once everyday automatically. 

The purpose of this formula is to check what is the last cell in a specific column that does not contain any characters and add the number 1. 

Help will be highly appreciated ! 

Thanks !

Solution:

You can achieve this with the help of Scripts.

Have a look at the following screenshot of my Spreadsheet:

Have a look at the following script code:

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

function addValueInLastCell() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName("Sheet1");
  var column = sheet.getRange("C:C"); //for column C
  var values = column.getValues();  
  for(var i=0;i<values.length;i++)
    if(values[i][0]=="") { values[i][0]=1; break; }
  column.setValues(values);
}

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

put the above code in the Script editor of your Spreadsheet. And after inserting this code, you have to set the Triggers (Time Driven) so that the above code executes daily...

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 

And If you are not much familiar with Triggers then check out the following link:
http://igoogledrive.blogspot.com/2012/08/how-to-set-trigger-on-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

Conditionally Change the Color of Entire Row

Question:

( by Paula Lafferty )

I have found that conditional formatting multiple cells based on the value in one cell is possible in google spreadsheets via a script. I am not very familiar with writing or customizing my own scripts, but have used some from the Scripts Gallery.

My workbook has multiple sheets and I would like all sheets to run this script.

The data starts on row 3 to the end of the document (the script doesn't need to apply to rows 1&2)

If there is anything in cell K3 (or K4, K5, etc) I would like that entire row to change the background color to white.

If there is anything in cell P3 (etc.) the row should change background color to green

If there is anything in cell Q3 (etc.) the row should change background color to yellow.

I would like them to work in that order. So if there is something in both K3 and P3 the row should be Green, if there is something in all three cells the row should be Yellow.

I've found a few scripts online, but I have not been able to edit them to do what I want. Is this possible with scripts? Any help would be appreciated!

Solution:

Have a look at the following screenshot of my Spreadsheet:

Have a look at the following script code:

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

function onEdit(e) {
  changeColor();
};

function changeColor() {
  var activeSheet = SpreadsheetApp.getActiveSheet();
  var numRows = activeSheet.getMaxRows();
  var numColumns = activeSheet.getMaxColumns();
  var dataRange = activeSheet.getRange(1, 1, numRows, numColumns);
  var values = dataRange.getValues();
  var backGroundColors = dataRange.getBackgrounds();
  for(var i=2;i<values.length;i++) {
    if(values[i][16] != "") // Column Q
      for(var j=0;j<numColumns;j++)
        backGroundColors[i][j] = "#ffff00"; // Yellow
    else if(values[i][15] != "") // Column P
      for(var j=0;j<numColumns;j++)
        backGroundColors[i][j] = "#00ff00"; // Green
    else if(values[i][10] != "") // Column K
      for(var j=0;j<numColumns;j++)
        backGroundColors[i][j] = "#ffffff"; // White
  }
  dataRange.setBackgrounds(backGroundColors);

};

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

put the above code in the Script editor of your Spreadsheet.

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

Google Spreadsheet Counting in Arrayformula

Question:

( by David.J.A )

a simple formula I would like as a column heading with an array formula, but it's not working.

=IF(Countif(E2:J2,"No")=6,"Ready","Not Ready") << this formula works. If it counts 6 No's, status is ready. Else it's not ready.

When I try to put it in an arrayformula it doesn't work. :(

=Arrayformula(IF(Row(P:P)=1;"Ready for check?";IF(Countif(E1:E:J1:J,"No")=6,"Ready","Not Ready")))

That's what i've tried, now I'm sure the reference E1:E:J1:J is wrong for a start,

Solution:

Have a look at the following screenshot:

I have inserted the following formula in Cell P1:
=Arrayformula(IF(Row(P:P)=1;"Ready for check?";IF((E:E="No")*(F:F="No")*(G:G="No")*(H:H="No")*(I:I="No")*(J:J="No"),"Ready","Not Ready")))

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

Google Spreadsheet Arrayformula Solution

Question:

( by Wood County Systems )

I am trying to calculate totals for a survey about computer ordering.

 

DATA:

Current Computer Type	Current Number of Monitors	Current Keyboard Type	New Computer Type	New Number of Monitors	New Type of Keyboard
Laptop	2	Straight	No Change	No Changes	No Changes
Desktop	1	Straight	Desktop to Laptop	1 Monitor to 2 Monitors	Change to Ergonomic
Desktop	1	Ergonomic	Desktop to Laptop	1 Monitor to 2 Monitors	Change to Straight
Desktop	1	Ergonomic	No Change	No Changes	No Changes

 

In Excel my Fomula looks like this for each cell: =COUNTIF(Sheet1!$A2:$H2, -- AND(Sheet1!$F2="No Change", Sheet1!$C2="Desktop"))

 

My totals look like this for each cell:

0

0

0

1

 

The Double Unary does not work in Sheets.  Please help.

Solution:

Have a look at the following screenshot:

Now, instead of filling formula in each cell, you can use arrayformula to auto compute entire range.

I have inserted the following formula in Cell I2:
=arrayformula(if(C2:C="";"";if((Sheet1!F2:F="No Change")*(Sheet1!C2:C="Desktop");1;0)))

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