How to transpose (rotate) entire Google Sheets sheet?

Question

My sheet is 123 rows and 93 columns and is very complicated: there are lots of grouped/hidden rows, grouped/hidden columns, notes in cells, comments in cells, complex formulas (including ones that are custom-coded in Apps Scripts), conditional formatting, etc.

I've realized (unfortunately this late) that I should have designed the spreadsheet rotated 90º such that the row headers and column headers are swapped.

I'm dreading the painful, slow, error-prone task of trying to rebuild this entire sheet.

What would be an automated way to do it?

P.S. 2 reasons for wanting to rotate: 1) the dimensions that are currently my 123 row headers are relatively static, but the data that makes up my (current) column headers will continue to increase over time. I think the web and mobile apps handle vertical scrolling better than horizontal scrolling. 2) Google Sheets supports Data Filters and similar features with that orientation in mind.

P.P.S. I'm already aware of the transpose cell function, and I'm already using it as a workaround, but I'd really prefer the real data to be transposed, especially since managing conditional formatting and row/col groupings on a separate sheet that uses the transpose function has been annoying.

Answer 1

Select the entirety of your data then go to a new sheet where we will be placing the transposed version. Simply right click > Paste Special > Transposed.

This should carry over all conditional formatting, functions, and notes over (with the cell references being moved over as well.)

Note that this doesn't appear to work with comments, grouped rows, or hidden rows. I checked. To get those in the transposed table you can probably 1) create an App Script function for that or 2) do it manually. I think manually going from there might be the best course of action, though you be the judge of that.