The file you shared is not accessible.
It's better to make a copy of your sheet and share that copy publicly with edit access.
It's not clear what you want to achieve.
You are setting a filter
You are sorting
You are removing the filter
You can set a filter by app-script like below ( https://stackoverflow.com/questions/18457235/enable-data-filters-on-a-range-with-google-apps-script )
function applyFilter() {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var dataSheet = ss.getActiveSheet();
var toFilter = dataSheet.getDataRange();
var filter = toFilter.createFilter();
// Make some criteria to filter with.
var fcb = SpreadsheetApp.newFilterCriteria();
/* use FilterCriteria methods */
fcb.whenCellNotEmpty();
// Filter the range based on the 1st column:
filter.setColumnFilterCriteria(1, fcb.build());
}
I tried the below script to set a filter dynamically by asking the user to feed the value - It will filter out all other values
function filter_by_id(){
//for production people to enter contractor info
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sh = ss.getActiveSheet();
var activesheet=SpreadsheetApp.getActiveSpreadsheet().getActiveSheet().getName();
if (activesheet!=="Bill") {Browser.msgBox('Pl. click this option from "Bill" sheet only');return;};
//var dept = SpreadsheetApp.getUi().prompt("Enter Dept CTPT(C), Electrical(E), Bus(B), Assembly(A), VCB(V), LBS(L), Transformer(T)").getResponseText();
var id = SpreadsheetApp.getUi().prompt("Enter ID exactly").getResponseText();
var billsheet = ss.getSheetByName("Bill");
var filterRange = sh.getRange('C1:C'+sh.getLastRow()).getValues(); //Get L column values
var hidden = getHiddenValueArray(filterRange,[id]); //get values except id
//var filtercriteria = SpreadsheetApp.newFilterCriteria().setHiddenValues(hidden).build();
//billsheet.getFilter().setColumnFilterCriteria(3, filtercriteria);
var filterSettings = {};
// optional arguments: startRowIndex, startColumnIndex, endRowIndex, endColumnIndex
filterSettings.range = {
sheetId: ss.getSheetByName("Bill").getSheetId() // provide your sheetname to which you want to apply filter.
};
filterSettings.criteria = {};
var columnIndex = 2; // column that defines criteria [A = 0]
filterSettings['criteria'][columnIndex] = {
'hiddenValues': [ hidden ]
};
var request = {
"setBasicFilter": {
"filter": filterSettings
}
};
Sheets.Spreadsheets.batchUpdate({'requests': [request]}, ss.getId());
}
function __getHiddenValueArray(colValueArr,visibleValueArr){ //exact match
var flatUniqArr = colValueArr.map(function(e){return e[0];})
.filter(function(e,i,a){return (a.indexOf(e.toString())==i && visibleValueArr.indexOf(e.toString()) ==-1); })
return flatUniqArr;
}
function getHiddenValueArray(colValueArr,visibleValueStr){
//any part match
var newArray= []
for (var i = 0; i < colValueArr.length; i++) {
if(colValueArr[i].toString().toLowerCase().indexOf(visibleValueStr.toString().toLowerCase()) == -1){newArray.push(colValueArr[i]);}
}
return newArray
}