Google Spreadsheet Script to Blink a range of Cells

Question

I'm New in scripting, Recently found the below script to make a cell flash or change color when edit. I would like to implement the same script but for a range of cells. I have tried ie: A7:A but it won't work. I believe I'm missing an argument somewhere.

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);
    }
  }
}

Possible duplicate of [Blinking cell script depending on content - how to do indefinitely](https://stackoverflow.com/questions/44388584/blinking-cell-script-depending-on-content-how-to-do-indefinitely) — Rubén, Apr 25 '19 at 02:00

score 1 · Answer 1 · edited May 23 '17 at 10:28

1

I understand you want the edited cell to flash if it's in the range A7:A. This is done below using the event object in which the range property refers to the active range.

If the column is 1 and row >= 7, flash 50 times. (This means 25 seconds of flashing, by the way.)

function onEdit(e) {
  if (e.range.getColumn() == 1 && e.range.getRow() >= 7) {
    for (var i = 0; i < 50; i++) {
      e.range.setBackground(i % 2 ? "WHITE" : "RED");
      SpreadsheetApp.flush();
      Utilities.sleep(500);
    }
  }
}

If you wanted the entire range A7:A to flash when any of its cells is edited, then the relevant method is setBackgrounds, and it takes a double array of colors. This array needs to be prepared first, so the code becomes

function onEdit(e) {
  if (e.range.getColumn() == 1 && e.range.getRow() >= 7) {
    var range = e.range.getSheet().getRange("A7:A");
    var height = range.getHeight();
    var whiteArray = Array.apply(null, Array(height)).map(function() {
      return ["WHITE"];
    });
    var redArray = Array.apply(null, Array(height)).map(function() {
      return ["RED"];
    });
    for (var i = 0; i < 50; i++) {
      range.setBackgrounds(i % 2 ? whiteArray : redArray);
      SpreadsheetApp.flush();
      Utilities.sleep(500);
    }
  }
}

using the array-filling method from this answer.

edited May 23 '17 at 10:28

Community

1
1

answered Jun 28 '16 at 13:00

Great answer. A couple of things you might consider: To only flash column through where you have data instead of to the end of the column change var range = e.range.getSheet().getRange("A7:A"); to var lr=e.range.getSheet().getLastRow(); var range = e.range.getSheet().getRange("A7:A"+lr); Also, if you want the flashing cells to remain red reverse the arrays in range.setBackgrounds(i % 2 ? whiteArray : redArray);//white after flashing range.setBackgrounds(i % 2 ? redArray : whiteArray );// red after flashing – Ed Nelson Jun 28 '16 at 15:45
Excellent, this works great. How would I link this to a specific tab are sheet? the current script works for a single sheet, not for a workbook with many tabs are sheets. also, If I would like to implement this code when a cell has a specific text what else can I add? once again thanks for you help. – Ozz Jun 29 '16 at 17:25
@Ozz If you only want it to blink, use another comparison in `if`, ... `&& e.value = 'blink'`. To restrict this to a particular sheet, also compare the sheet name, `e.range.getSheet().getSheetName() == 'Blinking Sheet'`. – Jun 29 '16 at 23:46
@Bookend would yo be able to give me an example how the code would look like? for some reason I'm getting a range error. sorry for the Noob questions. – Ozz Jul 05 '16 at 15:37

Google Spreadsheet Script to Blink a range of Cells

1 Answers1

Linked