//Function to identify last row of sheet filled with formulas
function getLastRowS(range){
 var rowNum = 0;
 var blank = false;
 for(var row = 0; row < range.length; row++){
   if(range[row][0] === "" && !blank){
     rowNum = row;
     blank = true;
   }else if(range[row][0] !== ""){
     blank = false;
   };
 };
 return rowNum;
};

// Function to submit the data to Database sheet
function submitData() {

 var myGooglSheet = SpreadsheetApp.getActiveSpreadsheet(); //declare a variable and set with active google sheet
 var shUserForm = myGooglSheet.getSheetByName("Data Entry Form"); //delcare a variable and set with the User Form worksheet
 var datasheet = myGooglSheet.getSheetByName("ORDER_HEADERS"); ////delcare a variable and set with the Database worksheet
 //to create the instance of the user-interface environment to use the messagebox features
 var ui = SpreadsheetApp.getUi();
  // Display a dialog box with a title, message, and "Yes" and "No" buttons. The user can also
 // close the dialog by clicking the close button in its title bar.
 var response = ui.alert("Submit", 'Do you want to submit the data?',ui.ButtonSet.YES_NO);
 // Checking the user response and proceed with clearing the form if user selects Yes
 if (response == ui.Button.NO)
 {return;//exit from this function
 }
 //Validating the entry. If validation is true then proceed with transferring the data to Database sheet
if (validateEntry()  == true) {
    var SpecificCol = datasheet.getRange("A:A").getValues(); //selecting a specific column w/o formulas
   var blankRow = getLastRowS(SpecificCol) + 1; //identify the next blank row
   datasheet.getRange(blankRow, 1).setValue(shUserForm.getRange("D3").getValue()); //Order Number
   datasheet.getRange(blankRow, 2).setValue(shUserForm.getRange("D5").getValue()); //Order Date
   datasheet.getRange(blankRow, 3).setValue(shUserForm.getRange("D7").getValue()); //Expected Date
   datasheet.getRange(blankRow, 4).setValue(shUserForm.getRange("D9").getValue()); //Order Type
   datasheet.getRange(blankRow, 5).setValue(shUserForm.getRange("D11").getValue()); //Partner Name

   ui.alert(' "New Data Saved - Emp #' + shUserForm.getRange("D3").getValue() +' "');
  //Clearnign the data from the Data Entry Form
   shUserForm.getRange("D3").clear();
   shUserForm.getRange("D5").clear();
   shUserForm.getRange("D7").clear();
   shUserForm.getRange("D9").clear();
   shUserForm.getRange("D11").clear();
}
}