Google Sheets – Sorting Columns with Script Instead of Macro

google sheetsgoogle-apps-scriptsorting

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

Best Answer

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

Code

function mySQL(ref, dates) {
  var output = [];
  for(var i = 0, iLen = ref.length; i < iLen; i++) {
    var user = ref[i][0], begin = new Date(ref[i][1]), end = new Date(ref[i][2]);
    for(var j = 0, jLen = dates.length; j < jLen; j++) {
      var date = new Date(dates[j]);
      if(date >= begin && date <= end) {
        output.push([date, user]);
      }
    }
  }
  return output;  
}

Screenshot

data
enter image description here

outcome
enter image description here

Example

I've added the script to your example file.

Google-sheets – why is one row skipped when including ranges from two sheets

The data is there, but it's out of sight because of the blank rows that are included due to the use of whole columns (A2:B). Filter the rows where A or B cells are not empty. One way to do that is by using QUERY(), i.e.:

=QUERY({{'source data 1'!A2:B};{'source data 2'!A2:B}},
"Select Col1,Col2 Where (Col1<>'' OR Col2<>'') Order by Col1",0)

Best Answer

Related Solutions

Google Sheets – How to Make SQL CROSS JOIN of Two Tables

Code

Screenshot

Example

Google-sheets – why is one row skipped when including ranges from two sheets

Related Topic