How do I create only ONE mandatory field in my data entry form for Google Sheet App Script button?

user_16711677 0
Viewed 272

I have created a data entry form on google sheets that has a submit button that triggers a script. I want to ensure that only one field is not left blank when someone submits the form through the script.

I want the script to check if the field (C5) is blank and return a SpreadsheetApp.getUi().alert rejecting the submission as follows:

SpreadsheetApp.getUi().alert('Please enter mandatory field in C5.');

I have tried the below function's code that ensures that ALL fields are not blank when someone submits the form through the script however I only want one field (C5) to be mandatory not all of them. Can someone please help? :)

function submitData() {
  var ss        = SpreadsheetApp.getActiveSpreadsheet();
  var formSS    = ss.getSheetByName("Form"); //Form Sheet
  var datasheet = ss.getSheetByName("OrderInput"); 
   
  //Input Values
  var values = [[formSS.getRange("C5").getValue(),
                 formSS.getRange("C7").getValue(),
                 formSS.getRange("C9").getValue(),
                 formSS.getRange("C11").getValue(),
                 formSS.getRange("C13").getValue()]];

  //Check Blank Values
  for (i = 0; i < values[0].length; i++) {
    if (values[0][i] == '') {
      var blankExists = true;
      break;
    }
  }

  if (blankExists) {
    SpreadsheetApp.getUi().alert('Please enter all mandatory fields.');
  }
  else {
    datasheet.getRange(datasheet.getLastRow()+1, 2, 1, 5).setValues(values);
    SpreadsheetApp.getUi().alert('Request successfully submitted!');
    formSS.getRange("C5").clear();
    formSS.getRange("C7").clear();
    formSS.getRange("C9").clear();
    formSS.getRange("C11").clear();
    formSS.getRange("C13").clear();
  }
}

google-apps-script spreadsheet cell google-sheets
2 Answers
  //Check Blank Values
  /*
  for (i = 0; i < values[0].length; i++) {
    if (values[0][i] == '') {
      var blankExists = true;
      break;
    }
  }
  */
  var blankExists = values[0][0] == '';
user_161251390

The reason your code is not working as expected is mainly because you are checking the entire values array for the empty values.

In order to fix this you will have to replace the for loop with a simple if:

if (values[0] == '')
   SpreadsheetApp.getUi().alert('Please enter the required value in C5.');

Also, since you are using a one dimensional array, I suggest you change your values variable to this:

 var values = [formSS.getRange("C5").getValue(),
                 formSS.getRange("C7").getValue(),
                 formSS.getRange("C9").getValue(),
                 formSS.getRange("C11").getValue(),
                 formSS.getRange("C13").getValue()];
user_117720350
Related