1

I am trying to show an alert popup when cell selection is changed, using the new trigger onSelectionChange. For some reason it is not showing any alerts. Am i doing something wrong or alerts does not work with this trigger?

function onSelectionChange(e) {
      showAlert();
}
function showAlert() {
  var ui = SpreadsheetApp.getUi();
  var result = ui.alert(
                  'ALERT!',
                  'ALERT MESSAGE.',
               ui.ButtonSet.OK);
}

I also tried that way:

function onSelectionChange(e) {
  var ui = SpreadsheetApp.getUi();
  var result = ui.alert(
                  'ALERT!',
                  'ALERT MESSAGE.',
               ui.ButtonSet.OK);
}
Rubén
  • 34,714
  • 9
  • 70
  • 166
Lucas Matos
  • 1,112
  • 5
  • 25
  • 42
  • 2
    In the current stage, the trigger of `onSelectionChange` cannot be used as the installable trigger. By this, when `onSelectionChange` is executed as the simple trigger, an error occurs at `SpreadsheetApp.getUi()`. When OnSelectionChange trigger could be used as the installable trigger, I think that your goal can be achieved. I think that this thread might be useful. https://stackoverflow.com/q/62647068/7108653 – Tanaike Aug 29 '20 at 01:28
  • 1
    See View>Executions for the error – TheMaster Aug 29 '20 at 01:31
  • 1
    @TheMaster Wow! Now I tested OP's script several times. I noticed that the dialog is sometimes opened without error. I thought that the specification might have been changed. When you tested it, what result will you get? – Tanaike Aug 29 '20 at 01:35
  • 2
    @Tanaike I didn't test, but I expect alerts to show. This is also mentioned in others answers. The error will only occur if ``ui`` calls any html service like modal or sidebar. That's why I recommend OP to check his executions. – TheMaster Aug 29 '20 at 01:37
  • When i run from editor it works. But inside sheet it do not work. The execution history shows "Execution paused". – Lucas Matos Aug 29 '20 at 01:37
  • 1
    @TheMaster Thank you. When OnSelectionChange was released, when I tested `SpreadsheetApp.getUi()`, I confirmed that an error related to the authorization occurred. So I thought that the specification had been changed. – Tanaike Aug 29 '20 at 01:39
  • 1
    @Lucas Matos For example, when you created new Spreadsheet and put your script to the script editor and select a cell, what result will you get? In my environment, the dialog is opened. Or, when you put use this script `function onSelectionChange(e) { Browser.msgBox("sample"); }`, what result will you get? – Tanaike Aug 29 '20 at 01:41
  • 1
    @Tanaike AFAIK, Alerts have always been allowed. Before 2 years or so, even htmlservice dialogs/sidebar was allowed. Then there was backlash due to a hack published in news, where shared sheets were opened by victims, which load malicious JavaScript(not sure whether you remember). After that Google removed html from simple triggers(If I remember all that right). – TheMaster Aug 29 '20 at 01:43
  • @Tanaike in a new spreadsheet it is really working. But in my production spreadsheet it does not work!! lol.. weird... :( – Lucas Matos Aug 29 '20 at 01:45
  • 1
    @TheMaster Thank you for the information. From your information, I might have misunderstood the modal, modeless dialog and the alert dialog. – Tanaike Aug 29 '20 at 01:46
  • @Tanaike `function onSelectionChange(e) { Browser.msgBox("sample"); }` works running from editor, but nothing happened when clincking on cells.... – Lucas Matos Aug 29 '20 at 01:48
  • 1
    @Lucas Matos Thank you. About `my production spreadsheet`, when you copy it and test it for the copied Spreadsheet, what result will you get? – Tanaike Aug 29 '20 at 01:49
  • @Tanaike Surprisingly it worked on the copy.... – Lucas Matos Aug 29 '20 at 01:53
  • 2
    @Lucas Matos Thank you for testing it. I have sometimes had the same case that when the situation doesn't work at the existing Spreadsheet, it can be resolved by copying Spreadsheet. So I proposed it. But unfortunately, I'm not sure about the actual reason of your issue. I apologize for this. – Tanaike Aug 29 '20 at 01:57
  • Ok! Thank you for your attention!! – Lucas Matos Aug 29 '20 at 02:00
  • 1
    @Lucas Matos As one confirmation point, can I ask you about the created time of `my production spreadsheet`? When the Spreadsheet is copied, the Spreadsheet of new version might be used as the base Spreadsheet of destination and the data of source Spreadsheet might be copied to the destination Spreadsheet. By this, the issue might be resolved. But this is just my guess. – Tanaike Aug 29 '20 at 02:07
  • 1
    What web browser are you using? Are you signed in on multiple accounts? What runtime you are using? Does your project has other functions? The "executions paused" messae might be shown when the script is waiting for the user to click the pop-up button. Have you click on the button before reaching the maximum execution time (6 / 30 mins)? – Rubén Aug 29 '20 at 04:39
  • @Tanaike man, i think this trigger needs some time to start working properly. It is working now on my production spreadsheet... – Lucas Matos Aug 29 '20 at 23:04
  • 1
    @Lucas Matos Thank you for your comment. Can I ask you about the detail of `this trigger needs some time to start working properly`? – Tanaike Aug 29 '20 at 23:33
  • Not much to say. I left it alone and then when i came back it was working. Now i need to limit the popup to appear just one time in user sessioin. Do you think is that possible? – Lucas Matos Aug 29 '20 at 23:54
  • @Lucas Matos In that case, it is not the response speed of "onSelectionChange". It is to wait for activating "onSelectionChange". Is my understanding correct? About `limit the popup to appear just one time in user sessioin`, for example, how about using PropertiesService? – Tanaike Aug 30 '20 at 01:46
  • Your understanding is Correct. I dont know if it makes sense though... – Lucas Matos Aug 30 '20 at 01:59

1 Answers1

3

I created a script to test different ways to show a "pop up" in Google Apps Script. In both runtimes, only the one that use the HTML Service throw an error. The test was done using Chrome, a G Suite account, only signed in in one account.

Here is the code of the referred script:

function onSelectionChange(e) {
  var message = e.range.getA1Notation();
  switch(e.range.columnStart){
    case 1:
      alert(message);
      break;
    case 2:
      toast(message);
      break;
    case 3:
      msgBox(message);
      break;
    case 4:
      dialog(message);
      break;
    case 5:
      alertWithButton(message);
      break;
    default:
    console.info(message);
  }
}

function alert(message){
  SpreadsheetApp.getUi().alert(message);
}

function toast(message){
  SpreadsheetApp.getActiveSpreadsheet().toast(message);
}

function msgBox(message){
  Browser.msgBox(message);
}

function dialog(message){
  SpreadsheetApp.getUi().showModalDialog(
    HtmlService.createHtmlOutput(message), 
  'Alert'
  )
}

function alertWithButton(message){
  var ui = SpreadsheetApp.getUi();
  ui.alert(message, ui.ButtonSet.OK);
}
Rubén
  • 34,714
  • 9
  • 70
  • 166