Blinking cell script depending on content - how to do indefinitely

Question

Hi I have the following code:

function onEdit(e) {
  if (e.value == "Requested") {
    var number = 50; // Number of blink
    for (var i = 0; i <= number * 2; i++) {
      if (i % 2 == 0) {
        var color = "white";
      } else {
        var color = "red";
      }
      e.range.setBackground(color);
      SpreadsheetApp.flush();
      Utilities.sleep(1500); // Blink speed (ms)
    }
    e.range.setBackground("white") // Cell color is white after blinks were completed.
  }
}

However for some reason the cell blinks less than 10 times or so even though I have set it to 50. Is there a time limit when google runs a for loop? Is there a way to make it indefinite until I change the cell value to something else?

Answer 1

Making so many calls to Google services will count against your daily quotas. More on quotas here https://developers.google.com/apps-script/guides/services/quotas

Only use service calls for something that's really important like fetching and processing data. Find another solution to highlight the edited cell.

Because of latency, you'll never get the blinking to render perfectly, but you could bring the wait time down to 50 milliseconds. Check out the script below. Be advised, this is extremely bad practice and should be avoided at all costs.

   //junk code: not for use

    var i = 0;

    var isRunning = true;

    while(isRunning) {  

      if (i % 2 == 0) {
        var color = "white";
      } else {
        var color = "red";
      }

      i++;
      e.range.setBackground(color);
      SpreadsheetApp.flush();
      Utilities.sleep(30); // Blink speed (ms)
    }

Try executing it for several cells. You'll notice that the script will stop executing after some time. That's because Google limits total execution time and will not allow your script to run continuously. If you go to View -> Execution transcript in Script Editor, you'll see something like this 'Execution failed: Exceeded maximum execution time [32.548 seconds total runtime]'.