I am trying to create an alert based on the answer on the prompt, prompt is working but it does not show the pop up alert message after answering the prompt. Here is my script hoping someone can help please

function onOpen() {
  var ui = SpreadsheetApp.getUi();
  ui.prompt('Please provide your shift on the box below.', ui.ButtonSet.OK);
  if (result == "morning") {
    alert("We have "   Sheet17.D3   " OT hours for TLs and "   Sheet17.D11   " for QAs!");
  }
  if (result == "dawn") {
    alert("We have "   Sheet17.D2   " OT hours for TLs and "   Sheet17.D10   " for QAs!");
  }
  if (result == "EM") {
    alert("We have "   Sheet17.D4   " OT hours for TLs and "   Sheet17.D12   " for QAs!");
  }
  if (result == "Mid") {
    alert("We have "   Sheet17.D5   " OT hours for TLs and "   Sheet17.D13   " for QAs!");
  }
  if (result == "evening") {
    alert("We have "   Sheet17.D6   " OT hours for TLs and "   Sheet17.D14   " for QAs!");
  }
  if (result == "night") {
    alert("We have "   Sheet17.D7   " OT hours for TLs and "   Sheet17.D15   " for QAs!");
  }
}

Tried different scripts but still not working

CodePudding user response：

The script has several problems

1. It's using a reserved function name that will cause that the script run every time that the spreadsheet is opened. It might work for the spreadsheet owner but it will not work for spreadsheet editors because restrictions introduced by Google several years ago that prevent that a simple on open trigger opens a user interface element.
2. The variable result has not being declared.
3. The variable Sheet17 has not being declared.
4. The variable alert has not being declared.

The following function shows how to use Ui.prompt

function myFunction(){
  const ui = SpreadsheetApp.getUi();
  const response = ui.prompt('Please provide your shift on the box below.', ui.ButtonSet.OK);
  if(response.getResponseText() === 'morning'){
     ui.alert('Morning message')
  }
}

Note: The code should be added on a script bounded to a spreadsheet.

To learn about how to get values from a spreadsheet please read https://developers.google.com/apps-script/guides/sheets

References

• https://developers.google.com/apps-script/reference/base/prompt-response

CodePudding user response：

Try it this way:

function onMyOpen() {
  const ui = SpreadsheetApp.getUi();
  const result = ui.prompt('Please provide your shift on the box below.', ui.ButtonSet.OK).getResponseText();
  const ss = SpreadsheetApp.getActive();
  const sh17 = ss.getSheetByName("Sheet17");
  const [D2,D3,D4,D5,D6,D7] = sh17.getRange("D2:D7").getValues().flat();
  const [D10,D11,D12,D13,D14,D15] = sh17.getRange("D10:D15").getValues().flat(); 
  if (result == "morning") {
    alert("We have "   D3   " OT hours for TLs and "   D11   " for QAs!");
  }
  if (result == "dawn") {
    alert("We have "   D2   " OT hours for TLs and "   D10   " for QAs!");
  }
  if (result == "EM") {
    alert("We have "   D4   " OT hours for TLs and "   D12   " for QAs!");
  }
  if (result == "Mid") {
    alert("We have "   D5   " OT hours for TLs and "   D13   " for QAs!");
  }
  if (result == "evening") {
    alert("We have "   D6   " OT hours for TLs and "   D14   " for QAs!");
  }
  if (result == "night") {
    alert("We have "   D7   " OT hours for TLs and "   D15   " for QAs!");
  }
}

Run the following function to create installable trigger

function createOnMyOpenTrigger() {
  if(ScriptApp.getProjectTriggers().filter(t => t.getHandlerFunction() == "onMyOpen").length == 0) {
    ScriptApp.newTrigger("onMyOpen").forSpreadsheet(ss).onOpen().create();
  }
}