Search code examples
google-apps-scriptcustom-function

Google Apps script - syntax of passed in ranges


I use Google Apps Scripts to analyse some data in a Google Sheet. I want to obtain the first date of purchase and the last date of sale of a group of tickers "Tickers" extracted from a list of tickers "TickerRange". The dates are in "dateRange". The tickers should be included in the calculation only if the related tickbox is checked in "includeRange". After a lot of trying, I believe there is a syntax problem in the line:

if (TickerRange[i] == Tickers[j]) {

I have also tried

if (TickerRange[i].value == Tickers[j].value) {

and still it does not work. Here the full code:

function TickersDates(Tickers,TickerRange,dateRange,includeRange) {  
    var Datesoutput = new Array(1);
    // Compute StartDate and EndDate for the current tickers
    var dateTemp = new Date(1900, 01, 01);
    var minDate= new Date(2900, 01, 01);
    var maxDate= new Date(1900, 01, 01);
    for(var j=0, jLen=Tickers.length; j<jLen; j++) {
        if (includeRange[j]=="true" && Tickers[j]!="") {
            for(var i=0, iLen=dateRange.length; i<iLen; i++) {
                dateTemp = new Date(dateRange[i]);
                if (isValidDate(dateTemp)){
                    if (TickerRange[i] == Tickers[j]) {
                        minDate = Math.min(dateTemp.valueOf(), minDate.valueOf());
                        maxDate = Math.max(dateTemp.valueOf(), maxDate.valueOf());
                    }
                } 
            }   
        }           
    }    
    var StartDate = new Date(minDate)  
    var EndDate = new Date(maxDate)  
    Datesoutput[0]=StartDate
    Datesoutput[1]=EndDate
    return Datesoutput;  
}

Solution

  • The arguments of a custom function is a 2 dimensional array. You should use

    if (TickerRange[i][0] == Tickers[j][0]) {
    

    as objects cannot be compared: []!==[]