Google Script get values from UserForm Sheet to Data Sheet. even if I insert or delete row in UserFo-CodePudding

I have a situation, in which I may insert or delete rows. so I could not specify the exact cell range

Please help with this code, What I am doing wrong (beginner), I have tried the below code. I am trying to achieve this by getnamedranges (correct me if wrong)

https://docs.google.com/spreadsheets/d/1NY_ckzEWxU7DCGro5tTqzpiOi6iG5PAQFxpZg0OKodY/edit?usp=sharing

function submitData() {
     
  var myGooglSheet= SpreadsheetApp.getActiveSpreadsheet(); //declare a variable and set with active google sheet 
 
  var shUserForm= myGooglSheet.getSheetByName("UserForm"); //delcare a variable and set with the User Form worksheet
 
  var datasheet = myGooglSheet.getSheetByName("Datasheet"); ////delcare a variable and set with the Datasheet 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 Datasheet sheet
  // if (validateEntry()==true)
 {
  
    var blankRow=datasheet.getLastRow() 1; //identify the next blank row
 
    datasheet.getRange(blankRow, 1).setValue(shUserForm.getRange("G4").getValue()); //Date
    datasheet.getRange(blankRow, 2).setValue(shUserForm.getRange("J4").getValue()); //UserForm Number
    datasheet.getRange(blankRow, 3).setValue(shUserForm.getRange("C3").getValue()); //Student Name
    datasheet.getRange(blankRow, 4).setValue(shUserForm.getRange("C6").getValue()); //ID
    datasheet.getRange(blankRow, 5).setValue(shUserForm.getRange("H8").getValue()); //Project Name
    datasheet.getRange(blankRow, 6).setValue(shUserForm.getRange("G5").getValue()); //Batch Number 
    datasheet.getRange(blankRow, 7).setValue(shUserForm.getRange("cellname1").getValue()); // Get cellname1
    datasheet.getRange(blankRow, 8).setValue(shUserForm.getNamedRanges("cellname2").getValue());//Get cellname2
    datasheet.getRange(blankRow, 9).setValue(shUserForm.getNamedRanges("cellname3").getValue());// Get cellname3
    datasheet.getRange(blankRow, 10).setValue(shUserForm.getNamedRanges("cellname4").getValue());// Get cellname4
    datasheet.getRange(blankRow, 11).setValue(shUserForm.getRange("C9").getValue());// Summary
   
    // date function to update the current date and time as submittted on
    datasheet.getRange(blankRow, 12).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, 13).setValue(Session.getActiveUser().getEmail()); //Submitted By
    
    ui.alert(' "New Data Saved - Emp #'   shUserForm.getRange("C3").getValue()  ' "');

[![sample1][1]][1] [1]: https://i.stack.imgur.com/UxCdT.png

CodePudding user response：

Note that you have a difference bteween 2 names : TotalPresentt vs TotalPresent !

You can use directly shUserForm.getRange("TotalPresentt").getValue()

Try this

// Function to submit the data to DataSheet sheet
function submitData() {

  var myGooglSheet = SpreadsheetApp.getActiveSpreadsheet(); //declare a variable and set with active google sheet 
  var shUserForm = myGooglSheet.getSheetByName("UserForm"); //delcare a variable and set with the User Form worksheet
  var datasheet = myGooglSheet.getSheetByName("DataSheet"); ////delcare a variable and set with the DataSheet worksheet

  var ui = SpreadsheetApp.getUi();
  var response = ui.alert("Submit", 'Do you want to submit the data?', ui.ButtonSet.YES_NO);
  if (response == ui.Button.NO) {
    return;
  }

  var blankRow = datasheet.getLastRow()   1; //identify the next blank row

  datasheet.getRange(blankRow, 1).setValue(shUserForm.getRange("C3").getValue()); //Date
  datasheet.getRange(blankRow, 2).setValue(shUserForm.getRange("C4").getValue()); //UserForm Number
  datasheet.getRange(blankRow, 3).setValue(shUserForm.getRange("C5").getValue()); //Student Name
  datasheet.getRange(blankRow, 4).setValue(shUserForm.getRange("C6").getValue()); //ID
  datasheet.getRange(blankRow, 5).setValue(shUserForm.getRange("C7").getValue()); //Project
  datasheet.getRange(blankRow, 6).setValue(shUserForm.getRange("C8").getValue()); //Group Name
  datasheet.getRange(blankRow, 8).setValue(shUserForm.getRange("TotalPresentt").getValue());// TotalPresent
  datasheet.getRange(blankRow, 8).setValue(shUserForm.getRange("SoundRoomDay").getValue());// SoundRoomDAy
  datasheet.getRange(blankRow, 9).setValue(shUserForm.getRange("GroupDay").getValue());// GroupDay
  datasheet.getRange(blankRow, 10).setValue(shUserForm.getRange("TotalDays").getValue());// TotalDays

  datasheet.getRange(blankRow, 12).setValue(new Date()).setNumberFormat('yyyy-mm-dd h:mm'); //Submitted On
  datasheet.getRange(blankRow, 13).setValue(Session.getActiveUser().getEmail()); //Submitted By

  ui.alert(' "New Data Saved - StudentID #'   shUserForm.getRange("C5").getValue()   ' "');

}