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();
  }
}

Answer 5

I spent the morning digging around for something similar.

Unfortunately the function call to UpdateFilter() posted by wriddle2 didn't work. The function isn't defined in Apps Script for a Google Sheets project when I tried it today, April 15 2021. It was pretty close to what I ended up using, though.

In my case, I'm pulling information in from another spreadsheet and merging it with information in the one where the script runs (Google, can we PLEASE have JOIN capability in the QUERY function?).

I'm sure that the original poster has long ago moved on with their life, but I'll frame my answer here in that context anyway. This can be called from any function in an Apps Script project, it doesn't have to be in onFormSubmit().

function onFormSubmit(e) {
  SpreadsheetApp.flush();
}

In my case, I can't know if the user will have a filter enabled in the first place, so I have a bit of extra code to save it, then remove and clear the sheet before inserting today's data, and re-add the filter if one existed beforehand. It looks something like this:

function myFunc() {
  var spreadSheet = SpreadsheetApp.getActive();
  var reportSheet = spreadSheet.getSheetByName('Report Sheet');
  var columnCount = reportSheet.getLastColumn();

  reportSheet.clearContents(); // I do this because each day gets a fresh report

  if(reportSheet.getFilter()) {
    var hadFilter = true;
    var sheetFilter = reportSheet.getFilter();
    var filterSettings = new Array(columnCount);

    // Array indexed at 0, columns indexed at 1. A recipe for disaster!
    for(var i = 0; i < columnCount; i++) {
      filterSettings[i] = sheetFilter.getColumnFilterCriteria(i + 1);
    }
    // If you attempt to append data over a set of rows that had a filter still applied, 
    // things get weird. Hence removing and re-creating the filter.
    sheetFilter.remove(); 
  }

  // Not going to bore you with the details; 
  var arrayOfCells = myCustomFunctionThatReturnsData();
  reportSheet.getRange(1,1,arrayOfCells.length,columnCount).setValues(arrayOfCells);

  if(hadFilter) {
    var sheetFilter = reportSheet.getDataRange().createFilter();
    for(var i = 0; i < columnCount; i++) { 
      sheetFilter.setColumnFilterCriteria(i + 1, filterSettings[i]) 
    }
    // Here's the magic! Without this, you get a filter that looks 
    // like it should work but doesn't actually apply.
    SpreadsheetApp.flush();
  }
}