7

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?

  • 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 '15 at 22:28
  • Unfortunately, random changes in the file do not cause the filter view to be refreshed. – RudolfKaiser Jul 31 '15 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 '15 at 6:14
  • Thanks for this information! I'll leave the question open in case Google offers this function in the future. – RudolfKaiser Aug 1 '15 at 12:55
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

  • 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 at 0:02
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)
    }
  • 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. – Rubén Apr 26 at 21:28
1

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!

1

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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