Follow

Keep Up to Date with the Most Important News

By pressing the Subscribe button, you confirm that you have read and are agreeing to our Privacy Policy and Terms of Use
Contact

TypeError: Cannot read properties of undefined (reading 'length') in google sheet

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);

}

MEDevel.com: Open-source for Healthcare and Education

Collecting and validating open-source software for healthcare, education, enterprise, development, medical imaging, medical records, and digital pathology.

Visit Medevel

>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.");
  }
}
Add a comment

Leave a Reply

Keep Up to Date with the Most Important News

By pressing the Subscribe button, you confirm that you have read and are agreeing to our Privacy Policy and Terms of Use

Discover more from Dev solutions

Subscribe now to keep reading and get access to the full archive.

Continue reading