copyTo Not bringing correct data

Question

So I am trying to run a script in Google Sheets. I have two sheets, one of which is a master list with all of my data and another of which is a sheet I am using to filter results when I want to look through a specific date/category. I'm doing this by having three cells in my filtering sheet that I would fill out information to match up with the master list and then having a button that would copy the data over. Here is my code:

function filter() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var s = ss.getSheetByName("Total");
  var month = ss.getSheetByName("Filter").getRange("A2").getValue();
  var payment = ss.getSheetByName("Filter").getRange("B2").getValue();
  var category = ss.getSheetByName("Filter").getRange("C2").getValue();


  for(var i = 1; i < 200; i++)
  {
  var p = s.getRange(i, 5);
  var c = s.getRange(i, 6);  
  var m = s.getRange(i, 7);

   if( p.getValue() == payment && c.getValue() == category && m.getValue() 
== month){
     var targetSheet = ss.getSheetByName("Filter");
      var target = targetSheet.getRange(targetSheet.getLastRow() + 1, 1);
      s.getRange(i, 1, 1, 7).copyTo(target);
   }


  }
}

Using && conditional inside of my if statement results in nothing appearing. Using || conditional results in everything appearing. I can't seem to figure out how to make it so that I can sort by just one category OR all categories.

Thanks in advance!

Here is a spreadsheet using the same code with some sample data https://docs.google.com/spreadsheets/d/1VRY1P5drqiHaARZM5xvutt6K0TvE0drZ8d5_AycMncg/edit?usp=sharing — Aloy, Sep 17 '17 at 17:05
@Aloy I used your code above and cannot reproduce the issue in my copy of the sample sheet. Are there specific steps or combinations that reliably reproduce the bug? — Brian, Sep 17 '17 at 18:21
@BrianBennett an example would be if you try to filter by "September" and "Cash." This will bring in everything from September and everything from cash, but I am trying to get it to bring in only rows with both of those values. However, if I change the script from using '| |' to using '&&' then it brings in none of the rows. — Aloy, Sep 17 '17 at 19:16

score 0 · Answer 1 · answered Sep 17 '17 at 19:33

You need all 3 values using a short-circuit comparitor (&& and ||). If the condition isn't met, the script exits. You also need to clean up your logic tests by grouping items in parentheses.

For instance,

if(p.getValue() == payment && m.getValue() == month)

returns values with "September" and "Cash" only. "Category" is irrelevant.

Using

if((p.getValue() == payment && m.getValue() == month) || (p.getValue() == payment && c.getValue() == category))

will return a row if the payment and the value match or if payment and category match, regardless of month.

Breaking conditions out using parentheses will help. Also, reconsider using comparators that short circuit a script. This post describing the difference may help.

copyTo Not bringing correct data

1 Answers1