1

I understand how to sort rows in Google sheets, but there doesn't seem to seem to be a built-in way to sort columns like their is with Excel. Can this still be done in Google sheets? I am trying to avoid using sort and transpose because it relies on having the data in two spots: raw data, and then another spot with the formula.

For example,

   A B C
1  4 3 7

If I sorted largest to smallest it would go: Column C, Column A, Column B

C West
  • 123
  • 8
  • 1
    if you want to sort `in place` then I think the current answer is your only choice. You can't have both a formula and a value in a cell. You have to choose one or the other. – Marios Jan 28 '21 at 16:56

1 Answers1

2

You are trying to sort the rows in place, and spreadsheet formulas can't do that as it will cause circular dependency.

I recommend using Apps Script instead:

function myFunction() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var lastColumn = sheet.getLastColumn();

  // Sort 1st row (B1 - E1)
  var firstValues = sheet.getRange(1, 2, 1, lastColumn - 1).getValues().flat();
  sheet.getRange(1, 2, 1, lastColumn - 1).setValues([sortWithIndices(firstValues)]);

  // Using loop, sort 2nd - 10th row based on the 
  // movement of indices when sorting the 1st row
  var i = 2;  
  var lastRow = 10;
  while(i <= lastRow) {
    var values = sheet.getRange(i, 2, 1, lastColumn - 1).getValues().flat();
    var tempValues = sheet.getRange(i, 2, 1, lastColumn - 1).getValues().flat();
    for (var index = 0; index < values.length; index++) {
      values[index] = tempValues[firstValues.sortIndices[index]];
    }
    sheet.getRange(i, 2, 1, lastColumn - 1).setValues([values]);
    i++;
  }
}

function sortWithIndices(toSort) {
  for (var i = 0; i < toSort.length; i++) {
    toSort[i] = [toSort[i], i];
  }
  toSort.sort(function(left, right) {
    // < means ascending
    // > means descending
    return left[0] < right[0] ? -1 : 1;
  });
  toSort.sortIndices = [];
  for (var j = 0; j < toSort.length; j++) {
    toSort.sortIndices.push(toSort[j][1]);
    toSort[j] = toSort[j][0];
  }
  return toSort;
}

It got very different from simply sorting per row since we are now sorting other rows based on the first row.

So basically, I created a function that is saving the changes in indices after sorting the first row (firstValues.sortIndices). This is an array that contains the index changes when the first row was sorted.

After getting that, it becomes easier as we only need to swap the values of the succeeding rows (2-10) based on the firstValues.sortIndices.

It happens inside the for loop.

Sample Output:

sample output

This is one of the possible answers I can think off, and is actually the simplest to do but lengthy to apply.

One possible answer to this is swapping the whole columns instead and I do think that would be better and faster than this.

Reference:

NightEye
  • 10,634
  • 2
  • 5
  • 24
  • Hey @Marios, thanks for your input, can you expound why `reverse()` is not sorting in descending order? I was wondering since when I tested it on a larger array, it still does the descending order right. Updated the answer to show you my test case. Also linking the performance difference between the `sort().reverse()` vs the one you recommended I saw on SO. https://stackoverflow.com/a/52030227/14606045 – NightEye Jan 28 '21 at 16:45
  • ahh yes, my bad, I did update it since I forgot to include the `sort()` in it. – NightEye Jan 28 '21 at 16:47
  • Thank you to you both. If I wanted the data in rows 2-10 to following the sorting of row 1, would I still use setvalue? – C West Jan 28 '21 at 16:58
  • Hi @CWest, I recommend it to be in loop. Updated the answer for the loop. – NightEye Jan 28 '21 at 17:19
  • Hi @CWest, If your question was solved, please push the accept button. Other people who have the same problem with you can also base your question as a question which can be solved. If you don't find the button, feel free to tell me. stackoverflow.com/help/accepted-answer – NightEye Jan 28 '21 at 17:29
  • Hi @NaziA, I don't think I explained it too clearly. My objective is sort the top row, and have the data below follow the top row. That probably still doesn't make sense so I put together a before & after on Sheets, https://docs.google.com/spreadsheets/d/1Zx24oyoz7O4WVFjhuCAgj2Ri9J7v_CEUvqWCSR7ggJc/edit#gid=0. I think you're on the right track as your script is sorting the data. It should base the order of the columns on the first row, but then move the entire column to its place in the order. I apologize if that still doesn't make sense. – C West Jan 28 '21 at 18:27
  • Sorry for misunderstanding that @CWest, I have updated the answer. – NightEye Jan 28 '21 at 19:08
  • Your code worked! Thank you. I was able to successfully integrate it. I was not familiar with sortWithIndices so I can't wait to dive into that. – C West Jan 28 '21 at 22:32