0

I am trying to get a cell to blink when a file is opened. I currently have the following code that I have found from here, but don't know how to modify it to blink when the file is open. Can anyone help?

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);
    }
  }
}
2
  • Welcome to Web Applications Stack Exchange. This request comes up every now and then. Google Apps Script runs on Google's servers, not in your computer, so this type of interactivity is not as practical as one might think. Consider using some way to draw a user's attention to a particular cell. If you require more help, please describe the end goal you are trying to solve by blinking a cell. Jan 2, 2023 at 4:28
  • @doubleunary Just in case you are wondering why there is so much attention on this question, it was bumped by Community User :) Sep 29, 2023 at 18:35

2 Answers 2

1

Edited Script

function onOpen(e) {

  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var mysheet = ss.getActiveSheet();

  for(var i=0; i<38; i++) {
     (i) && Utilities.sleep(400); 
     if( i%2 == 0 ) {
      
       mysheet.getRange("A3").setBackground("RED");
      
     } else {
      
       mysheet.getRange("A3").setBackground("WHITE");
      
     }
      
     SpreadsheetApp.flush();
    
  }
  
}

Time Limit

Credit to Rubén for raising this issue.
Simple triggers are subject to several restrictions which you should review.

For example, they cannot run for longer than 30 seconds.

The original script needs ~40 seconds to complete so will always time out. You can reduce the interval, reduce the loops, or both. Factors outside your control may also affect the duration of the same script at different times.

Successful Tests

Note that the first test listed an execution time of >30s, but still succeeded. Your mileage may vary.

Loops Sleep (s) Runtime (TTL) Runtime (AVG)
38 0.50 30.209 0.79497
38 0.40 25.637 0.67466
38 0.35 23.694 0.62353

Script Changes Explained

  1. onOpen() replaced onEdit(e)
  2. getActiveSheet() replaced getSheetByName("LBACC17")
    • if the user is expected to actually see the blinking when they open the spreadsheet it must be the active sheet.
    • if blinking should be skipped unless the active sheet matches a specific name then that condition can of course be added
  3. Active cell on open should be A1 so deleted:
    • var activeCell = ss.getActiveCell().getA1Notation()
    • if( activeCell == "K2" )
  4. Removed Utilities.sleep(500) from the end of the loop since that would add an unnecessary delay to the end of the last loop.
  5. Added (i) && Utilities.sleep(400) to the beginning of the loop so the next iteration is delayed unless it is the first iteration (i==0).
4
  • I hope you don't forget that simple triggers have a 30-second time execution limit. An alternative might be an installable trigger, but you should consider the Google Apps Script quotas. Another alternative might be to open a sidebar and use client-server communication, but this option has its caveats. Sep 29, 2023 at 18:23
  • This is a question about blinking indefinitely Blinking cell script depending on content - how to do indefinitely Sep 29, 2023 at 18:44
  • Thanks for the mention. To overcome the 30 seconds execution time limit of the simple triggers, remember that it's possible to use an installable trigger. Bear in mind that if you set the installable trigger to call a function using a name reserved for a simple trigger, the function will be called two times, one by the installable trigger and another by the simple trigger. I suggest that the keep blinking indefinitely be handled in another question if someone is interested. Still, I think that this question is a Problem X Y. Sep 30, 2023 at 0:29
  • Good points. OP presented script to be adapted to on open so I (happily) stayed in that lane. Always learn a lot from your comments
    – Blindspots
    Sep 30, 2023 at 0:46
-1

To run code when a user opens the spreadsheet, use an onOpen(e) simple trigger.

2
  • Although true, the statement fails to address the question in the context of Web Applications SE. Rather than answer the words used by the OP, the answer should point to the solve the problem that the OP is facing. This question is a case of a situation known as Problem X-Y. What you have mentioned as a comment could be more appropriate as the answer. As is very common, it might be worth the community work on creating a canonical question for users trying to use scripts found on the web to do impractical things and point them in the right direction. Sep 29, 2023 at 17:43
  • Another possibility is that you work independently, and if it convinces others to be canonical, your post might be used as a reference / flag or vote to close as duplicate. Sep 29, 2023 at 17:47

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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