Simultaneously sort multiple ranges on a single sheet in varying orders

Question

I am trying to find a way using a script if possible (or formula) that will allow me to reduce the number of operations that I have perform to sort data in a particular google spreadsheet. Currently, I have to individually sort my required ranges. Since there are multiple types of data that share the same column I am not able to sort by column, also I have found sorting ranges by header is not a viable solution.

For example I need a way of performing the following actions.

Range: B3-C9, Sort By Column C, Sort: Z-A

Range: H3-I9, Sort By Column I, Sort A-Z

Range: H13-I19, Sort By Column I, Sort Z-A

Range: N13-O19, Sort By Column O, Sort Z-A

Range: N23-O29, Sort By Column O, Sort Z-A

Range: T23-U29, Sort By Column U, Sort A-Z

Is it possible to create a script to to perform these actions with a minimum of clicks? I have a little experience writing scripts, but if someone could point me in the right direction on how to do it I would be very grateful. If however, someone has a template script with the functionality I'm after, I am sure I can modify it to my requirements.

Answer 1

An easy but clunky way to do this is to do it on another sheet.

E.g. On sheet2 you have =sort() commands that reference sheet 1.

Leave room.

E.g. Sheet2:B3 has the formula =sort(Sheet1!B3:C9,2,FALSE)

Better: Define named ranges for each of the sources. This allows you to reference the named ranges in sheet2. When you extend one in Sheet1, you don't have to change sheet2, unless you collide with another range.

If you add a row IN the range on sheet1, the ranges that include that row update automatically. The area used by the sorted ranges on sheet 2 does not update, so you will eventually get collision errors as tries to stomp on the next block.

You can put these on the same sheet too.

Thus the =sort goes to a blank part of the sheet to the right of the top of the block it's sorting. This will create room as rows are added. The downside of this is a spread out layout that requires lots of spacing

E.g.

B3  C3   {bunch of other columns}        Q3=sort(B_Range,2,FALSE)
B4  C4
B5  ...

You can make a new sheet and on this one, create a formula A1=Q1 and replicate that down and across. This will give you the effect of having a separate tab for the sorted data.

You can hide the sorted data on the first sheet if you wish.