Wednesday, October 10, 2012

Google Spreadsheet Script to Blink a Cell

Question:

Hi all,

I wanted to know is it possible to highlight the cell so that attention of the user gets on that cell?? I mean I already have lots of colored cells now, so can I have a cell which keeps on changing colors ?

I learned about conditional formatting, but that doesn't works for me, I want the cell to keep changing its color.
Just wondering is it possible or not??

Any kind of help would appreciated.

Thanks
Adam Martin


Solution:

If you change the value in Cell A1 then the following script will make Cell A1 to change the color Red and White, it will look as if it is blinking:



function onEdit(e)
{
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var mysheet = ss.getSheetByName("Sheet1");
  var activeCell = ss.getActiveCell().getA1Notation();
  
  if( activeCell == "A1" )
  {
    for(var i=0;i<50;i++)
    {
      if( i%2 == 0 )
        mysheet.getRange("A1").setBackground("RED");
      else
        mysheet.getRange("A1").setBackground("WHITE");
      
      SpreadsheetApp.flush();
      Utilities.sleep(500);
    }
  }
}

Similarly we can script to function as per our conditions and as per our requirement.

NOTE: The above script will blink the Cell for 25 times as I have looped the code for 50 times, you can change it as per your requirement, but take care of the maximum time execution limit of Google Spreadsheet scripts.

And If you are not much familiar with scripts then check out the following link:

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 or 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,
Kishan,

15 comments:

  1. Hi Kishan,
    is there a way to give as input specific cell that i like it to blink?

    Thanks,
    Yaki.

    ReplyDelete
  2. thanks.... going to try if I can do it...

    ReplyDelete
  3. Hi!
    In wich cell must we invoke this script? I wroted "=onEdit(A1)" in B2 but when I write in A1 nothing happens...

    ReplyDelete
  4. I use this script and it works great except that it will blink on all my sheets that have that cell number (i.e. B3, C3). I have it to work on the active sheet but it still is doing this. Any help would be greatly appreciated.

    ReplyDelete
  5. Hi!

    It works.Is there a way to set blinking cells with conditional formatting under columns or rolls when a cell start with specific character?

    Example: I would like to start blinking in the whole column A of any cells when that cell has a specific input of (Letter,word...)

    Thanks,
    Tri.

    ReplyDelete
  6. This comment has been removed by the author.

    ReplyDelete
  7. I'm trying to use the logic in your code on blinking cells to make the cell in column A blink when E in any row equals "Advanced-Ken"

    I want A30 & A32 to blink because E30 and E32 both say "Advanced-Ken"

    ReplyDelete
  8. TypeError: Cannot call method "getRange" of null. (line 12, file "Code")Dismiss

    ReplyDelete
  9. Hi Krishan, This script is almost what I am looking for, but what I need is for 2 cells (B1 and D1) to flash if either is blank, either onEdit or onOpen and to then stop flashing when text is entered. The cells are used for name and date. Thanks Paul

    ReplyDelete
  10. brilliant - worked well thanks

    ReplyDelete
  11. Hey, i have tried and its really work. but one help, i have to blink the column A when the value of column gets below 'X' or above 'X'.

    ReplyDelete
  12. Thanks you very much ..!
    It works for me ..

    ReplyDelete
  13. I want to blink a cell whenever open sheet and it will be for multiple sheet of a spread sheet, how?

    ReplyDelete
  14. if i want blinking unlimited times the what?

    ReplyDelete
  15. Preformed a Switcharoo, And it Worked.

    function onEdit(e)
    {
    var ss = SpreadsheetApp.getActiveSpreadsheet();
    var mysheet = ss.getSheetByName("CombineView");

    if( activeCell == "A3" )
    var activeCell = ss.getActiveCell().getA1Notation();

    {
    for(var i=0;i<50;i++)
    {
    if( i%2 == 0 )
    mysheet.getRange("A3").setBackground("GREEN");
    else
    mysheet.getRange("A3").setBackground("WHITE");

    SpreadsheetApp.flush();
    Utilities.sleep(500);
    }
    }
    }

    ReplyDelete