// function to validate the entry made by user in user form
function validateEntry(){
// declare a variable and referernece of active google sheet
var myGoogleSheet = SpreadsheetApp.getActiveSpreadsheet();
var shUserForm = myGoogleSheet.getSheetByName("User Form");
// to create the instance of the user interface to show the alert
var ui = SpreadsheetApp.getUi();
shUserForm.getRange("C3").setBackground('#FFFFF');
shUserForm.getRange("C5").setBackground('#FFFFF');
shUserForm.getRange("C7").setBackground('#FFFFF');
shUserForm.getRange("C9").setBackground('#FFFFF');
shUserForm.getRange("C11").setBackground('#FFFFF');
shUserForm.getRange("C13").setBackground('#FFFFF');
shUserForm.getRange("C15").setBackground('#FFFFF');
shUserForm.getRange("C17").setBackground('#FFFFF');
shUserForm.getRange("C19").setBackground('#FFFFF');
shUserForm.getRange("C21").setBackground('#FFFFF');
// validating Division
if(shUserForm.getRange("C3").isBlank()==true){
ui.alert("Please enter Division");
shUserForm.getRange("C3").activate();
shUserForm.getRange("C3").setBackground('#FF0000');
return false;
}
// validating Section
if(shUserForm.getRange("C5").isBlank()==true){
ui.alert("Please enter Section");
shUserForm.getRange("C5").activate();
shUserForm.getRange("C5").setBackground('#FF0000');
return false;
}
// validating Station
if(shUserForm.getRange("C7").isBlank()==true){
ui.alert("Please enter Station");
shUserForm.getRange("C7").activate();
shUserForm.getRange("C7").setBackground('#FF0000');
return false;
}
// validating inspected by
if(shUserForm.getRange("C9").isBlank()==true){
ui.alert("Please enter Officer Name");
shUserForm.getRange("C9").activate();
shUserForm.getRange("C9").setBackground('#FF0000');
return false;
}
// validating date
if(shUserForm.getRange("C11").isBlank()==true){
ui.alert("Please enter Date");
shUserForm.getRange("C11").activate();
shUserForm.getRange("C11").setBackground('#FF0000');
return false;
}
// validating major deficiencies 1
if(shUserForm.getRange("C13").isBlank()==true){
ui.alert("Please enter deficiency 1");
shUserForm.getRange("C13").activate();
shUserForm.getRange("C13").setBackground('#FF0000');
return false;
}
/* validating major deficiencies 2
if(shUserForm.getRange("C15").isBlank()==true){
ui.alert("Please enter atlease deficiency 2");
shUserForm.getRange("C15").activate();
shUserForm.getRange("C15").setBackground('#FF0000');
return false;
}
// validating major deficiencies 3
if(shUserForm.getRange("C17").isBlank()==true){
ui.alert("Please enter deficiency 3");
shUserForm.getRange("C17").activate();
shUserForm.getRange("C17").setBackground('#FF0000');
return false;
}
// validating major deficiencies 4
if(shUserForm.getRange("C19").isBlank()==true){
ui.alert("Please enter deficiency 4");
shUserForm.getRange("C19").activate();
shUserForm.getRange("C19").setBackground('#FF0000');
return false;
}
// validating major deficiencies 5
if(shUserForm.getRange("C21").isBlank()==true){
ui.alert("Please enter deficiency 5");
shUserForm.getRange("C21").activate();
shUserForm.getRange("C21").setBackground('#FF0000');
return false;
}*/
return true;
}
// function to submit the data
function submitData(){
// declare a variable and set the reference of active google sheet
var myGoogleSheet = SpreadsheetApp.getActiveSpreadsheet();
var shUserForm = myGoogleSheet.getSheetByName("User Form");
var datasheet = myGoogleSheet.getSheetByName("Database");
// to create the instance of ther user-interface enviroment to user the alert feature
var ui = SpreadsheetApp.getUi();
var response = ui.alert("Submit", "Do you want to submit the data?", ui.ButtonSet.YES_NO);
// checking the user response
if(response == ui.Button.No){
alert.ui("no data saved");
return;// to exit from this function
}
if(validateEntry() == true){
var blankRow = datasheet.getLastRow()+1; // identify the next blank row
//code to update the database sheet
//For division
datasheet.getRange(blankRow,1).setValue(shUserForm.getRange("C3").getValue());
//For section
datasheet.getRange(blankRow,2).setValue(shUserForm.getRange("C5").getValue());
//For station
datasheet.getRange(blankRow,3).setValue(shUserForm.getRange("C7").getValue());
//For officer name
datasheet.getRange(blankRow,4).setValue(shUserForm.getRange("C9").getValue());
//For date
datasheet.getRange(blankRow,5).setValue(shUserForm.getRange("C11").getValue());
//For major deficiency 1
datasheet.getRange(blankRow,6).setValue(shUserForm.getRange("C13").getValue());
//For major deficiency 2
datasheet.getRange(blankRow,7).setValue(shUserForm.getRange("C15").getValue());
//For major deficiency 3
datasheet.getRange(blankRow,8).setValue(shUserForm.getRange("C17").getValue());
//For major deficiency 4
datasheet.getRange(blankRow,9).setValue(shUserForm.getRange("C19").getValue());
//For major deficiency 5
datasheet.getRange(blankRow,10).setValue(shUserForm.getRange("C21").getValue());
ui.alert("Entry saved successfully");
shUserForm.getRange("C3").clear();
shUserForm.getRange("C5").clear();
shUserForm.getRange("C7").clear();
shUserForm.getRange("C9").clear();
shUserForm.getRange("C11").clear();
shUserForm.getRange("C13").clear();
shUserForm.getRange("C15").clear();
shUserForm.getRange("C17").clear();
shUserForm.getRange("C19").clear();
shUserForm.getRange("C21").clear();
}
}