Unable to filter data from other spreadhseet paste in master sheet based on dates in google spreadsheet

Question

First of all thank you for the efforts made in this site. As an individual and a beginner i have learnt from my errors made. Thanks for all who have contributed & extended their support. Thanks for this.

Here is a small program written which is not working (no output)seen , i have tried it in many ways but in vain. please help me to find a solution for this. the aim of this program was to filter the data from 4 sheets and paste into current sheet (master). this filter is based on date values.

Conditions of dates are taken from the master sheet in columns in (b2 & d2)dates. this are to be filtered out based in column no.18 which has dates in client sheets.

function myFunction3() {

var source = ['0AjkkHlm3kCphdGhSWnlxWmFsakZ2aFhMSHl6SlF3M1E',
                '0AjkkHlm3kCphdHY2aXpjTVJEMlFRYVBST0ZPYzNwRFE',
                '0AjkkHlm3kCphdEc5ZHFpeHVlc241SlFKWGJDeXFKLXc',
                '0AjkkHlm3kCphdG9WVjVRRnQ3RlFlcllhd1JGallXVmc'];

var ss = SpreadsheetApp.getActiveSpreadsheet();
// get start date from sheet
var sDate = ss.getSheetByName('123').getRange("B2").getValue();
// get end date from sheet
var eDate = ss.getSheetByName('123').getRange("D2").getValue();
// days between
var Dura = ss.getSheetByName('123').getRange("E1").getValue(); 

  var codes = new Array();

  for (var k = 0; k < Dura; k++){ 
      var d = new Date(sDate);
      d.setDate(d.getDate()+ k);
      codes[k] = d;
  }

  var numCodes = codes.length;
  var copied = [];
  for (var k = 0; k < numCodes; k++) {
    copied[k] = [];
  }

//get data from external sheets for comparision
  for (var i = 0; i < source.length; i++) {
    var tempCopy = SpreadsheetApp.openById(source[i]).getSheetByName('Footfall-Format').getDataRange().getValues();
    // comparision starts
    for (var j = 0; j < tempCopy.length; j++) {
      var codeIndex = codes.indexOf(tempCopy[j][5]);
      if (codeIndex > -1) copied[codeIndex].push(tempCopy[j]);
    }
  }
  var sheets = SpreadsheetApp.getActive().getSheets();
  for (var m = 0; m < numCodes; m++) {
    if (copied[m][0] != undefined) {
      var gensheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('123');
      gensheet.getRange(5, 1, 1500, 18).clear({contentsOnly:true});
      gensheet.getRange(5, 1, copied[m].length, copied[m][0].length).setValues(copied[m]);
    }
  }
}

score 2 · Accepted Answer · edited May 23 '17 at 10:27

The fundamental problem is that you are comparing objects for equality - in this case, you're comparing Date objects. Even when the date represented by two of these objects is the same, the object comparison comes up false unless you're actually referencing the same object. You can read more about this in Compare two dates with JavaScript.

Here's a simple change to your script, using toDateString(), that will ensure your codes[] array contains string values that can be compared with values in tempCopy[j][5].

function myFunction3() {

var source = ['0AjkkHlm3kCphdGhSWnlxWmFsakZ2aFhMSHl6SlF3M1E',
                '0AjkkHlm3kCphdHY2aXpjTVJEMlFRYVBST0ZPYzNwRFE',
                '0AjkkHlm3kCphdEc5ZHFpeHVlc241SlFKWGJDeXFKLXc',
                '0AjkkHlm3kCphdG9WVjVRRnQ3RlFlcllhd1JGallXVmc'];

var ss = SpreadsheetApp.getActiveSpreadsheet();
// get start date from sheet
var sDate = ss.getSheetByName('123').getRange("B2").getValue();
// get end date from sheet
var eDate = ss.getSheetByName('123').getRange("D2").getValue();
// days between
var Dura = ss.getSheetByName('123').getRange("E1").getValue(); 

  var codes = new Array();

  for (var k = 0; k < Dura; k++){ 
      var d = new Date(sDate);
      d.setDate(d.getDate()+ k);
      codes[k] = d.toDateString(); //***** Make array of Strings, not Dates
  }

  var numCodes = codes.length;
  var copied = [];
  for (var k = 0; k < numCodes; k++) {
    copied[k] = [];
  }

//get data from external sheets for comparision
  for (var i = 0; i < source.length; i++) {
    var tempCopy = SpreadsheetApp.openById(source[i]).getSheetByName('Footfall-Format').getDataRange().getValues();
    // comparision starts
    for (var j = 4; j < tempCopy.length; j++) {  // start at 4 to skip headers
      if (typeof tempCopy[j][5] != "object") break;   // skips strings, but could improve
      // Search for String match of date from input record
      var codeIndex = codes.indexOf(tempCopy[j][5].toDateString());
      if (codeIndex > -1) copied[codeIndex].push(tempCopy[j]);
    }
  }
  // This part has bugs... each day overwrites the previous
  var sheets = SpreadsheetApp.getActive().getSheets();
  for (var m = 0; m < numCodes; m++) {
    if (copied[m][0] != undefined) {
      var gensheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('123');
      gensheet.getRange(5, 1, 1500, 18).clear({contentsOnly:true});
      gensheet.getRange(5, 1, copied[m].length, copied[m][0].length).setValues(copied[m]);
    }
  }
}

As @Serge points out, there are other problems in this code.

d.getDate()+ k does not handle month-end, so you need to do that yourself.
The last part of your script that handles the output via setValues() needs to be debugged. As it is, each day overwrites the previous day's values from the copied[] array. I'm not sure what requirement you were trying to meet with this, so I left it alone, but it needs attention.

Unable to filter data from other spreadhseet paste in master sheet based on dates in google spreadsheet

1 Answers1