Q: Loop in Google Apps Script Error: Cannot read property "length" of undefined

Question

I got the Error: Cannot read property length of undefined and dont quite understand the problem.

From the other posts of stackoverflow I figured out that the function cannot determinate the length of the parsed Object.

What I am doing is the following:

I do import historical data of e.g. google stock price with =Index(GOOGLEFINANCE("NASDAQ:GOOG";"price";HEUTE()-60;HEUTE());;2) into my spreadsheet. Then I calculate the 21 simple moving average from the data with =average(A128:A148).

Now I compare the stockprice and the 21 SMA to determinate if the Stockprice is above or under the 21 SMA. So far so good.

Last I try to write a function that counts the days above the 21 SMA starting from the latest Value.

function daysabove(input){
var output = 0;
for (var i = input.length-1;i>= 0;i--){ 
if (input[i][0]=="over" ){
 if (input[i+1][0]=="under"){
output++;
   break;
 }
 else {
 output++;
        }
    } ` 
else  {
  output--;
     } 
}
   return output;}

On the spreadsheet i call that function with =daysabove(C129:C148) . C129 to C148 containing either above or under.

Edit: I even tried to run the function on a range that contain manually written "above" & "under" so that there is no connection to google finance. Still doesnt work.

So getting back to the question: The length or the object should be defined. Whats wrong here?

Answer 1

Error: Cannot read property length of undefined

This occurs , because input is undefined, when called from the script editor and doesn't have a length property, because you don't pass any value as input to the function daysabove.

---

Error: Cannot read property[0] from undefined

This occurs because i+1 evaluates to out of the index range. Say, You execute =daysabove(C1:C4). C1:C4 will all be "over".

for (var i = input.length-1;i>= 0;i--){ 
if (input[i][0]=="over" ){
 if (input[i+1][0]=="under"){

input.length will be 4;
input.length-1 will be 3;
i is initialized as 3;
Note that in javascript arrays, the first element's index is 0;
index of ["over","over","over","over"] from front to back is 0,1,2,3 respectively;
input[i][0] == input[3][0] will be the last element in C1:C4;
The next statement calls for i+1,i.e., input[4] ,which is undefined. Undefined is not a array. Hence we cannot read the property [0] of undefined.
If you're gonna do a bottoms-up loop, then make sure everything is reverse. So, your loop can be fixed by using input[i-1][0] as the next statement and changing for-condition to i>0. There are other syntax errors too. Consider reviewing arrays and taking a tutorial

---

Regardless,
As written in the Official documentation,

Historical data cannot be downloaded or accessed via the Sheets API or Apps Script. If you attempt to do so, you will see a #N/A error in place of the values in the corresponding cells of your spreadsheet.

Your script will fail in any case,as it references historical google finance data.

---

If you just want to count the number of days above-days under, use

=COUNTIF(C1:C4,"over")-COUNTIF(C1:C4,"under")