14

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?

4
  • Does editing a spreadsheet refresh the filtered view? Then your script could just apply setValue to some unused cell. Apps Scripts (infamously) don't have a way to manipulate filter views..
    – user79865
    Jul 30, 2015 at 22:28
  • Unfortunately, random changes in the file do not cause the filter view to be refreshed. Jul 31, 2015 at 10:11
  • This is unfortunate indeed. If the only user action that refreshes a filter view is touching the filter, then a script won't be able to help: a script can't touch a filter.
    – user79865
    Aug 1, 2015 at 6:14
  • Thanks for this information! I'll leave the question open in case Google offers this function in the future. Aug 1, 2015 at 12:55

10 Answers 10

4

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

1
  • 2
    That issue is now marked as solved, but it doesn't directly answer the question. For G-sheets amateurs like myself, it would be very helpful if you can post instructions of how to do this.
    – Elliott B
    Aug 6, 2019 at 0:02
5

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!

4

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)
    }
1
  • Welcome. When suggesting the use of a script it's better to include a complete function and if this is too large, include the main code lines and a link to space with the complete code. Apr 26, 2019 at 21:28
2

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

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

Look into the Tools -> Add a Slicer option.

Select the table and set it up like a standard filter. Then I've found that it is important to click the 3 dots and set it as a Set Current Filter as Default, then it automatically updates.

2
  • 1
    Glad I gave your solution a shot, it's the only one here the worked for me. At first I thought it didn't work but it turns out I forgot to Set Current Filter as Default as you said The other solutions here either required figuring out how to make the code work for my project or simply failed.
    – A Kareem
    Jun 4, 2023 at 17:23
  • 1
    Glad to hear. I had to use it on a printable spreadsheet so I changed the text and background of the slicer element to white then it does not show up. Aug 4, 2023 at 13:54
1

In 2021, the answer to this is to delete and recreate the filter on a google-apps-script (GAS) trigger. In this case, the trigger could indeed be an onFormSubmit trigger.

Refer to this answer for an implementation: https://stackoverflow.com/a/67799857

Refer to my answer in a separate question for how to obviate this need by using Slicers (which are still incredibly buggy): https://webapps.stackexchange.com/a/158627/111710

0

I don't know if this helps since I'm also a noob, my workaround to have an autoupdating filter view is to importrange the linked sheet of the Google Form, then apply the filter view so that it will automatically update.

0

I've just found that Google Sheets filter views solve this problem without needing any complex scripting.

The advantage of this over pivot tables is that you can still edit the data.

Data -> Filter views

See here for more details: https://support.google.com/docs/answer/3540681?#zippy=%2Csave-a-filter-as-a-filter-view

-2

I am glad to have found a solution to this.

The solution is the QUERY funciton. Look it up, it simply does the auto refresh filter that you need.

I use it together with IMPORTRANGE, and well, it works wonderfully.

Hope it works. Regards, Francisco

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.