1

I currently use a macro script to filter my sheet upon opening the sheet by column A, and I wondered if there was a more efficient/simple way to do it with scripts?

  function Sort() {
  var spreadsheet = SpreadsheetApp.getActive();
  spreadsheet.getRange('3:138').activate();
  spreadsheet.getRange('3:138').createFilter();
  spreadsheet.getRange('A3').activate();
  spreadsheet.getActiveSheet().getFilter().sort(1, true);
  spreadsheet.getActiveSheet().getFilter().remove();
};

Link: https://docs.google.com/spreadsheets/d/1qpoTEE1bJdGt1rVVAETvh2kGG5EQy4wB1dYnhiTefA4/edit?usp=sharing

1
  • you can try like this in script editor:

    var ss = SpreadsheetApp.getActiveSpreadsheet();
    var sheet = ss.getSheetByName("AGL");
    var range = sheet.getRange("A4:C");

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

  • 1
    That works great! no lag at all! – Ceebee86 Jul 1 at 13:28
0

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 }

  • Hi Arul, apologies, it's working now I believe. I just want column B to automatically sort upon opening. When I use a macro and do the manual filtering, the sheet seems to 'shudder' and have to wait a bit. I wondered if scripting would do it faster? – Ceebee86 Jul 1 at 11:05
  • Is the script you posted above one which will filter dynamically upon input into certain cells...? – Ceebee86 Jul 1 at 11:07
  • it will ask the user for input and filter using what the user has fed. It is much better than asking the user to set a filter, clear all values and click the value you want and then click ok. – arul selvan Jul 1 at 12:09
  • That's incredible, going to play around with that. :) – Ceebee86 Jul 1 at 13:29
  • Pl. accept the answer – arul selvan Jul 5 at 4:08

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.