How to Custom Sort Text in Google Sheets using Apps Script

Learn how to effectively custom sort your data in Google Sheets with Apps Script, making it easier to manage and analyze information.
---
This video is based on the question https://stackoverflow.com/q/74060687/ asked by the user '00jay' ( https://stackoverflow.com/u/20234476/ ) and on the answer https://stackoverflow.com/a/74061757/ provided by the user 'doubleunary' ( https://stackoverflow.com/u/13045193/ ) at 'Stack Overflow' website. Thanks to these great users and Stackexchange community for their contributions.

Visit these links for original content and any more details, such as alternate solutions, latest updates/developments on topic, comments, revision history etc. For example, the original title of the Question was: How to custom sort text in Google Sheet App Scripts?

Also, Content (except music) licensed under CC BY-SA https://meta.stackexchange.com/help/licensing
The original Question post is licensed under the 'CC BY-SA 4.0' ( https://creativecommons.org/licenses/by-sa/4.0/ ) license, and the original Answer post is licensed under the 'CC BY-SA 4.0' ( https://creativecommons.org/licenses/by-sa/4.0/ ) license.

If anything seems off to you, please feel free to write me at vlogize [AT] gmail [DOT] com.
---
How to Custom Sort Text in Google Sheets using Apps Script

Sorting data is a fundamental task every Google Sheets user encounters. However, sometimes the default sorting options don't meet our needs—especially when you want to sort data based on a specific order of text values.

If you find yourself in a position where you need to custom sort data in Google Sheets, this guide is for you. Here, we'll walk through a straightforward solution—using Google Apps Script, paired with a handy formula that streamlines your sorting process.

The Problem

Imagine you have a column that contains statuses such as "Interview Scheduled", "Waitlisted", or "Rejected". You want to sort this column in a specific order rather than alphabetically. Sorting manually every time is cumbersome. How can you automate this process in Google Sheets using Apps Script?

The Solution

To achieve the desired sorting in Google Sheets using Apps Script, follow these comprehensive steps:

Step 1: Create a Helper Column

Put this formula in row 3 of a free column (let’s say column “N” for this example):

[[See Video to Reveal this Text or Code Snippet]]

Explanation:

The ARRAYFORMULA function allows the formula to extend over a range of cells instead of one by one.

The SWITCH function evaluates the status in column C and assigns a corresponding prefix (like "01 ", "02 ", etc.) to it.

This numeric prefix helps in sorting the data according to your specific requirements.

Step 2: Modify Your Sorting Script

Next, update your Apps Script to sort based on the new helper column. Here’s how you can modify the script:

[[See Video to Reveal this Text or Code Snippet]]

Key Points:

In the range.sort() method, 14 represents the index of the new helper column (column N).

The other numbers (4, 10, 2) indicate the indices of other columns to be sorted in a specified order.

Be aware of the correct column numbers to ensure the rest of the sorting process remains as desired.

Step 3: Auto-Sorting upon Edit

To ensure that your data remains sorted even after making edits, you can use the onEdit function. Here's an example of how it looks:

[[See Video to Reveal this Text or Code Snippet]]

What This Does:

The function checks if the edit occurred starting from row 3 downwards.

If true, it calls the autoSort() function (where your sorting logic is implemented).

Additional Tip:

If the helper column is simply a temporary solution for sorting and clutters your sheet, you can easily hide it after you're done.

Conclusion

Custom sorting in Google Sheets can greatly enhance your productivity, especially when working with non-alphabetic status values. By mixing Google Apps Script with a helper column formula, you can create a dynamic, automated sorting solution that meets your needs.

Ready to Simplify Your Sorting Process?

Set up this customized sorting solution today to make managing your data more efficient!

Видео How to Custom Sort Text in Google Sheets using Apps Script канала vlogize