3

I am trying to get a cell to blink (alternate colours white-red) when it contains the word "Red". I currently have the following code that I have found, but don't know how to modify it to particularly look for the word every time an edit is made. 

function onEdit(e)
{
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var mysheet = ss.getSheetByName("LBACC17");
  var activeCell = ss.getActiveCell().getA1Notation();

  if( activeCell == "K2" )
  {
    for(var i=0;i<50;i++)
    {
      if( i%2 == 0 )
        mysheet.getRange("K3").setBackground("RED");
      else
        mysheet.getRange("K3").setBackground("WHITE");

      SpreadsheetApp.flush();
      Utilities.sleep(500);
    }
  }
}
Improve this question
2
  • When should be changed the cell color?
    – Rubén ♦
    Jun 3, 2017 at 22:44
  • when a cell has the word "Red" I want it to blink Red-White
    – Areki
    Jun 5, 2017 at 12:26

1 Answer 1

Reset to default
1

This will get you up and running, but you'll probably want to either reduce the loop time or insert a check for if/when the cell is changed to something other than "Red", otherwise it'll just keep blinking until the loop completes.

function onEdit(event){

  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sh = ss.getSheets()[0]; // 0 == first sheet.
  var ee = event.source.getActiveRange().getA1Notation();
  if (ee == "K2") { // Check if edited cell is the one we're watching.
    if (event.value == "Red"){ // If the value == "Red", do stuff.
      var toggle = sh.getRange("K2");

      for(var i=0;i<50;i++) {
      if( i%2 == 0 )
        toggle.setBackground("RED");
      else
        toggle.setBackground("WHITE");

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

Edit
Make sure you consider heavily the oversimplified nature of this script, as noted by Ruben in the comments below. There's plenty of room for this to be fleshed out to handle edge cases.

Improve this answer
2
  • 1
    Considering that the OP is using an script that they found "somewhere", I think that your answer will be even better if you mention the limitations of using your script like it will work only when the edit a are made one cell at a time, since event.value is undefined when doing a autofill down or pasting multiple cells.
    – Rubén ♦
    Jun 11, 2018 at 13:02
  • True, but an edited range as you describe will never equal K2, which we check for. Regardless, I'll make an edit to emphasise this. :-)
    – David Metcalfe
    Jun 12, 2018 at 20:32

Not the answer you're looking for? Browse other questions tagged or ask your own question.