Google-sheets – Why are sheet Filter Views not updating for new rows

google sheetsgoogle-sheets-filter

I have a sheet with two filter views, which apply filter on one column and sort on another. The sheet is populated from the API nightly – it has all worked great but today I noticed things in the wrong views.

First issue – Labels column is filtered on containing "CPA" but you can see the one right under the popup is "CVP,feature".

Second issue… Priority is sorted on value but the two new ones at the bottom are not being counted here.

I've tried switching filter views, refreshing the page in my browser, even closing and reopening the document, but nothing. What is wrong and how do I fix this?

Best Answer

Apparently in Google Sheets, when you turn on the filter (say on the first row) and you don't create a named filter view, that filter on the first row is still set with a range that only goes to the maximum number of rows that you have set up at the moment you turn on filters. If you add rows (not data to existing rows, but physical rows) to the spreadsheet, the filter will not read them.

In order to fix this, the user has to "Turn off filter" and then "Turn on filter" to reset the range.

From the question

Is it possible for creating the subset (filtered by Month) in a way that if we were to make changes in that subset view, those changes would cascade back to the Master. Would QUERY work? Or do I have to resort to a script. Or it is not possible.

Short answer

Google Sheets doesn't have a built-in feature that syncs two spreadsheets so you have to use something that extends it.

Explanation

Built-in functions like QUERY are not able to make make changes to another range. They could only return a value or an array of values. The same apply to custom functions.

Below is included a very simple Google Apps Script code example that exports the value of an edited cell to a cell of another spreadsheet. That code could be added to two spreadsheets to export the edited cells values to each other in order to have a "two way sync" between those spreadsheets.

It's purpose is to show that it's possible to sync two spreadsheets by using Google Apps Script. In order to make it work to sync a master sheet with filtered sheet to logic to find which rows are the match in the other file should be added. Usually this is done using an unique ID.

Broad instructions

Copy the code to script projects bounded to the each spreadsheet and update the targetID and targetSheetName variable definition code lines (lines 1 and 2).
Add a On Edit installable triggers to each script project.

Code

var targetID = 'spreadsheet-id';
var targetSheetName = 'Sheet1';

function onEdit(e) {
  // Get the event object properties
  var range = e.range;
  var value = e.value;
  //Get the cell position 
  var row = range.getRowIndex();
  var column = range.getColumnIndex();
  exportValue(row,column,value)
}

function exportValue(row,column,value) {
  var ss = SpreadsheetApp.openById(targetID);
  var s = ss.getSheetByName(targetSheetName); 
  var target = s.getRange(row, column);
  target.setValue(value);
}

Limit the changes to be synced

In order to limit the changes to be synced add rules to the code. The JavaScript comparators ==, ===, !=, !==, >, <, >=, <=, and the logical operators &&, ||, ! with if, switch among other commands could be used to create simple of complex rules.

Best Answer

Related Solutions

Google Sheets – Copy Data to Another Sheet Based on Month of Date

Google-sheets – Copying data to *and from* another sheet based on the month of a date

From the question

Short answer

Explanation

Broad instructions

Code

Limit the changes to be synced

References

Related Topic

Google-sheets – Copying data to and from another sheet based on the month of a date