CODEWITHSUNDEEP
javascriptgoogle-apps-scriptgoogle-sheetscustom-function
firenemus
firenemus

Reputation: 165

Avoiding a TypeError object when Using the Map Method in Google Apps Script

Based on some help I received yesterday, I've created a @customFunction script to run in my spreadsheet which "appends" a value to a string.

In column A of the spreadsheet I have a student's current GPA (ex. 3.2) and in column B I have a string of their previously recorded GPAs (ex. [2,3,3,4.5,2.5,2.1,1.3,0.4]). The script adds the current GPA to the end of the string.

In this case current and stored are named ranges in the spreadsheet.

/** @customFunction */
function GPAweekly(current,stored) {
stored = stored.replace(']',','+current+']');
return stored;
}

This works fine when using a 1D range and for only a few rows. However when I use it for larger amounts of data, I hit the Google's service quota and get the “Service invoked too many times in a short time” error.

My next attempt was to use a 2D range and the map() method to call my function recursively.

/** @customFunction */
function GPAweekly(current,stored) {
if (typeof current.map === "function" && typeof stored.map === "function") { 
return current.map(GPAweekly);
return stored.map(GPAweekly);
}  

stored = stored.replace(']',','+current+']');
return stored;
}

This seems to work until the script reaches the replace() method and then I get an error in the spreadsheet cell stating "TypeError: Cannot find function replace in object 0".

I have two questions:

1) How can I get around this TypeError issue?

2) Is there a more elegant way of handling what I'm trying to achieve overall (keep in mind I'll have a couple thousand students data to deal with).

I apologize for the title. I couldn't quite figure out how to word it.

Upvotes: 2

Views: 788

Answers (1)

tehhowch
tehhowch

Reputation: 9872

Note that Array#map provides 3 parameters: the element, the index, and the original Array. Thus when you call return current.map(GPAweekly);, you are doing the equivalent of this code:

var results = [];
for (var i = 0; i < arr.length; ++i) {
  results.push(GPAweekly(arr[i], i, arr));
return results;

In other words, because the 2nd parameter given to GPAweekly is a number (0), rather than the presumed string, you receive the given error message. Also note that a multiple-range input is passed as a 2D array, even for a single column, e.g.

=GPAweekly(A1:A5, B1:B5) ->
/**
  current == [ [A1val], [A2val], [A3val], [A4val], [A5val] ]
  stored  == [ [B1val], [B2val], ... ]
 */

The solution is to ensure you call GPAweekly with both of the original parameters, and to extract the value from the inner array:

function GPAweekly(current, stored) {
  if (current.map && stored.map // Array test
      && current.length // Non-falsy length
      && current.length === stored.length // inputs are equally long
  ) {
    return current.map(function (row, i) {
      GPAweekly(row[0], stored[i][0]);
    });
  } else {
    // at least one of the tested conditions failed, so handle appropriately...
    ...
  }
}

Upvotes: 2

Related Questions