0

What I want to do: sort a range of data by the active column via shortcut key(s). I do this regularly and want to automate. However, the size and location of the range changes each time I do this.

My approach

  1. Select a cell in the range of interest that's in the first row and specific column I want to sort by, call it C4
  2. Select down to last row of the range
  3. Select left and/or right to all columns of the range
  4. Get column number / address of C4 (which should be 3, since C = column 3)
  5. Sort range by column of C4

I've been able to accomplish 1. to 3. I'm struggling with 4. Below is what I've got. I suspect that if I can get 4. sorted, then then last line will accomplish 5.

function sortCol() {
  // KGF - 22,49
  // Purpose: automatically select a range of data and sort by column of active cell
  // Status - wip; haven't determined how to get col # of active cell
  // Last updated: 22-12-4

  var spreadsheet = SpreadsheetApp.getActive();
  var currentCell = spreadsheet.getCurrentCell();
  let cc1 = currentCell;
  spreadsheet.getSelection().getNextDataRange(SpreadsheetApp.Direction.DOWN).activate();
  spreadsheet.getActiveRange().getDataRegion(SpreadsheetApp.Dimension.COLUMNS).activate();

  // how to get col # of active cell??  22,49
  let cc1_col = cc1.getColumn
  console.log(cc1_col)
  Logger.log(cc1_col)
  //

 cc1.activateAsCurrentCell();
  spreadsheet.getActiveRange().sort({column: cc1_col, ascending: true});
};
Rubén
  • 34,714
  • 9
  • 70
  • 166
Keilan Freeman
  • 1
  • better provide a sample spreadsheet of what you are working with. – Ping Dec 05 '22 at 02:54
  • and to get the current active cell A1Notation, you need the ```.getActiveCell().getA1Notation()``` method within the sheet_object. – Ping Dec 05 '22 at 02:58
  • 1
    Do NOT share [spreadsheets](//meta.stackoverflow.com/a/260455)/[images](//meta.stackoverflow.com/q/285551) as the only source of data, to avoid closure of the question. Make sure to add input and expected output as **plain text table** to the question. [Click here](//webapps.stackexchange.com/a/161855) to create a table easily, which are **easier to copy/paste as well**. Also, note that [your email address can also be accessed by the public](//meta.stackoverflow.com/q/394304), if you share Google files. – TheMaster Dec 05 '22 at 05:00
  • 1
    Does this answer your question? [Convert column index into corresponding column letter](https://stackoverflow.com/questions/21229180/convert-column-index-into-corresponding-column-letter) – Tedinoz Dec 05 '22 at 06:05

0 Answers0