Google Script for Data Entry Form in Google Spreadsheet

// 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();

}

}