can someone assist me with this error in Google Sheets Apps Script: "TypeError: Cannot read properties of undefined (reading ‘length’)"? I actually copied this script from YouTube.
Please see below the Script.
function showInputBox(){
var ui = SpreadsheetApp.getUi();
var input = ui.prompt("Please enter your Name.",ui.ButtonSet.OK_CANCEL);
if(input.getSelectedButton() == ui.Button.OK){
var ss = SpreadsheetApp.getActiveSpreadsheet();
var ws = ss.getSheetByName("original");
var data = ws.getRange("A2:G" + ws.getLastRow()).getValues();
var userSelectedRep = input.getResponseText().tolowercase;
var newData = data.filter(function(r){ return r[2] == userSelectedRep });
var newWs = ss.insertSheet(userSelectedRep);
newWs.getRange(2, 1, newData.length, newData[0].length).setValues(newData);
}
>Solution :
The error you are getting, TypeError: Cannot read properties of undefined (reading 'length') can be because you are not accessing the data safely as newData could be empty if no rows match the filter conditions.
So, below i have implemented a check for newData.
Also the tolowercase should be toLowerCase()
function showInputBox() {
var ui = SpreadsheetApp.getUi();
var input = ui.prompt("Please enter your Name.", ui.ButtonSet.OK_CANCEL);
// Check if OK button was clicked
if (input.getSelectedButton() == ui.Button.OK) {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var ws = ss.getSheetByName("original");
var data = ws.getRange("A2:G" + ws.getLastRow()).getValues();
// Convert user input to lowercase
var userSelectedRep = input.getResponseText().toLowerCase();
// Filter data based on user input
var newData = data.filter(function(r) {
return r[2].toLowerCase() == userSelectedRep; // Ensure comparison is case-insensitive
});
// Check if any data was found
if (newData.length > 0) {
// Create a new sheet and insert the filtered data
var newWs = ss.insertSheet(userSelectedRep);
newWs.getRange(2, 1, newData.length, newData[0].length).setValues(newData);
} else {
ui.alert("No matching data found for the entered name.");
}
} else {
ui.alert("Operation canceled.");
}
}