Error: Attempting to access the 'length' property of an undefined variable in a Google Sheets document

Question

Error: Attempting to access the 'length' property of an undefined variable in a Google Sheets document

Could someone help me troubleshoot this issue I'm encountering in Google Sheets Apps Script: "TypeError: Cannot read properties of undefined (reading 'length')"? I found this script on YouTube and tried to implement it.

Below is the code snippet:

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

javascript google-sheets google-apps-script typeerror

Answer 1

Answer №1

If you encounter the error message,

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

, it could be due to unsafe data access as newData may be empty if no rows meet the filter criteria.

To address this issue, I have added a check for newData below.

Additionally, make sure to use toLowerCase() instead of tolowercase.

function showInputBox() {
  var ui = SpreadsheetApp.getUi();
  var input = ui.prompt("Please enter your Name.", ui.ButtonSet.OK_CANCEL);

  // Verify 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;  // Make sure comparison is case-insensitive
    });

    // Check if any matching 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.");
  }
}

Answer 2

If you encounter the error message,

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

, it could be due to unsafe data access as newData may be empty if no rows meet the filter criteria.

To address this issue, I have added a check for newData below.

Additionally, make sure to use toLowerCase() instead of tolowercase.

function showInputBox() {
  var ui = SpreadsheetApp.getUi();
  var input = ui.prompt("Please enter your Name.", ui.ButtonSet.OK_CANCEL);

  // Verify 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;  // Make sure comparison is case-insensitive
    });

    // Check if any matching 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.");
  }
}

Error: Attempting to access the 'length' property of an undefined variable in a Google Sheets document

Answer №1

Similar questions

How can one retrieve the set-cookie value in a <meta http-equiv> tag using Node.js?

Is there a tool that can help pinpoint the original collection for shared elements across multiple collections?

Learn how to effortlessly update models by integrating AngularJS with Django and Django Rest Framework

Unlock the full potential of your database with ExpressJs and MySQL. Discover a superior option to Knex Interceptor for securing your data with

Creating Particle Systems using THREE.js

Creating a responsive HTML5 webpage that adjusts seamlessly to all screen sizes without the need for scrolling

The clear icon in React will only appear when there is a value inputted

What is the process of transforming a string into a JSON object?

Vue.js displays an error: TypeError - "this is undefined" when executing JavaScript

What is the reasoning behind placing CDN links at the bottom of the index file?

Convert an array comprising arrays/objects into JSON format

Issue with nivo-lightbox not opening upon clicking image

JS limits browsers to downloading only 10 images simultaneously

Ways to induce scrolling in an overflow-y container

What are some methods to maintain active MySQL connections in a Node.js environment?

Retrieve specific information by clicking a button within a table cell on the data grid in React.js

What advantages does using a callback offer compared to await?

Exploring AngularJS's capabilities with Cross Domain POST requests

What is the proper way to utilize a variable as a field name in a Mongo query within the Meteor framework?

Is there a way to set up gulp without relying on npm when using shared hosting?