1

I have a Movie list using Google Sheets that I would like do some sorting with using a script rather than a formula.

Column A is the movie Title. I would like to be able to sort the movie titles alphabetically but ignore when a movie title BEGINS with "A", "An" and "The". For example: "The Abyss" would be filed under 'A' for "Abyss" but would still display as "The Abyss".

I would also like it to ignore a few other words that I can continue to enter as needed. For example: "Marvel's"... So that "Marvel's Iron Man" would be filed under 'I' for "Iron Man" but would still display as "Marvel's Iron Man".

I know how to do it with this formula in a new column:

=IF(LEFT(A2,2)="A ",RIGHT(A2,LEN(A2)-2),
   IF(LEFT(A2,3)= "An ",RIGHT(A2,LEN(A2)-3),
     IF(LEFT(A2,4)="The ",RIGHT(A2, LEN(A2)-4),A2)
    )
  )

But I would rather do it through a script so that I don't need to use another column for this. When someone clicks to sort movie titles A-Z, it would be nice if it ignored "A", "An", and "The" without having to use an additional column of titles. It's less messy and takes up less space.

Also, if there is a way through the script for it to first sort by Movie Title and then by release Date by default, that would be great. But of course, it would still be nice not if it weren't locked into that sorting order so that friends and family can sort as they like (by year, etc.) when browsing our catalog. This is not as important of a feature, though.

  • Did you already tried to write a script? – Rubén Sep 8 '15 at 21:03
  • I tried messing around with a few that I found online but I've had no luck so far. I'm not great with scripts unfortunately. I do ok with some tweaking of existing scripts sometimes but I'm not very good at writing them from scratch. – Computer Candy Sep 8 '15 at 21:39
  • Add those details to your question including if those scripts worked for you before you tweak them to fit your needs. – Rubén Sep 8 '15 at 21:43
  • Sorry, I should have been more clear. I didn't really find any for this purpose. The ones that I found really were not for this purpose which is why I didn't have any luck with them. I more just meant that in general I do better with a little tweaking as opposed to writing scripts from scratch. – Computer Candy Sep 9 '15 at 1:36
3

Managing expectations

Scripts cannot intercept a user's click on column name. This action will always invoke built-in search. So, if you want users to sort by clicking a column, you need a separate column for sorting.

With a separate column

You don't need a complex formula with IFs to remove articles or other words: a regular expression can be used to handle all variants at once:

= regexreplace(A1, "(?i)^(a|an|the) ", "") 

(case-insensitive replacement), or

= regexreplace(lower(A1), "^(a|an|the) ", "") 

(also converting to lowercase, so that the sorting is case-insensitive).

This is easily extended to other words: (a|an|the|Marvel's) and so on.

With a script, for formula-less sheets

But if you really don't want another column, here's a script. What it does:

  • Adds a "Sort > by Movie Title" command to the menu (when the spreadsheet is opened).
  • When invoked with this command, sorts entire sheet A>Z by the first column, ignoring beginning a, an, the and also ignoring case.
  • Does not sort by any other column (adding year, etc as a secondary criterion is up to you).

As a side effect, this script will remove any formulas from the sheet, replacing them with the current values in those cells. Which is unacceptable, unless you don't use formulas.

function sortByTitle() {
  var range = SpreadsheetApp.getActiveSheet().getDataRange(); 
  var values = range.getValues();
  values.sort(function(a,b) {return (trimmed(a[0]) < trimmed(b[0]) ? -1 : 1);});
  range.setValues(values);
}

function trimmed(str) {
  return str.toLowerCase().replace(/^(a|an|the) /, ''); 
}

function onOpen() {
  SpreadsheetApp.getActiveSpreadsheet().addMenu("Sort", [{name: "by Movie Title",  functionName: "sortByTitle"}]);
}

With a script, preserving formulas

This is a hybrid approach: it adds a new column filled with trimmed titles, invokes the method sort of the Range class, then clears the column. As a result, formulas are preserved (if your formulas make relative references to cells in other rows, sorting will still mess things up, but this is to be expected).

Here is the new sortByTitle function; the functions trimmed and onOpen remain as above.

function sortByTitle() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var height = sheet.getDataRange().getHeight();
  var width = sheet.getDataRange().getWidth();
  var titles = sheet.getRange(1, 1, height, 1).getValues();
  trimmedTitles = [];
  for (var i=0; i<titles.length; i++) {
    trimmedTitles.push([trimmed(titles[i][0])]);
  }
  var newColumn = sheet.getRange(1, width+1, height, 1);
  newColumn.setValues(trimmedTitles);
  sheet.getRange(1, 1, height, width+1).sort(width+1);   // see comment below
  newColumn.clear();
}

If your spreadsheet contains a header row (which is likely), replace the line with the comment with

  sheet.getRange(2, 1, height-1, width+1).sort(width+1);

so that the top row is excluded from sorting.

  • Thank you. I wound up using the regexreplace formula in place of the IF formula I had been using. It's much more simple. Unfortunately, I have lots of other formulas in the sheet so the script is not an option if it will strip other formulas from the sheet. I'm still hoping for a script option but if this is the best I can get in the next few days, I'll mark it as the best answer. Thank you. – Computer Candy Sep 9 '15 at 6:46
  • 1
    I added a solution for preserving formulas. – user79865 Sep 9 '15 at 22:36
  • 1
    Using the 3rd method, it's easy to add sorting by a secondary column: see documentation of range.sort() – user79865 Sep 9 '15 at 23:11
  • Sorry for the super late reply. I had lost track of this after I wound up using an array formula so I only needed one formula in a second header row instead of in every row. But I love the idea of using your first solution as an array since it's much more simple than my formula. Can it be easily edited as an array? And I love the idea of using a script instead to eliminate the sorting column all together. Using the addition you gave for headers, which part do I need to edit to allow for a second or third header row? – Computer Candy Oct 27 '16 at 4:46

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.