Sorting columns with script instead of macro

Question

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

Answer 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}]);
  }

Answer 2

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 }