1

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);
    }
  }
}
  • When should be changed the cell color? – Rubén Jun 3 '17 at 22:44
  • when a cell has the word "Red" I want it to blink Red-White – Areki Jun 5 '17 at 12:26
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: Be sure to 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.

  • 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 '18 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 '18 at 20:32

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

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