CODEWITHSUNDEEP
google-apps-scriptgoogle-sheets-formula
Elad Benda
Elad Benda

Reputation: 36656

apps script copies filtered rows, but duplicate them

I have a filtered column. I want to copy its values, clear all the original values and paste only the content of the copied filtered ones.

I try to use a draft sheet for that, but it seems not to work. The paste repeats the filtered bulk over and over again.

(I had to add the +300, otherwise I get a range mismatch error).

function main() {
  _move(mailTab, 'F8:H', draftTab, 'A8:C');

}

function _move(fromSheet, fromRange, toSheet, toRange) {
  var fromRange = fromRange + "300";
  var toRange = toRange + "300";
  toSheet.getRange(toRange).clearContent();
  fromSheet.getRange(fromRange).copyTo(
    toSheet.getRange(toRange), { contentsOnly: true, skipFilteredRows: true });
  fromSheet.getRange(fromRange).clearContent();
}

enter image description here

Upvotes: 0

Views: 173

Answers (3)

Elad Benda
Elad Benda

Reputation: 36656

I'm not sure how, why but this code fixes the problem.

It copies the filtered rows only.

I manually clear data after the first duplication chunk.

function _move(fromSheet, fromRange, toSheet, toRange) {
  //var fromRange = fromRange + "300";
  //var toRange = toRange + "300";  
  toSheet.getRange(toRange).clearContent();
  fromSheet.getRange(fromRange).copyTo(
    toSheet.getRange(toRange), { contentsOnly: true, skipFilteredRows: true });
  _clearDestinationDups(toSheet, toRange);
  fromSheet.getRange(fromRange).clearContent();
}


function _clearDestinationDups(toSheet, toRange){
  var values = toSheet.getRange(toRange).getValues();
var i;
for (i = 1; i<values.length; i++){
  if (values[i][0] == "") break;
}
toSheet.getRange(toRange).offset(i+1,0).clearContent();
}

Update:

I'm sure it copies all rows and ignores any filter. Try to apply a filter on your source data and you'll see -- all hidden rows will appear on the destination sheet.

My reply: I removed the filter in the source range, but the target range stays the same.

enter image description here

enter image description here

enter image description here

enter image description here

Upvotes: 0

Yuri Khristich
Yuri Khristich

Reputation: 14502

As far as I can tell skipFilteredRows option works only with clear() method. https://developers.google.com/apps-script/reference/spreadsheet/range#clearoptions.

copyTo() has no such option: https://developers.google.com/apps-script/reference/spreadsheet/range#copytodestination,-options

To copy/paste filtered data you need more complicated algorithm.

Something like this:

function main() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheets()[0];
  var range = "A2:B" + sheet.getLastRow();
  var all_data = sheet.getRange(range).getValues();

  var filter_column = 0;
  var filter_criteria = getFilterCriteria(sheet, filter_column);

  var filtered_data = all_data.filter(row => !filter_criteria.includes(row[filter_column].toString()));
  Logger.log(filtered_data);
}

function getFilterCriteria(sheet, column) {
  try { 
    return sheet.getFilter().getColumnFilterCriteria(column+1).getHiddenValues();
  } catch(e) { 
    return []; 
  }
}

If you have a table like this

enter image description here

And if you will apply the filter that hides 1:

enter image description here

You will get the filtered data without 1:

[[2.0, bbb], [, ], [2.0, ddd]]

Just like it looks in the filtered table:

enter image description here

Upvotes: 0

CMB
CMB

Reputation: 5163

From the documentation, skipFilteredRows is not a valid advanced parameter, thus it does nothing. Also, since copyTo always copies to a range of the same size, the filtered values are repeated.

A better way of copying filtered values is to check each row using isRowHiddenByFilter(i) and copy them if the condition returns false.

Sample Code is in How to copy filtered spreadsheet data with Apps Script

Upvotes: 1

Related Questions