hi all please repost in other sections if what i'm asking isn't for this section
basically i'm trying to create a database i have the form completed
and now i have coded it, but when i submit the data into from the user form
into the database, what i have submitted don't go into the database?
here is what i have accomplished so far https://docs.google.com/spreadsheets/d/1monyA9RJO2jizwM1_WJZWWvzLNcg_D6c-raV7XhhzuI/edit?usp=sharing
and here is the code
basically i'm trying to create a database i have the form completed
and now i have coded it, but when i submit the data into from the user form
into the database, what i have submitted don't go into the database?
here is what i have accomplished so far https://docs.google.com/spreadsheets/d/1monyA9RJO2jizwM1_WJZWWvzLNcg_D6c-raV7XhhzuI/edit?usp=sharing
and here is the code
Code:
//Function to validate the entry made by user in User Form
function validateEntry(){
//declare a variable and set the reference of active google sheet
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 ui = SpreadsheetApp.getUi(); //to create the instance of the user interface to show the alert
//Assigning white as default background color
shUserForm.getRange("C6").setBackground('#FFFFFF');
shUserForm.getRange("C8").setBackground('#FFFFFF');
shUserForm.getRange("C10").setBackground('#FFFFFF');
shUserForm.getRange("C12").setBackground('#FFFFFF');
shUserForm.getRange("C14").setBackground('#FFFFFF');
shUserForm.getRange("C16").setBackground('#FFFFFF');
//Valadating New DJ/Presenter First Name
if(shUserForm.getRange("C4").isBlank()==true){
ui.alert("Please enter New DJ/Presenter First Name.");
shUserForm.getRange("C4").activate();
shUserForm.getRange("C4").setBackground('#FFCCCB');
return false;
}
//Valadating Your DJ NAME!
else if(shUserForm.getRange("C6").isBlank()==true){
ui.alert("enter Your DJ Name.");
shUserForm.getRange("C6").activate();
shUserForm.getRange("C6").setBackground('#FFCCCB');
return false;
}
//Valadating Your DJ D.O.B
else if(shUserForm.getRange("C8").isBlank()==true){
ui.alert("Please enter Your DJ D.O.B.");
shUserForm.getRange("C8").activate();
shUserForm.getRange("C8").setBackground('#FFCCCB');
return false;
}
//Valadating New DJ ID
if(shUserForm.getRange("C10").isBlank()==true){
ui.alert("Please enter New DJ ID.");
shUserForm.getRange("C10").activate();
shUserForm.getRange("C10").setBackground('#FF0000');
return false;
}
//Valadating Country
if(shUserForm.getRange("C12").isBlank()==true){
ui.alert("Please enter DJ's Country.");
shUserForm.getRange("C12").activate();
shUserForm.getRange("C12").setBackground('#FF0000');
return false;
}
//Valadating Date Of Joining Us
if(shUserForm.getRange("C14").isBlank()==true){
ui.alert("When Did You First Join Coolvibes.");
shUserForm.getRange("C14").activate();
shUserForm.getRange("C14").setBackground('#FF0000');
return false;
}
//Validating Department
else if(shUserForm.getRange("C16").isBlank()==true){
ui.alert("Please select Department from the drop-down.");
shUserForm.getRange("C16").activate();
shUserForm.getRange("C16").setBackground('#FF0000');
return false;
}
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
var ui=SpreadsheetApp.getUi();
var response=ui.alert("Submit", "do you want to submit the data?", ui.ButtonSet.YES_NO);
//checking the user responce
if(responce==ui.Button.NO){
return;//to exit from this function
}
datasheet.getRange(blankRow, 1).setValue(shUserForm.getRange("A1").getValue()); //Staff/DJ Name
datasheet.getRange(blankRow, 2).setValue(shUserForm.getRange("B1").getValue()); //Gender
datasheet.getRange(blankRow, 3).setValue(shUserForm.getRange("C1").getValue()); //D.O.B
datasheet.getRange(blankRow, 4).setValue(shUserForm.getRange("D1").getValue()); //New DJ/Presenter First Name
// date function to update the current date and time as submittted on
datasheet.getRange(blankRow, 5).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, 6).setValue(Session.getActiveUser().getEmail()); //Submitted By
ui.alert(' "New Data Saved - DJID #' + shUserForm.getRange(A1).getValue() +' "');
shUserForm.getRange("C4").clear();
shUserForm.getRange("C6").clear();
shUserForm.getRange("C8").clear();
shUserForm.getRange("C10").clear();
shUserForm.getRange("C12").clear();
shUserForm.getRange("C14").clear();
shUserForm.getRange("C16").clear();
}
}
wondering if anyone could help me out here