Converting VBA code to google sheets script for auto sort

Question

I am looking for assistance on a script for google sheets for an auto sort function that runs when I update the sheet with new information. The sheet name is "Summary All Events". I have data starting in Row 4, Columns A:J. I would like to auto sort any data from A4:J1000 by column 1 (A) in ascending order. Can anyone help me?

Here is the script I am running in VBA for Excel. But now I need to convert it into a script for Google Sheets.

Public Sub Worksheet_Change(ByVal Target As Excel.Range)

If Target.Column = 1 Then

    Dim lastRow As Long
    lastRow = Cells(Rows.Count, 1).End(xlUp).Row
    Range("A4:J" & lastRow).Sort Key1:=Range("A4:A" & lastRow), Order1:=xlAscending, Header:=xlNo

End If

End Sub

Kishan Kishan · Accepted Answer · 2017-09-02T09:06:18

Try the following code:

function onEdit(e) {
  var sheet = e.source.getActiveSheet();
  if (sheet.getName() == 'Sheet1') {
    var r = e.range;
    if (r.columnStart == 1 && r.rowStart >= 4)
      sheet.getRange('A4:J').sort({column:1,ascending:true});
  }
};

Now, whenever any user will edit any cell in range "A4:A", the above code will auto sort the range A4:J by column A in ascending order.

Converting VBA code to google sheets script for auto sort

1 Answers