Auto sort script on google sheets onChange trigger

Question

Can anyone help me please, it's regarding auto sort script for google sheets? I'm not familiar with script actually, but what I'm trying to do is to auto sort the dates entered on column A on my table range. I found a generic script below which I tried on my google sheets and it actually works on manual edit on the same sheet. However, the values on my table range are results of my (Index,Match) formula from another sheet so whenever a new value is entered from another sheet it will not automatically get sorted.

Here's the sample script which I tried on my google sheets:

function onEdit(event){
  var sheet = event.source.getActiveSheet();

  var editedCell = sheet.getActiveCell();

  var columnToSortBy = 1;
  var tableRange = "A2:J61"; // What to sort.
  if(editedCell.getColumn() == columnToSortBy){   
    var range = sheet.getRange(tableRange);
    range.sort( { column : columnToSortBy, ascending: true});
  }
}

Can anyone guide me to modify this script, please...

Here's the link to sample google sheet which I'm trying to work on:

https://docs.google.com/a/alarmpromotions.org/spreadsheets/d/1IHPwNpAklt_5R6OyxTHMxMbXKkT4WsPBk20saUPbxgw/edit?usp=sharing

Auto sort will only work if I manually edit the column on the same sheet (sheet1), however the values on sheet 1 are results of the formula with basis from sheet 2. — marby

Rajeev Ranjan Rajeev Ranjan · Accepted Answer · 2017-08-05T10:37:33

Try this onEdit with the auxiliary function to update first sheet when this is triggered for the second sheet.

function onEdit(){
  var sheet = SpreadsheetApp.getActiveSheet(); 
  var editedCell = sheet.getActiveCell();

  var columnToSortBy = 1;
  var tableRange = "A2:J61"; // What to sort.

  if(editedCell.getColumn() == columnToSortBy){   
    var range = sheet.getRange(tableRange);
    range.sort( { column : columnToSortBy, ascending: true});

    var ss = SpreadsheetApp.getActiveSpreadsheet();
    if(ss.getSheets()[1] == sheet ){
       sortFirstSheet();
    }
  }
}


function sortFirstSheet(){
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  SpreadsheetApp.setActiveSheet(ss.getSheets()[0]);

  var sheet = SpreadsheetApp.getActiveSheet();
  var range = sheet.getRange("A2:J61");
  range.sort( { column : columnToSortBy, ascending: true});
}

Take a look at these APIs

Auto sort script on google sheets onChange trigger

1 Answers