Data Entry Form - Google Apps Script Code

// Function to Clear the User Form

function clearForm()
{
var myGoogleSheet= SpreadsheetApp.getActiveSpreadsheet(); //declare
a variable and set with active google sheet
var shUserForm = myGoogleSheet.getSheetByName("User Form");
//declare a variable and set with the User Form worksheet

//to create the instance of the user-interface environment to use

the alert 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("Reset Confirmation", 'Do you want to reset
this form?',ui.ButtonSet.YES_NO);

// Checking the user response and proceed with clearing the form if
user selects Yes
if (response == ui.Button.YES)
{

shUserForm.getRange("C4").clear(); //Search Field

shUserForm.getRange("C7").clear();// Employeey ID
shUserForm.getRange("C9").clear(); // Employee Name
shUserForm.getRange("C11").clear(); // Gender
shUserForm.getRange("C13").clear(); // Email ID
shUserForm.getRange("C15").clear(); //Department
shUserForm.getRange("C17").clear();//Address

//Assigning white as default background color

shUserForm.getRange("C4").setBackground('#FFFFFF');
shUserForm.getRange("C7").setBackground('#FFFFFF');
shUserForm.getRange("C9").setBackground('#FFFFFF');
shUserForm.getRange("C11").setBackground('#FFFFFF');
shUserForm.getRange("C13").setBackground('#FFFFFF');
shUserForm.getRange("C15").setBackground('#FFFFFF');
shUserForm.getRange("C17").setBackground('#FFFFFF');
return true ;

}
}
// 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("User Form"); //delcare

a variable and set with the User Form worksheet

var datasheet = myGooglSheet.getSheetByName("Database");

////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 blankRow=datasheet.getLastRow()+1; //identify the next blank

row

datasheet.getRange(blankRow,
1).setValue(shUserForm.getRange("C7").getValue()); //Employee ID
datasheet.getRange(blankRow,
2).setValue(shUserForm.getRange("C9").getValue()); //Employee Name
datasheet.getRange(blankRow,
3).setValue(shUserForm.getRange("C11").getValue()); //Gender
datasheet.getRange(blankRow,
4).setValue(shUserForm.getRange("C13").getValue()); // Email ID
datasheet.getRange(blankRow,
5).setValue(shUserForm.getRange("C15").getValue()); //Department
datasheet.getRange(blankRow,
6).setValue(shUserForm.getRange("C17").getValue());// Address

// date function to update the current date and time as submittted

on
datasheet.getRange(blankRow, 7).setValue(new
Date()).setNumberFormat('yyyy-mm-dd h:mm'); //Submitted On

//get the email address of the person running the script and update
as Submitted By
datasheet.getRange(blankRow,
8).setValue(Session.getActiveUser().getEmail()); //Submitted By

ui.alert(' "New Data Saved - Emp #' +

shUserForm.getRange("C7").getValue() +' "');

//Clearnign the data from the Data Entry Form

shUserForm.getRange("C7").clear();
shUserForm.getRange("C9").clear();
shUserForm.getRange("C11").clear();
shUserForm.getRange("C13").clear();
shUserForm.getRange("C15").clear();
shUserForm.getRange("C17").clear();

}
}

//Function to Search the record

