In a google apps script, google sheet data entry form, how would I select a different tab for data o-CodePudding

I have a current google sheet that I would like to use a data entry front end tab built in Google apps script to populate separate tabs in the sheet based on some of the the entries made in the data entry form/tab.

i.e., if "DeviceID_1" is selected in the first entry (right now from a pulldown menu) and a specific month is selected in the second entry(also from a pulldown menu), when the data is validated, then it sends it to the tab corresponding to that specific already named and existing tab, "DeviceID_1-January".

I have moved through tutorials on building the data entry form and it outputs to a single database tab called "database", and works as expected. I'm guessing, what I'd like it to do is not trivial in implementation. This is, I'd guess, real programming and development of the function to validate the input and returning the correct output, and I have the time to learn how to do this the right way, if directed to resources, but do not currently, know how to begin. Well, that isn't entirely true. I know I will need to validate the input data, and then create a function that based on the input data, if (shUserForm.getRange("C6").is????()==true), will submit the data in the form by getSheetByName("DeviceID_1-Jan");

So far, I think the relevant current code is here:

 if (shUserForm.getRange("C4").isBlank()==true){

     ui.alert("Please enter system ID #.");
     shUserForm.getRange("C4").activate();
     shUserForm.getRange("C4").setBackground('red');
     return false;

 }
 
 //Validating Device ID
 if (shUserForm.getRange("C6").isBlank()==true){

     ui.alert("Please enter Device ID.");
     shUserForm.getRange("C6").activate();
     shUserForm.getRange("C6").setBackground('red');
     return false;

 }

 //Validating Start Date
 if (shUserForm.getRange("C8").isBlank()==true){

     ui.alert("Please enter Start Date.");
     shUserForm.getRange("C8").activate();
     shUserForm.getRange("C8").setBackground('red');
     return false;

 }

 //Validating End Date
 if (shUserForm.getRange("C10").isBlank()==true){

     ui.alert("Please enter End Date.");
     shUserForm.getRange("C10").activate();
     shUserForm.getRange("C10").setBackground('red');
     return false;

 }

 //Validating Payment
 if (shUserForm.getRange("C12").isBlank()==true){

     ui.alert("Please enter Payment.");
     shUserForm.getRange("C12").activate();
     shUserForm.getRange("C12").setBackground('red');
     return false;

 }

 //Validating Location
 if (shUserForm.getRange("C14").isBlank()==true){

     ui.alert("Please enter Location.");
     shUserForm.getRange("C14").activate();
     shUserForm.getRange("C14").setBackground('red');
     return false;

 }

 //Validating Delivery Fee
 if (shUserForm.getRange("C16").isBlank()==true){

     ui.alert("Please enter Delivery Fee.");
     shUserForm.getRange("C16").activate();
     shUserForm.getRange("C16").setBackground('red');
     return false;

 }

 //Validating Extra Fees
 if (shUserForm.getRange("C18").isBlank()==true){

     ui.alert("Please enter Extra Fees.");
     shUserForm.getRange("C18").activate();
     shUserForm.getRange("C18").setBackground('red');
     return false;

 }

 //Validating Parking Expense
 if (shUserForm.getRange("C20").isBlank()==true){

     ui.alert("Please enter Parking Expense.");
     shUserForm.getRange("C20").activate();
     shUserForm.getRange("C20").setBackground('red');
     return false;

 }

 //Validating Wash expense
 if (shUserForm.getRange("C22").isBlank()==true){

     ui.alert("Please enter Wash Expense.");
     shUserForm.getRange("C22").activate();
     shUserForm.getRange("C22").setBackground('red');
     return false;

 }

 //Validating Time
 if (shUserForm.getRange("C24").isBlank()==true){

     ui.alert("Please enter Time.");
     shUserForm.getRange("C24").activate();
     shUserForm.getRange("C24").setBackground('red');
     return false;

 }

 return true;
}

//Function to submit the data to database sheet

function submitData(){

   //declare a variable and set the reference of active google sheet

   var myGoogleSheet=SpreadsheetApp.getActiveSpreadsheet();

   var shUserForm=myGoogleSheet.getSheetByName("Entry Form");

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

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

   var ui=SpreadsheetApp.getUi();

   var response=ui.alert("Submit", "Do you want to submit the Data?", ui.ButtonSet.YES_NO);
   
   //checking the user response
 if(response==ui.Button.NO){

   return;//to exit from this function

 }

 if (validateEntry()==true){

   var blankRow=datasheet.getLastRow() 1; //identify the last blank row
 
 //code to update the data in the spreadsheet

 datasheet.getRange(blankRow,1).setValue(shUserForm.getRange("C4").getValue());//System ID #

 datasheet.getRange(blankRow,2).setValue(shUserForm.getRange("C6").getValue());//Device ID

 datasheet.getRange(blankRow,3).setValue(shUserForm.getRange("C8").getValue());//Start Date

 datasheet.getRange(blankRow,4).setValue(shUserForm.getRange("C10").getValue());//End Date

 datasheet.getRange(blankRow,5).setValue(shUserForm.getRange("C12").getValue());//Payment

 datasheet.getRange(blankRow,6).setValue(shUserForm.getRange("C14").getValue());//Location

 datasheet.getRange(blankRow,7).setValue(shUserForm.getRange("C16").getValue());//Delivery Fee

 datasheet.getRange(blankRow,8).setValue(shUserForm.getRange("C18").getValue());//Extra Fees

 datasheet.getRange(blankRow,9).setValue(shUserForm.getRange("C20").getValue());//Parking Expense

 datasheet.getRange(blankRow,10).setValue(shUserForm.getRange("C22").getValue());//Wash Expense

 datasheet.getRange(blankRow,11).setValue(shUserForm.getRange("C24").getValue());//time

If someone directs me to a site portion of the Google apps scripts site that might be relevant, I'd guess that would get me there or if someone has worked through this and has hints, I'm all ears. I've done a fair amount of searching myself, but have not found anything, at least dumbed down to as far as I need it currently.

CodePudding user response：

There are a lot of ways to go here. This is a possibility.

Assume that you have decided upon a data entry form that has the following controls for selecting the output sheet:

Then you could implement the setting of the current data sheet in this manner:

function onEdit(e) {
  const sh = e.range.getSheet();
  if(sh.getName() == "Entry Form Sheet" && e.range.columnStart == 2 && e.range.rowStart == 1 && e.value) {
     PropertiesService.getDocumentProperties().setProperties('Sheet',e.value);
  }
}

function getCurrentDataSheet() {
  let name = PropertiesService.getDocumentProperties().getProperty('Sheet');
  return SpreadsheetApp.getActive().getSheetByName(name);
}

The wherever you which the output some data the the current data sheet you could use a function like getCurrentDataSheet to get the proper current datasheet so take your example code:

 if (shUserForm.getRange("C4").isBlank()==true){

     ui.alert("Please enter system ID #.");
     getCurrentDataSheet().getRange("C4").activate();
     getCurrentDateSheet().getRange("C4").setBackground('red');
     return false;

 }