How to filter on values produced by a formula in google sheets without moving the formula?

0
votes

I am using a FILTER function to pull data from one tab in a sheet to another based on a criteria in column K from the source tab. The source tab updates on a nightly basis (automatic refresh of a BigQuery connection). I want to pull in columns A-I from the source tab, and then allow users to edit columns K-O that pertain to each row that is pulled in from the source tab.

The problem is I also want to sort the range I am pulling in from the source tab by a status column, on a daily basis. And I want the full row (including manually entered col K-O) to also shift order when I sort so they stay tied to the values I'm pulling in from the source. I can't use the SORT function because then only columns A-I will adjust.

I also can't filter the source data because the criteria in column K is a person assignment, and if I change the order in the source data it would change the person assignment, rendering the entire sheet useless.

So I added filters to the top row of the non-source tab and was planning to just run a macro that unfiltered and then re-filtered the sheet and set up a trigger so it runs automatically on a daily basis. However, when I attempt to filter the sheet Z --> A, the row with the filter function moves and messes the entire sheet up.

Is there anyway to lock the FILTER formula so that it doesn't move when I reorder the sheet, but the row values do shift?

Or is there a way to insert a blank row below the FILTER function so that I can add filters to that row and not the actual row with the formula in it.

I'm open to any other suggestions as well this has been a huge source of frustration for me.

1
sortinggoogle-apps-scriptgoogle-sheetsfilter

1 Answers

0
votes

use script:

var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getSheetByName("Sheet2");                                   //SHEET NAME HERE
var range = sheet.getRange("A2:Z");                                        //RANGE HERE

function onEdit(e)  {
range.sort([{column: 4, ascending: false}, {column: 1, ascending: true}]); //SORT CRITERIA
}