Display Google sheet results in Google form

Triggers for forms which are currently supported are onOpen and onFormSubmit

I created a script and it is working but the issue is the trigger.

• onOpen - when the form is opened for edit (not applicable when filling up form)

• onFormSubmit - when the form is already submitted (can't do dialogs/alert due to this error Error: Cannot call FormApp.getUi() from this context)

So what I did is when opening/refreshing the page where you edit your form, I trigger the function below:

function showPrompt() {
  var ui = FormApp.getUi();

  var result = ui.prompt(
      'Please enter email:',
      ui.ButtonSet.OK_CANCEL);

  // Process the user's response.
  var button = result.getSelectedButton();
  var email = result.getResponseText();
  if (button == ui.Button.OK) {
    // User clicked "OK".
    var ssID = '1B2zmo1IIoE9OdgEZQ36gJN4AQ_WMPoWtW6UrvEt25gc';
    var sheetName = 'Sheet1';
    var sheet = SpreadsheetApp.openById(ssID).getSheetByName(sheetName);
    var lastRow = sheet.getDataRange().getLastRow();
    var dataEmail = sheet.getRange('B2:B' + lastRow).getValues().flat();
    var found = false;
    dataEmail.forEach(function(item, index){
      if (email == item && sheet.getRange('C' + (index + 2)).getDisplayValue() == 'Yes') {
        found = true;
        ui.alert(email + ' is attending in ' + sheet.getRange('A' + (index + 2)).getDisplayValue());
      }
      if (!found && index == dataEmail.length - 1){
        ui.alert(email + ' is not attending');
      }
    });
  } 
}

Make sure to add the function under triggers:

When the form is opened as edit:

Sample Data:

Outputs:

If you prefer using onFormSubmit, you can't do alerts/dialogs but you do other things as well. If it's fine, you can try sending email instead.