javascriptarrayssortinggoogle-sheetspy-appscript

filtering an array by date and pass matching data set to a new array to be written into a google sheet


Hi im currently trying to compare an array of data using the ArrayLib but it is unable to match any of my dates and thus passing all of the data sets instead of just the ones between the date range. If there is a better way to achieve this im open to it [copy of my code that I am currently using ][1]: https://i.sstatic.net/Uj7Wo.png

EDIT: as requested a Google sheet of a sample data table im using: https://docs.google.com/spreadsheets/d/1whrB9O84i85grz6Av3EY-hnGGNvVG6ji_v5w7X_rMG0/edit?usp=sharing


Solution

  • Instead of using a separate library to filter your multidimensional array by date, you can try this sample script below:

    Sample script

    function main() {
      var ssLc = SpreadsheetApp.getActive().getActiveSheet();
      var rangeLc = ssLc.getRange('A1:R15');
      var Lc = rangeLc.getValues();
    
      var startDate =  new Date('January 04, 2021 08:00:00'); //Used GMT time on my testing
      var endDate =  new Date('June 03, 2021 08:00:00'); //Used GMT time on my testing
    
      var sampleResult = filterByDate(Lc, startDate, endDate);
    
      for(x=0; x<sampleResult.length;x++){ //Sample loop to log the each filtered array data
        Logger.log(sampleResult[x]);
      }
    }
    
    function filterByDate(arrayData, startDate, endDate){
      var newArrayResult = [];
      for(index = 0; index < arrayData.length; index++){
        if(arrayData[index][0].getTime() >= startDate.getTime() && arrayData[index][0].getTime() <= endDate.getTime()){
          newArrayResult.push(arrayData[index]);
      }
     }
     return newArrayResult;
    }
    

    Sample Result:

    Used your sample spreadsheet data:

    enter image description here

    Sample result by filtering data from January 04, 2021 to June 03, 2021

    enter image description here