How to automatically refresh a filter view in Google Sheets when new data comes in via Google Forms?

Question

I have a filter view in Google Sheets that does not refresh when new data comes in via Google Forms.

To refresh the filter view, I can remove the filter and apply it again, or even just click "OK" in the filter menu without chaning anything. F5 and closing/reopening the page does not work, though.

I need a way to automatically refresh the filter view when new data comes in. Or at least to get the refreshed filter view when I close and reopen the website witout having to manually fiddle with the filter settings.

Any ideas how I could do this? Maybe with a Script that fires onFormSubmit?

Answer 1

Google Apps Script is used to extend Google Sheets, but as was noted by @Normal Human, at this time it doesn't include methods to update/refresh Google Sheets filters.

https://code.google.com/p/google-apps-script-issues/ is the place for feature requests and enhancements to existing Google Apps Script services. People can "upvote" a feature request by starring an issue. In this case, the related issue is:

Issue 524: Integration of Tools/Filter with google apps script

Answer 2

Try a Script that fires onFormSubmit. Get and then re-set the same criteria.

  var sheet = SpreadsheetApp.getActiveSheet();
  var filter = sheet.getFilter();
  if (filter) {
    var criteria = filter.getColumnFilterCriteria(4);
    if (criteria) {
      filter.setColumnFilterCriteria(4, criteria)
    }

Answer 3

I have found a workaround that creates the same result, though it may take a little bit more time to set up.

What I do is create a pivot table of the data that comes in from the Google Forms results, manipulate the table to represent the data the way I want it, and then link the pivot table to a new sheet ( using ={'Sheet Name'!A1:G100} ) and format that final sheet the way I want.

This way you can filter/manipulate the data in the pivot table, and it will be reflected in the final sheet. Also, and most importantly, it automatically updates as new form data comes in.

** Note: Make sure that the pivot table range includes the empty cells in the Google Form results sheet where the data will be added; meaning if you only have 50 rows of data now, but you know that by next week you'll have 1000 rows, set up the pivot table's range to 1000+ rows!

Answer 4

Sometimes if I need to figure out a function on Google Sheets, I start recording a macro, make the change that I want to make, and then stop recording the macro. Doing this, I found out that there is a refresh function.

I wrote a simple onEdit function. My filter only refreshes when the data on a specific page is changed because that is the only page that affects the filtered rows.

function onEdit(e){

  // Set a comment on the edited cell to indicate when it was changed.

  var range = e.range;
  var sheet = range.getSheet();

  if (sheet.getSheetName() === "CNST Cost Estimator")
  {
    UpdateFilter();
  }
}