Google sheets


Joined
Jun 14, 2018
Messages
46
Reaction score
0
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

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
 
Ad

Advertisements


Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Google sheets 2

Top