Scripting a sorted spreadsheet in Google Docs with ignored rows

0
votes

I have a Google Spreadsheet with two separate sheets. The first one is just a big list of names and data, and the second is supposed to be a sorted listing of all the data on the first sheet (sorted by, say, last name). Here is the current way I am defining the second sheet:

=sort(sheet1!A:L, 2, TRUE)

Which works fine for the most part, except for one issue: in sheet1, the first row is a header row that consist of "First Name", "Last Name", "Phone", etc. When I use that formula for sheet2, that row gets sorted in with the rest of them.

How can I change the assignment so that the sorting ignores the first row of sheet1, and sorts the columns A to L with the same behavior? Manually specifying the length is unfeasible as entries may be rapidly added and removed in the future.

2
google-apps-scriptgoogle-sheets

2 Answers

2
votes

You can use another range definition, that specifies the first cells but not the end. e.g.

A1: =ArrayFormula(Sheet1!A1:L1)
A2: =Sort(Sheet1!A2:L, 2, TRUE)

A1 formula is just to copy the headers :)
In A2, you specify that you want the data only (2nd row and forth) by placing the 2 in A2:L.

1
votes

Here's a generic script that will autosort based on the 1st column, and assumes a Header row.

To create a script:

In the menu, go to Tools -> Script Editor... In the empty code window, paste the following code, which will run automatically whenever a cell is edited:

 /**
 * Automatically sorts the 1st column (not the header row) Ascending.
 */
function onEdit(event){
  var sheet = event.source.getActiveSheet();
  var editedCell = sheet.getActiveCell();

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

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