lundi 7 mai 2018

Google Script: Looping If Else Array Through Rows

this is my first question so hopefully I follow all of the guidelines, let me know where I can improve!

I am trying to create a simple function for google scripts which takes 4 columns data and loops through them with an if else statement. It takes the first value that isn't equal to 0 and subtracts them from a 5th column. It does this through each row of one of the sheets, and pasts the results in 6th column. I keep getting "TypeError: Cannot find function foreach in object." My assumption is that I am not storing the Array correctly? I cannot embed pictures as I have no feedback yet. Here is the code, and a link to sample data, if you need more to be placed here please let me know. https://docs.google.com/spreadsheets/d/182C95mMxWlw4qqiEoVI3RjDJnXpUItk0TwS33amg1zE/edit?usp=sharing. For the data, Line 6, would need to ignore I6, and take J6 and subtract it from M6, then paste the results in P6.

function waterFallCalculator() 
{
var deltaRange = SpreadsheetApp.getActive().getSheetByName("4/18"),
res = [];
deltaRange.getRange(30,9,100,5).getValues().foreach
  (function(row)
   {  
        res.push([row.reduce(function (a,b,c,d,e)
                             {
                               if(!isNaN(a))
                               {
                                 return (a-e)
                               if(isNaN(a))
                               {
                                 return (b-e);
                               }
                               else if(isNaN(b)) 
                               {
                                 return(c-e);
                               }
                               else if(isNaN(c))
                               {
                                 return(d-e);
                               }  
                             }  
                             }
                            )
                 ])
        }
   )
        return deltaRange.getRange(30,16,100,1).setValues(res);

}

Aucun commentaire:

Enregistrer un commentaire