function searchRecord() {

var myGooglSheet= SpreadsheetApp.getActiveSpreadsheet(); //declare

a variable and set with active google sheet
var shUserForm= myGooglSheet.getSheetByName("User Form"); //delcare
a variable and set with the User Form worksheet
var datasheet = myGooglSheet.getSheetByName("Database");
////delcare a variable and set with the Database worksheet
var str = shUserForm.getRange("C4").getValue();
var values = datasheet.getDataRange().getValues(); //getting the
entire values from the used range and assigning it to values
variable
var valuesFound=false; //variable to store boolean value

for (var i = 0; i < values.length; i++)

{
var rowValue = values[i]; //declaraing a variable and storing the
value

//checking the first value of the record is equal to search item

if (rowValue[0] == str) {

shUserForm.getRange("C7").setValue(rowValue[0]) ;
shUserForm.getRange("C9").setValue(rowValue[1]);
shUserForm.getRange("C11").setValue(rowValue[2]);
shUserForm.getRange("C13").setValue(rowValue[3]);
shUserForm.getRange("C15").setValue(rowValue[4]);
shUserForm.getRange("C17").setValue(rowValue[5]);
return; //come out from the search function

}
}

if(valuesFound==false){
//to create the instance of the user-interface environment to use
the messagebox features
var ui = SpreadsheetApp.getUi();
ui.alert("No record found!");
}

//Function to delete the record

function deleteRow() {

var myGooglSheet= SpreadsheetApp.getActiveSpreadsheet(); //declare

a variable and set with active google sheet
var shUserForm= myGooglSheet.getSheetByName("User Form"); //delcare
a variable and set with the User Form worksheet
var datasheet = myGooglSheet.getSheetByName("Database");
////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 delete the
record?',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
}

var str = shUserForm.getRange("C4").getValue();

var values = datasheet.getDataRange().getValues(); //getting the
entire values from the used range and assigning it to values
variable

var valuesFound=false; //variable to store boolean value to

validate whether values found or not

for (var i = 0; i < values.length; i++)

{
var rowValue = values[i]; //declaraing a variable and storing the
value

//checking the first value of the record is equal to search item

if (rowValue[0] == str) {

var iRow = i+1; //identify the row number

datasheet.deleteRow(iRow) ; //deleting the row

//message to confirm the action

ui.alert(' "Record deleted for Emp #' +
shUserForm.getRange("C4").getValue() +' "');

//Clearing the user form

shUserForm.getRange("C4").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() ;

valuesFound=true;
return; //come out from the search function
}
}

if(valuesFound==false){
//to create the instance of the user-interface environment to use
the messagebox features
var ui = SpreadsheetApp.getUi();
ui.alert("No record found!");
}

//Function to edit the record

function editRecord() {

var myGooglSheet= SpreadsheetApp.getActiveSpreadsheet(); //declare

a variable and set with active google sheet
var shUserForm= myGooglSheet.getSheetByName("User Form"); //delcare
a variable and set with the User Form worksheet
var datasheet = myGooglSheet.getSheetByName("Database");
////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 edit 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
}

var str = shUserForm.getRange("C4").getValue();

var values = datasheet.getDataRange().getValues(); //getting the
entire values from the used range and assigning it to values
variable

var valuesFound=false; //variable to store boolean value to

validate whether values found or not

for (var i = 0; i < values.length; i++)

{
var rowValue = values[i]; //declaraing a variable and storing the
value

//checking the first value of the record is equal to search item

if (rowValue[0] == str) {

var iRow = i+1; //identify the row number

datasheet.getRange(iRow,
1).setValue(shUserForm.getRange("C7").getValue()); //Employee ID
datasheet.getRange(iRow,
2).setValue(shUserForm.getRange("C9").getValue()); //Employee Name
datasheet.getRange(iRow,
3).setValue(shUserForm.getRange("C11").getValue()); //Gender
datasheet.getRange(iRow,
4).setValue(shUserForm.getRange("C13").getValue()); // Email ID
datasheet.getRange(iRow,
5).setValue(shUserForm.getRange("C15").getValue()); //Department
datasheet.getRange(iRow,
6).setValue(shUserForm.getRange("C17").getValue());// Address

// date function to update the current date and time as submittted

on
datasheet.getRange(iRow, 7).setValue(new
Date()).setNumberFormat('yyyy-mm-dd h:mm'); //Submitted On

//get the email address of the person running the script and update
as Submitted By
datasheet.getRange(iRow,
8).setValue(Session.getActiveUser().getEmail()); //Submitted By

ui.alert(' "Data updated for - Emp #' +

shUserForm.getRange("C7").getValue() +' "');
//Clearnign the data from the Data Entry Form

shUserForm.getRange("C4").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();

valuesFound=true;
return; //come out from the search function
}
}

if(valuesFound==false){
//to create the instance of the user-interface environment to use
the messagebox features
var ui = SpreadsheetApp.getUi();
ui.alert("No record found!");
}