Web Analytics Made Easy -
StatCounter google sheets - CodingForum

Announcement

Collapse
No announcement yet.

google sheets

Collapse
X
 
  • Filter
  • Time
  • Show
Clear All
new posts

  • google sheets

    hi basically i have created a database in sheets
    but thing is i do not know where i am going wrong

    i put all the info in and save but the persons name seems to keep going into the email section
    and not the first name section
    the search won't for some reason work? here's my sheet

    yes it's open to anyone to edit

    if anyone could help that would be great!
    the email isn't working either


    also here's the code​

    // Clear Form
    function ClearCell() {
    var ss =SpreadsheetApp.getActiveSpreadsheet();
    var formS=ss.getSheetByName('Form'); //Form Sheet
    var rangesToClear=["B6","B8","B10", "B12", "D6","D8","D10"];
    for (var i=0; i<rangesToClear.length; i++) {
    formS.getRange(rangesToClear[i]).clearContent();


    }
    }
    //------------------------------------------------------------------
    //input values
    function SubmitData() {
    var ss =SpreadsheetApp.getActiveSpreadsheet();
    var formS =ss.getSheetByName('Form'); //Data entry Sheet
    var dataS=ss.getSheetByName('Data'); //Data Sheet

    var values = [[formS.getRange('B6').getValue(),
    formS.getRange('B8').getValue(),
    formS.getRange('B10').getValue(),
    formS.getRange('B12').getValue(),
    formS.getRange('D6').getValue(),
    formS.getRange('D8').getValue(),
    formS.getRange('D10').getValue()]];
    dataS.getRange(dataS.getLastRow()+1,1,1,7).setValues(values);
    ClearCell();
    }
    //---------------------------------------------------------------------
    var SEARCH_COL_IDX= 0;
    function SEARCH() {





    var ss =SpreadsheetApp.getActiveSpreadsheet();
    var formS=ss.getSheetByName('Form'); //Form Sheet

    var str = formS.getRange('D3').getValue();
    var values=ss.getSheetByName('Data').getDataRange().getValues();
    for (var i =0; i <values.getlength; i++) {

    var row= values[i];
    if (row[SEARCH_COL_IDX] ==str) {

    formS.getRange('B6').setValue(),
    formS.getRange('B8').setValue(),
    formS.getRange('B10').setValue(),
    formS.getRange('B12').setValue(),
    formS.getRange('D6').setValue(),
    formS.getRange('D8').setValue(),
    formS.getRange('D10').setValue()



    } }}
    //----------------------------------------------------------------------

    function Update() {
    var ss =SpreadsheetApp.getActiveSpreadsheet();
    var formS=ss.getSheetByName('Form'); //Form Sheet
    var dataS=ss.getSheetByName('Data'); //Data Sheet

    var str = formS.getRange('D3').getValue();
    var values=ss.getSheetByName('Data').getDataRange().getValues();
    for (var i =0; i <values.getlength; i++) {
    var row= values[i];
    if (row[SEARCH_COL_IDX] ==str) {
    var INT_R = i+1

    var Values1 = [[formS.getRange('B6').getValue(),
    formS.getRange('B8').getValue(),
    formS.getRange('B10').getValue(),
    formS.getRange('B12').getValue(),
    formS.getRange('D6').getValue(),
    formS.getRange('D8').getValue(),
    formS.getRange('D10').getValue()]];

    dataS.getRange(INT_R, 1 ,1 ,1, 7).setValues(Values1);
    SpreadsheetApp.getUi().alert( 'Data Updated');

    } }}
    //-------------------------------------------------------------------------

    function Delete() {
    var ss =SpreadsheetApp.getActiveSpreadsheet();
    var formS=ss.getSheetByName('Form'); //Form Sheet
    var dataS=ss.getSheetByName('Data'); //Data Sheet

    var ui = SpreadsheetApp.getUi();
    var response = ui.alert('Delete ?' ,ui.ButtonSet.YES_NO);

    // Process the user's response
    if (response == ui.Button.YES) {

    var str = formS.getRange('D3').getValue();
    var values =ss.getSheetByName('Data').getDataRange().getValues(); //Data Sheet
    for (var i =0; i <values.getlength; i++) {
    var row = values[i];
    if (row[SEARCH_COL_IDX] ==str) {
    var INT_R = i+1

    dataS.deleteRow(INT_R);
    ClearCell();
    }}} }​
Working...
X