CODEWITHSUNDEEP
javascriptgoogle-apps-scriptgoogle-sheets
user13932223
user13932223

Reputation:

Custom function with given range and arguments - Google Apps Script

I have a Google Sheets table: Sheet 1 data table

and this function:

function loopMovement(movement, variation, range, a, b, c) {
  let arr = [];
  let projection = null;
  for (let i=0; I<=range.length; i++) {
    if (range[i][a]===movement && range[i][b]===variation){
      projection = range[i][c];
      arr.push(projection);
      let maxProjection = Math.max(arr);
    }
  }
  return maxProjection;
}

and here is the cell I have in Sheet 2: =loopMovement(Squat,Competition,Sheet1!A$2:E$7,0,1,4). However I am returned an error in the line: if (range[i][a]===movement && range[i][b]===variation){: TypeError: Cannot read property '0' of undefined (line 8).

I tried testing and here are the return values:

  1. Return range.length: 6
  2. Return range[0][a]: Squat
  3. Return range[0][c]: 110

Upvotes: 1

Views: 183

Answers (1)

Marios
Marios

Reputation: 27348

Solution:

function loopMovement(movement, variation, range, a, b, c) {
  let arr = [];
  let projection = null;
  for (let i=0; i<range.length; i++) {
    if (range[i][a]===movement && range[i][b]===variation){
      projection = parseFloat(range[i][c]);
      arr.push(projection);
    }
  }
  var maxProjection = arr.reduce(function(a, b) {
    return Math.max(a, b);
}); 
  return maxProjection; 
}

and use the custom function like that:

=loopMovement("Squat","Competition",Sheet1!A$2:E$7,0,1,4)

Error:

The error message you are getting:

TypeError: Cannot read property '0' of undefined

has to do with the fact that the for loop iterates through empty rows. According to your screenshot, there are 6 rows excluding the header. However, for loop iterates until row 10.

Modifications:

I made the following changes to your script:

  1. Instead of using the hardcopy value 10, you can use range.length to get the total number of rows of the given range.

  2. movement and variation in the formula have to be passed as strings:

    =loopMovement("Squat","Competition",...)

  3. Math.max() does not accept an array as an argument. For example, Math.max(1,5,2) returns 5, but Math.max([1,5,2]) returns NaN.

    Array.reduce() can be used to find the maximum element in a numeric array:

    var maxProjection = arr.reduce(function(a, b) {
return Math.max(a, b);
});

  4. The expression range[i][c] returns a string with the desired value. However, you want to construct a numeric array, therefore you need to convert it to a float number (given that it might contain decimals) using parseFloat():

    parseFloat(range[i][c])

Upvotes: 1

Related Questions