0

Is there a Google Sheets script that would be able to transpose my data, then sort it, then transpose it back. I need to do this formula below - but as a script.

=transpose(sort(transpose(J1:N4),1,))

My intention is to be able to sort my header columns A->Z so I can have a consistent placement of header columns every time I put them in. For example, sometimes the data comes as column A:G but the next time, the headers that used to be A:G are now AGBFCDE (scrambled). Sorting the entire columns going down is pretty strait forward but I need to sort A1:G1 while keeping the data below it still associated with the proper column header. For example, it would be "move entire column left or right" until the column headers are sorted A->Z. I am hoping this method of transposing-sorting-transposing back will do the trick but other recommendations would be welcome.

column 1   column4  column5  column 2   column 3
data 1     data 4   data 5   data 2     data 3
data 1     data 4   data 5   data 2     data 3
data 1     data 4   data 5   data 2     data 3

So as you can see above, the column headers are not in order. Every time I put them in, they will not be but I would like to know if there is preferably a script or some way to then sort Column 1:Column 5 in order even though they will never be pasted that way. If there is a better way besides transposing/sorting then I would be open to any method.

JonK
  • 49
  • 7

1 Answers1

1

Here I created a script with the same concept of Transpose -> Sort -> Transpose.

function myFunction() {

  // get all the data in the sheet
  var ss  = SpreadsheetApp.getActiveSheet();
  var range = ss.getDataRange();
  var values = range.getValues();
  
  //convert rows into columns
  var data = transpose(values);

  //sort data
  data.sort(
  function(a,b) {
   return b[0]-a[0] || a[0].localeCompare(b[0]);
  });

  //convert columns into rows
  var data1 = transpose(data);

  //write data back to spreadsheet
  ss.getRange(1,1,values.length,values[0].length)
    .setValues(data1);
}

function transpose(matrix) {
  return matrix[0].map((col, i) => matrix.map(row => row[i]));
}

Example

Before

enter image description here

After

enter image description here

References

Transposing a 2D-array in JavaScript

JavaScript Sorting Arrays

Nikko J.
  • 5,319
  • 1
  • 5
  • 14
  • I am getting an error saying that the number of rows in data does not match number of rows in range on line 22. Not sure if it would help to mention that it is on the first sheet and columns A:G. I'm sure there is an easy fix to point it to the correct range but I couldn't figure out what went wrong. – JonK Dec 08 '20 at 19:21
  • 1
    @JonK - I updated my comment above and change the number of columns and rows in the last two parameters of getRange function. – Nikko J. Dec 08 '20 at 20:08
  • That works thank you! Just a follow on question that I can post to the community but you know it would be appreciated. I have the following getrange and I need to add 2 ranges but it will not work. It will be used to take what we just transposed/sorted and move it to another tab without a specific column. sheet1.getRange("I1:K"+sheet1.getLastRow(),"M1:O"+sheet1.getLastRow()).getValues(). I tried comma, &, + but nothing works to combine. Basically need the following without column L - sheet1.getRange("I1:O"+sheet1.getLastRow()).getValues(). – JonK Dec 08 '20 at 21:01
  • @JonK - It would be better if you create a separate post for it so that the community will better understand the scenario. You can also link this post in your new question so people can see the context. And lastly, feel free to accept and upvote my answer if you feel it was useful to you. – Nikko J. Dec 08 '20 at 21:29
  • This worked fine for sorting columns by the first row. Thanks. A couple of comments: 1) I got a Desc sort initially. Had to switch the sort function parameters to get Asc. 2) colors, text format, comments are notes stay in their original cell position. They are NOT moved with the data. If you use any of those, apply them AFTER sorting. – Henry Jul 15 '23 at 18:18