Catch New Rows in Existing Filter Views

Question

I have a series of filter views that filter my Google sheet based on the values in column B.

I have 200 rows in my sheet, so the range for each filter view is A1:C200.

Often, new rows get added to my sheet (in my case, via Zapier, but inserting new rows at the bottom manually has the same effect). Each time this happens, I need to manually update the range of each filter view to be A1:C201, etc.

I have tried to set the range of the filter views to be A1:C (thinking this would cover entire columns forever), but my formula gets corrected to A1:C200.

Is there a way to get filter views to apply to entire columns, so that new rows added to a sheet are also captured by the filter view?

Answer 1

AFAIK at this time the Google Sheets UI doesn't include a option to set filter views to update its range automatically for appended rows but this occurs if the new rows are inserted between the first and last filter view range rows.

I don't know if Zapier is able to insert instead of append rows, but if this could be possible, you could set Zapier to add the new rows after on a fixed row, like row 2 assuming that row 1 is used for filter view headers, in order to avoid to have to calculate the row to make the insertion.

Another way is to use the Google Sheets API but you should know about programming and REST.

References

• https://developers.google.com/sheets/api/reference/rest/v4/spreadsheets/request