0

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

| Timestamp | Field 1 | Field 2 | Field 3 | Field 4 |
|           |         |         |         |    4    |
|           |         |         |     2   |         |
|           |         |         |     1   |         |
|           |         |         |         |    5    |
|           |         |         |     3   |         |

I wish to have a script that sorts the rows but considering the contents of two columns (3 and 4 in the example) combined. Field 3 and 4 are never filled-in in the same row.

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

| Timestamp | Field 1 | Field 2 | Field 3 | Field 4 |
|           |         |         |     1   |         |
|           |         |         |     2   |         |
|           |         |         |     3   |         |
|           |         |         |         |    4    |
|           |         |         |         |    5    |

Is it possible? If not, I would consider a script that would copy the contents of Field 4 in Field 3 IF field 3 is empty and then sort. I'm open to other suggestions.

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: 9, ascending: true}, {column: 12, ascending: true}]);
}

function onOpen(e) {
  var sheet = e.source.getActiveSheet(); // get the default sheet when the spreadsheet opens
  autoSort(sheet);
}
7
  • I assume that this isn't the Sheet that is linked to the form but an additional one? You shouldn't normally mess with the source form results. Can you confirm either way?
    – Blindspots
    Commented Jul 7 at 17:11
  • It is the sheet linked to the form. I'm only sorting the entries and have been doing this for a long time.
    – To Do
    Commented Jul 7 at 17:51
  • It's better to make the code to match the data sample. As the number of header rowa is harcoded the Is no need to use if. It's not clear how the result of your script Is diffferent from the sample provided Commented Jul 7 at 22:30
  • 1
    @ToDo 1. I rolled back your Q because it isn't appropriate to change requirements if the change would invalidate an otherwise good answer. 2. In this case, please create a new question with the new requirements. The new question should be self-contained but can link back to this one if it provides useful context. Please include any other details that may affect a solution.
    – Blindspots
    Commented Jul 8 at 16:42
  • When you post the new question comment here so that I'm notified and will know to post the modified script there.
    – Blindspots
    Commented Jul 8 at 21:02

1 Answer 1

1

Here is a script that sorts the range using a temporary helper column with the same number of populated rows as your data.

This approach doesn't directly manipulate the values in your cells like if one were to extract all the values and then overwrite the entire range with them after sorting.

Instead, your data is left untouched and the original values are sorted using values added to a helper column outside your data range and then, once sorted the new values are removed.

User Constants

Specify the following constants at the top of the script:

  1. sheetName
    Name of the specific sheet that should be sorted (eg. "Sheet1", "Form Responses 1", etc.). It doesn't need to be in the first position in the spreadsheet.
  2. sortInfoColumns
    Two (or more) column numbers that contain the values you want to be combined into a sort column
  3. rowsToBeSkipped
    The number of rows to be skipped. Such as to accommodate labels or headings. Can be any number including 0, and the rows are counted off from the top of the sheet.

Menu

On open, the script adds a menu item with the sheetName to the spreadsheet so it can be triggered manually if needed.
Script menu item

Script

const sheetName = "My Form Responses";
const sortInfoColumns = [4, 5];   // columns with ordering info  
const rowsToBeSkipped = 1;        //  0 = none;  3 = skip rows 1-3 etc.
 
function autoSort() { 
  const sheet = SpreadsheetApp.getActive().getSheetByName(sheetName);
  const range = sheet.getDataRange();
  const values = range.getValues();
  const rows = range.getLastRow();
  const newCol = range.getLastColumn() + 1;
  const sortColumn = sheet.getRange(1, newCol, rows);     
  const newRange = sheet.getRange(1, 1, rows, newCol); 
 
  values.forEach((v, i) => { 
    values[i] = [((i >= rowsToBeSkipped)
    && sortInfoColumns.reduce((a, c) => a = Math.max(a, v[c-1]), 0)) || i];
  });  

  sortColumn.setValues(values);
  newRange.sort(newCol);
  sortColumn.clearContent();  
}

function onOpen(e) {
  autoSort();
 
  SpreadsheetApp.getActive().addMenu(
    "โ†ช๏ธ ๐’๐Ž๐‘๐“ '" + sheetName + "'",
    [{ name: "๐Ÿก†โ€ƒ๐˜Š๐˜ญ๐˜ช๐˜ค๐˜ฌ ๐˜ฉ๐˜ฆ๐˜ณ๐˜ฆ ๐˜ต๐˜ฐ ๐˜ค๐˜ฐ๐˜ฏ๐˜ง๐˜ช๐˜ณ๐˜ฎ",
       functionName: "autoSort" }]
  );
}
3
  • 1
    This isn't working maybe because the content of the columns to be used for sorting order are alphanumeric and not numbers. The temporary column should contain the concatenated value of the two cells, since one of the two cells is always empty. I ran the original script after manually entering =CONCAT(E3,F3), etc. and setting the script to sort with the new column and the script works. So your script could do this operation automatically. Could you modify it? I'm not sure I understand the logic behind the sortInfoColumns.reduce line so I don't know how to modify it myself.
    – To Do
    Commented Jul 8 at 15:02
  • 1
    I modified the example to clarify the question. Also, the two columns are not adjacent to each other. In reality they are column 9 and 12.
    – To Do
    Commented Jul 8 at 15:15
  • I added a comment on the question. I'm happy to address this in a separate question.
    – Blindspots
    Commented Jul 8 at 16:43

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.