CODEWITHSUNDEEP
google-apps-scriptgoogle-sheetstriggers
Kim Hopkins
Kim Hopkins

Reputation: 85

OnSelectionChange Not Working Consistently with SpreadsheetApp.getUI().alert("Message") - Google Apps Script

This is the most bizarre thing. I have a simple trigger OnSelectionChange function that gives alerts and runs different functions depending on which specified cells are clicked. It was working. Now it's not working. Sometimes if I close the program, swear a lot, and open it again, it will work, sometimes not. It is not working 90% of the time.

In the example below, if you are on the sheet Units and you click on the cell range titled SaveUpdateA1, it should give the alert "Why is this not working?". Nothing happens. The executions view says "Paused" then eventually says "Timed Out".

Example

function onSelectionChange(e) {
  var app = SpreadsheetApp; 
  var activeSheet = app.getActiveSpreadsheet(); //Get current active spreadsheet
  var sheet = e.source.getActiveSheet().getName(); //Gets name of sheet where cell was clicked

  //row and column index of active (clicked) cell: 
  var range = e.range; //Activates range of clicked cell
  var rangeA1 = range.getA1Notation(); //Gets A1 notation of clicked cell
  var row = range.getRow(); //Gets row # of clicked cell
  var col = range.getColumn(); // Gets col # of clicked cell
  
  // UNIT SHEET TRIGGERS:
  if(sheet == "Units"){
    var sheetUnits = activeSheet.getSheetByName('Units');
  
    //Variables for Buttons to Save/Update, Add New, Vacate/Archive, and Cancel
    var SaveUpdateA1 = sheetUnits.getRange('O8:O9').getA1Notation(); //A1 notation of Save/Update button
    //Other buttons omitted for this example

    //SAVE/UPDATE BUTTON
    if(rangeA1 == SaveUpdateA1){  
      SpreadsheetApp.getUi().alert("Why aren't you working?");
    }

If I replace the last function in the code above, with the following, it works, albeit pretty slowly:

    if(rangeA1 == SaveUpdateA1){  
      sheetUnits.getRange('P2').setValue("Wow");
    }

I saw one thread that said it needs to be an installable trigger to use .getUi().alert(), but then they said that onSelectionChange was not a permissible installable trigger. (I need onSelectionChange because I'm simulating clicking buttons).

Another person said that .getUi().alert() should work in onSelectionChange but it's clearly not in my case.

I would be perfectly happy with an alternative to .getUi().alert() if one exists and works consistently with onSelectionChange.

Does anyone know how to fix this or use an alternative function? Thank you so much!

Upvotes: 0

Views: 1651

Answers (3)

Kevin
Kevin

Reputation: 33

From observation and extensive experience with the indispensable onSelectionChange trigger: Sometimes after making a change -- any change -- to the onSelectionChange code, the server-side JS engine will freeze up at any Ui-related line of code. If you look at the the Executions log when the freeze happens, you'll see the onSelectionChange trigger Status column will show "Paused", but there will be no message, dialog, nothing displayed over the sheet. After the execution time out period, the log status will show "Timed Out". THERE IS ABSOLUTELY NOTHING YOU CAN DO EXCEPT WAIT. Eventually, (say, a hour or two later), it all starts working again. Is this a bug? YES, it is. Is there a ticket for this? All I could find is this: "https://issuetracker.google.com/178615259 onSelectionChange in Sheets is not working" but that is a different issue.

Upvotes: 0

Dave W
Dave W

Reputation: 1

It is now December 2022 and it still seldom works. I too reported it years ago.

Here is the function I'm trying to use:

function onSelectionChange(e) {
 if (e.range.rowStart != e.range.rowEnd || e.range.columnStart != e.range.columnEnd) {
  SpreadsheetApp.getUi().alert("WARNING", "More than one cell has been selected for editing.", SpreadsheetApp.getUi().ButtonSet.OK);
 }
}

It was intended to warn users that they might be going to do something wrong. All I can do now is throw up warnings using function onEdit(e) AFTER they've gone wrong.

Upvotes: -1

doubleunary
doubleunary

Reputation: 18864

In my experience, the onSelectionChange() simple trigger has always worked pretty unreliably. The documentation tells:

To activate this trigger, you must refresh the spreadsheet once the trigger is added and every time the spreadsheet is opened. [...] If the selection moves between multiple cells in a short time, some selection change events might be skipped to reduce latency.

Star issue 178615259 in the tracker if you would like Google to fix this.

One workaround in your use case would be to use an onEdit(e) simple trigger to let you use checkboxes as script runner buttons. See the checkboxButtons_ script for sample code.

Upvotes: 2

Related Questions