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 text
var 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.
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);