1

This question is an evolution of this other question.

I have a Google sheet (Form responses) with the following structure:

| Timestamp | Field 1 | Field 2 | Field 3 | Field 4 |   Field 5 |
|           |         |         |         |         |    Luke   |
|           |         |         |  Adam   |         |           |
|           |         |         |  Zak    |         |           |
|           |         |         |         |         |    Beth   |
|           |         |         | Jonas   |         |           |

I wish to have a script that sorts the rows but considers the contents of two columns (4 and 6 in the example) combined. Field 3 and Field 5 are never filled-in on the same row. The header row should not be sorted.

To be clear this is the result I'm after:

| Timestamp | Field 1 | Field 2 | Field 3 | Field 4 | Field 5   |
|           |         |         |  Adam   |         |           |
|           |         |         |         |         |    Beth   |
|           |         |         |  Jonas  |         |           |
|           |         |         |         |         |    Luke   |
|           |         |         |  Zak    |         |           |

At the moment, I'm using this script which sorts the content of the columns SEPARATELY.

function autoSort(sheet) {
  var NUMBER_OF_HEADER_ROWS = 1;
  var range = sheet.getDataRange();
  if (NUMBER_OF_HEADER_ROWS > 0) {
    range = range.offset(NUMBER_OF_HEADER_ROWS, 0);
  }
  range.sort([{column: 3, ascending: true}, {column: 5, ascending: true}]);
}

function onOpen(e) {
  var sheet = e.source.getActiveSheet(); // get the default sheet when the spreadsheet opens
  autoSort(sheet);
}
1
  • I've updated the original script for the new requirements in this answer below. I'll try to add some comments later when I get a break. Change the user variables at the top to match your needs (column numbers, sheet name, skiprows)
    – Blindspots
    Commented Jul 9 at 20:36

2 Answers 2

1

Add combined sort keys in column G1:G with this formula in cell G1:

=arrayformula(D1:D & F1:F)

Then sort the range by [{ column: 6, ascending: true,},].

5
  • This duplicated all content in between the two columns without combining the two values in row G.
    – To Do
    Commented Jul 9 at 18:50
  • Since there is always one of the cells that is empty I modified the formula to =arrayformula(IF(D1:D="",F1:F,D1:D)) worked.
    – To Do
    Commented Jul 9 at 19:01
  • The formula worked as a standalone (=D1 & F1), but not in the arrayformula.
    – To Do
    Commented Jul 9 at 19:25
  • That is what I tried. For some reason it duplicated the columns instead of combining the contents of the two columns. Anyway, thanks, because you put me on the right track.
    – To Do
    Commented Jul 9 at 19:43
  • The formula in the answer doesn't output two columns but one column that combines the contents of the two source columns row by row. If you see another column fill up, chances are that there is another formula in that extra column. Commented Jul 9 at 20:07
1

'Merge Sort Columns' Script

The container-bound script below sorts a sheet's data range using a temporary column created from merging multiple user-specified, and then discards it when done.

The script doesn't overwrite any of the original sheet values and instead relies on the temporary helper column and the range.sort() method.

User Constants

  1. sheetName
    Name of sheet to be sorted (eg. "Sheet1", "Form Responses 1", etc.).
  2. keyColumns
    Column(s) whose values will be combined into a single sort column. Can be any number.
  3. skipRows
    Zero or more rows from the top of the sheet to exclude from being sorted.

Notable Behaviors

  1. Multiple Keys in Row:
    The key that sorts first ascending is used for that row.

  2. Valid Keys are Alphanumeric:
    A key can be a number or text string and can contain punctuation and spaces between strings.

  3. Row's Number Used If Key Not Present:
    If no key is found for a data row the row number will instead be assigned and it will sort above any keys that start with letters.

  4. Padding is Ignored:
    JavaScript trim() is used to exclude preceding and trailing spaces, and spaces in otherwise empty cells. Spaces within strings are not affected. For example:

    Original Value After Trim Impact                                                                                       
    [   One_love   ]  [One_love] Padding removed
    [One love !] [One love !]  No change
    [ ] [] Becomes empty value

onOpen() Behavior

When the spreadsheet is opened, the script is triggered and also a menu is added to the spreadsheet so it can be manually triggered as needed.

Script

const sheetName = "My Form Responses";
const keyColumns = [4, 6];     // columns with ordering values  
const skipRows = 1;            //  0 = none;  3 = skip rows 1-3, etc. 

function autoSort() {
  const sheet = SpreadsheetApp.getActive().getSheetByName(sheetName);
  const range = sheet.getDataRange();
  const rows = range.getLastRow();
  const cols = range.getLastColumn();
  const sortRows = rows-skipRows;
  const helperCol = range.offset(skipRows, cols, sortRows, 1);  
  const helperRange = range.offset(skipRows, 0, sortRows, cols+1); 
  const values = helperRange.getValues();

  function sortKey(v, i, idx=[]){
    keyColumns.forEach(c => (str = v[c-1].toString().trim()) && idx.push(str));
    return [(!idx[0] && i+skipRows) || idx.sort()[0]];
  };

  values.forEach((v, i) => values[i] = sortKey(v,i));
  
  helperCol.setValues(values);
  helperRange.sort(cols+1);
  helperCol.clearContent(); 
}

function onOpen(e) {
  autoSort();
  SpreadsheetApp.getActive().addMenu(
    "↪️ SORT '" + sheetName + "'",
    [{ name: "🡆 Click here to confirm",
       functionName: "autoSort" }]
  );
}

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.