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?

You can't use Google Finance programmatically. You didn't read the documentation of the finance functions. — tehhowch
THx for the reply, i tested that by just writing down 21values of "under" & "above" in a column and did parse this range as inputto the formula. That didnt work either. So i shouldnt be connected with google finance. Thx though for the information aber finance. didint know that. Makes google finance kinda unreliable — user8603220
in the spreadsheet its calld property[0] from undefined and in the scripteditor it is property length of undefined . So thats not the same error ? — user8603220

TheMaster TheMaster · Accepted Answer · 2018-07-30T21:26:03

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

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

1 Answers