Script Copy down formatting: Not supported on a range with a filtered out row

Question

I have a script that copies the formatting of row 2 and pastes the formatting to all other rows. Works fine unless there is a filter being used that hides Row 2 then I get the following error message: This operation is not supported on a range with a filtered out row.

I would like the script to format all rows with or without the filter on and I would prefer to not disable the filter as whoever put it on may still need it. To be noted, the issue is just if row 2 is filtered.

Sample Data & Test Workbook Link

  var ss = SpreadsheetApp.getActiveSpreadsheet(); 
  var Sheet1 = ss.getSheetByName("Submissions");                                         // Team Member Sheet
  var master = Sheet1.getRange(2,1,1,Sheet1.getMaxColumns());
  var target = Sheet1.getRange(2,1,Sheet1.getMaxRows(),Sheet1.getMaxColumns());
  master.copyTo(target, SpreadsheetApp.CopyPasteType.PASTE_FORMAT, false);
}

Just had to add an IF to check if there was not filter to remove. Final Solution was:

function Update_RowFormat() {
  var ss = SpreadsheetApp.getActiveSpreadsheet(); 
  var Sheet1 = ss.getSheetByName("Submissions");  // Team Member Sheet

  var filter = Sheet1.getFilter();
  if (filter !== null) {
    filter.remove();
    }
  SpreadsheetApp.flush() 
  var master = Sheet1.getRange(2,1,1,Sheet1.getMaxColumns());
  var target = Sheet1.getRange(2,1,Sheet1.getMaxRows(),Sheet1.getMaxColumns());
  master.copyTo(target, SpreadsheetApp.CopyPasteType.PASTE_FORMAT, false);
  SpreadsheetApp.flush()  
  var toFilter = Sheet1.getDataRange();
  var filter = toFilter.createFilter();
}

Answer 1

Replicating your concern, I would suggest you remove first the filter via apps script like this:

function Update_RowFormat() {
  var ss = SpreadsheetApp.getActiveSpreadsheet(); 
  var Sheet1 = ss.getSheetByName("Submissions");  // Team Member Sheet

  var currentFilter = Sheet1.getFilter();
  currentFilter.remove();

  var master = Sheet1.getRange(2,1,1,Sheet1.getMaxColumns());
  var target = Sheet1.getRange(2,1,Sheet1.getMaxRows(),Sheet1.getMaxColumns());
  master.copyTo(target, SpreadsheetApp.CopyPasteType.PASTE_FORMAT, false);
}

this will run the script without error but the drawback is , you have to manually re apply the filters since the behavior of Google sheets filter is whenever you filter out certain rows, it won't treat them as part of the table that's why you are getting that error message.

Otherwise, you might want to opt for another alternative approach.

Ref: Remove a filter