Google Apps Script Copy/Paste Filtered DataSet

Question

I've been trying to write a script that simply takes a filtered data, copies it, and then pastes it into another sheet. Nothing I seem to do works. With the code below, which I found online, it should work, but I keep getting an error that states The number of rows in the range must be at least 1. However, I have data in the range A7:R500 and I'm only filtering out blanks and 'W'. Am I correct in this thinking?

function copyPaste(){
  var sheet = SpreadsheetApp.getActiveSheet();
  var values = sheet.getRange('A7:R500').getValues();

  var hiddenValues = ['', 'W'];
  values = values.filter(function(v){
    return hiddenValues.indexOf(v[4]) == 'W';
  });

  sheet.getRange(1,21, values.length, 18).setValues(values);
}

Answer 1

Solution:

Since you are already using a filter Array, you can compare hiddenValues.indexOf(v[4]) to -1 to filter out blanks and "W".

Also, since your goal is to paste the results in a different sheet, you need to define both the source and the destination sheet. Create a sheet and plug its name into the new sheet name tag in the code below.

Sample Code:

function copyPaste() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet1 = ss.getActiveSheet();
  var sheet2 = ss.getSheetByName('<new sheet name>');
  var values = sheet1.getRange('A7:R500').getValues();
  var hiddenValues = ['', 'W'];
  values = values.filter(function(v){
    return hiddenValues.indexOf(v[4]) == -1;
  });
  sheet2.getRange(1,21, values.length, 18).setValues(values);
}

Reference:

indexOf()