[SOLVED] Sheets - Use a form to add data, but to a worksheet name based on the data input

Sheets - Use a form to add data, but to a worksheet name based on the data input

As the title explains, I have created a workbook in Google Sheets. There is a worksheet labeled "Form" which be used to input data into one of 12 worksheets (the months of the year labelled as Apr 2023, May 2023 etc...).

The user will choose the month from a drop down list, and then be offered the days within that month from a second dropdown. I would like assign these two cell input to a variable which can be used to tell the script which worksheet to send the data to.

If I explicitly name the sheet in the script it works fine:

your textvar dataS = ss.getSheetByName("Apr 2023"); //Data Sheet

But I want the "Apr 2023" part to be dynamic, based on user input.

I have tried something like this:

var month = formS.getRange("C1").getValue().toString(); //Get month from cell C1
var yearC2 = formS.getRange("C2").getValue(); //Get date selected within month chosen in C1 
var year = yearC2.toString().substring(15,11); //Extract year in YYYY from var yearC2
var dataS = ss.getSheetByName('month'+" "+'year'); //insert data in Data Sheet

I get this error: TypeError: Cannot read properties of null (reading 'getRange'). Debug highlights the bold line of code below.

//Submit Data
function SubmitData() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var formS = ss. getSheetByName ("Form"); //Data entry Sheet
  //var dataS = ss.getSheetByName("Apr 2023"); //Data Sheet
  
  var month = formS.getRange("C1").getValue().toString(); //Get month from cell C1
  var yearC2 = formS.getRange("C2").getValue(); //Get date selected within month chosen in C1 
  var year = yearC2.toString().substring(15,11); //Extract year in YYYY from var yearC2
  var dataS = ss.getSheetByName('month'+" "+'year'); //insert data in Data Sheet

  var amountchoice = formS.getRange("D4").getValue()
  
  if (amountchoice == true){
  var amount = formS.getRange("E4").getValue();
  }
  else {
  var amount = formS.getRange("C4").getValue();  
  }
  
  var values = [[formS.getRange("C2").getValue(),
                formS.getRange("C3").getValue(),
                amount,    
                formS.getRange("C5").getValue(),
                formS.getRange("C6").getValue(),
                formS.getRange("C7").getValue(),
                formS.getRange("C8").getValue(),
                formS.getRange("C9").getValue(),
                formS.getRange("C10").getValue(),
                formS.getRange("C11").getValue()]];
  **dataS.getRange(dataS.getLastRow()+1, 1, 1, 10).setValues(values);**
  ClearCells();
}

Any help would be appreciated.

Solution

In this section of your code, you have wrapped the variable names with single quotes, meaning it will take those as literal strings, so it's looking for a sheet named "month year" and returning a null.

Try changing this line

  var dataS = ss.getSheetByName(month + ' ' + year); //insert data in Data Sheet

It's definitely good practice to add logger/console logs to your code to help identify things like this.

Logger.log('example text: ' + exampleVariable);