How to show a confirmation message while selecting a value of a dropdown list in Google Sheets?

Question

I am selecting values from two dropdown lists in Google Sheets. In cell A4 I select subject code like IT101, Math301, PHY105 and son on. And in cell B4 there are 'No Class, Start 1-Period and Start 2-Period' in the dropdown list.

What I want is, in A4 I will select my subject code and then when I will select any item from the dropdown list in B4 (B4 cell value is linked with onEdit Trigger), I want to see a confirmation message like "Confirm your subject..." If I press OK, it will execute the functions below like refreshSheet() and onePeriod(). But if I cancel it, it will do nothing. In this code, the issues is, it is showing message but the code is executed automatically no matter whether I am pressing Ok or Cancel button. Please advise.

function onEdit(e){  
  if (e.range.getA1Notation() === 'B4' && e.range.getValue() === "Start 1-Period") { 
    var ui = SpreadsheetApp.getUi();
    ui.alert("Confirm Your Subject Details!!", ui.ButtonSet.OK_CANCEL);
    if (button == ui.Button.OK){
      refreshSheet();
      onePeriod();
    }
  }  
  if (e.range.getA1Notation() === 'B4' && e.range.getValue() === "Start 2-Period") {
      var ui = SpreadsheetApp.getUi();
    ui.alert("Confirm Your Subject Details!!", ui.ButtonSet.OK_CANCEL);
    if (button == ui.Button.OK){
      refreshSheet();
      onePeriod();
    }
  }  
}

ziganotschka · Accepted Answer · 2020-07-29T12:17:26.963

0

You need to implement an additional `if` statement running the rest of the code only if `Button.OK` was clicked:

    var button = ui.alert("Confirm Your Subject!!", ui.ButtonSet.OK_CANCEL);    
    if (button == ui.Button.OK) {
      refreshSheet();
      onePeriod();
    }

edited Jul 29 '20 at 12:17

answered Jul 29 '20 at 11:13

ziganotschka

25,866
2
16
33

Still the same result. Please check my revised code at the original post. – Aktaruzzaman Liton Jul 29 '20 at 11:50
Excuse me, I just saw that I skipped the variable declaration - you need it for the `if` statement. – ziganotschka Jul 29 '20 at 12:19
It is working in Computer browser but not in Google Sheets App. – Aktaruzzaman Liton Jul 29 '20 at 15:28
What do you mean? Try: `if (ui.alert("Confirm Your Subject Details!!", ui.ButtonSet.OK_CANCEL) == ui.Button.OK){ ui.alert("You clicked ok"); refreshSheet(); onePeriod(); } else{ ui.alert("You clicked cancel"); }` If you can see the alerts but your functions do not run correctly, then the problem must be inside those functions. – ziganotschka Jul 29 '20 at 15:55
By `Google Sheets App` do you mean the mobile App on your phone? – ziganotschka Jul 29 '20 at 15:56
Yes, mobile app – Aktaruzzaman Liton Jul 29 '20 at 15:57
Oh, I see. Does `SpreadsheetApp.getUi()` work for you on android at all? – ziganotschka Jul 29 '20 at 16:06
No, it does not work. People often wants to work from android app. That's the problem. Is there any other solution that works both in computer and mobile. – Aktaruzzaman Liton Jul 29 '20 at 16:08
Have a look at [this](https://stackoverflow.com/questions/33373826/executing-google-apps-script-functions-from-mobile-app), [this](https://stackoverflow.com/questions/57840757/button-click-is-only-working-on-windows-not-working-on-android-mobile-sheet/57842475#57842475) and [this](https://webapps.stackexchange.com/questions/87346/add-a-script-trigger-to-google-sheet-that-will-work-in-android-mobile-app). Unfortunately the approach above is only for use on computers, not mobile phones. – ziganotschka Jul 29 '20 at 16:10
1

Thanks anyway. I realised that it does not at mobile apps. – Aktaruzzaman Liton Jul 29 '20 at 16:12

How to show a confirmation message while selecting a value of a dropdown list in Google Sheets?

1 Answers1

You need to implement an additional if statement running the rest of the code only if Button.OK was clicked:

You need to implement an additional `if` statement running the rest of the code only if `Button.OK` was clicked: