Forum Discussion
Solved
Select entire sheet then sort on one column (automated)
Total newb here...
This should be very simple.
Automate > Record Actions
Right-Click to the left of Column A (This highlights the entire sheet)
Sort > Custom Sort
Sort on Col J ("Amount" is the heading)
The result, when done manually, is just fine.
When recorded as a script and run it only sorts Column J, not the entire sheet like when it is done manually.
function main(workbook: ExcelScript.Workbook) {
let selectedSheet = workbook.getActiveWorksheet();
// Sort the range range J2:J67 on selectedSheet
selectedSheet.getRange("J2:J67").getSort().apply([{ key: 0, ascending: true }], false, true, ExcelScript.SortOrientation.rows);
}
This is to be a 3 step process.
- Select all cells and unwrap all text. (This one works) function main(workbook: ExcelScript.Workbook) {let selectedSheet = workbook.getActiveWorksheet();// Set horizontal alignment to ExcelScript.HorizontalAlignment.general for all cells on selectedSheetselectedSheet.getRange().getFormat().setHorizontalAlignment(ExcelScript.HorizontalAlignment.general);selectedSheet.getRange().getFormat().setIndentLevel(0);// Set vertical alignment to ExcelScript.VerticalAlignment.bottom for all cells on selectedSheetselectedSheet.getRange().getFormat().setVerticalAlignment(ExcelScript.VerticalAlignment.bottom);selectedSheet.getRange().getFormat().setIndentLevel(0);// Set wrap text to false for all cells on selectedSheetselectedSheet.getRange().getFormat().setWrapText(false);// Set text orientation to 0 for all cells on selectedSheetselectedSheet.getRange().getFormat().setTextOrientation(0);// Indent set to 0 for all cells on selectedSheetselectedSheet.getRange().getFormat().setIndentLevel(0);}
- Sort on column J (Amount) and delete the zero-dollar amount rows. (The delete is currently done manually, though some manner of "IF/THEN" would probably automate the process.) This one does not work as recorded.function main(workbook: ExcelScript.Workbook) {let selectedSheet = workbook.getActiveWorksheet();// Sort the range range J2:J67 on selectedSheetselectedSheet.getRange("J2:J67").getSort().apply([{ key: 0, ascending: true }], false, true, ExcelScript.SortOrientation.rows);}
- Re-sort by Customer then Description, then Date. (Col B, F, E) This one shuffles the columns around. It's quite odd.function main(workbook: ExcelScript.Workbook) {let selectedSheet = workbook.getActiveWorksheet();// Custom sort on range range A1:K68 on selectedSheetselectedSheet.getRange("A1:K68").getSort().apply([{key: 1, ascending: true}, {key: 5, ascending: true}, {key: 4, ascending: true}], false, true, ExcelScript.SortOrientation.columns);}All 3 of these steps work perfectly when done manually. Recording the actions to a script produces crazy results. Any help is appreciated.
I'm not sure how recorder works, the script could be
function main(workbook: ExcelScript.Workbook) { const range = workbook .getActiveWorksheet() .getUsedRange() const sortedColumnIndex = range .getRow(0) // get first row, i.e. headers .find("Amount", { completeMatch: true }) .getColumnIndex() range.getSort().apply( // apply to entire range [{ ascending: true, // sort order key: sortedColumnIndex // on column to sort }], false, //case insensitive true, // range with headers ExcelScript .SortOrientation .rows // sort by rows ) }We select only range with data, not entire sheet. Find column number for the cell with "Amount". Apply sorting to entire range, not to one column. Use above column as one on which we do sorting.
As for the # 1 you may simplify it a bit
function main(workbook: ExcelScript.Workbook) { const range = workbook .getActiveWorksheet() .getUsedRange() const format: ExcelScript.RangeFormat = range.getFormat() format.setHorizontalAlignment(ExcelScript.HorizontalAlignment.general) format.setVerticalAlignment(ExcelScript.VerticalAlignment.bottom) format.setWrapText(false) format.setTextOrientation(0) format.setIndentLevel(0) }As for #3 didn't catch what exactly you are trying to do. Currently sort orientation is columns. That means values in second row of the range (key=1, numbering starts from zero) are taken, and columns are placed in order according to values in that row.
Only one sorting key works.
If you'd like to sort the range by rows that's as in previous case.
Multicolumn sort could be
function main(workbook: ExcelScript.Workbook) { const range = workbook .getActiveWorksheet() .getUsedRange() const columnIndex = (header: string): number => range.getRow(0) .find(header, { completeMatch: true }) .getColumnIndex() range.getSort().apply( [ { key: columnIndex("Customer Name"), ascending: true } , { key: columnIndex("Description"), ascending: true } , { key: columnIndex("Date"), ascending: true } ], false, true, ExcelScript .SortOrientation .rows) }
I'm not sure how recorder works, the script could be
function main(workbook: ExcelScript.Workbook) { const range = workbook .getActiveWorksheet() .getUsedRange() const sortedColumnIndex = range .getRow(0) // get first row, i.e. headers .find("Amount", { completeMatch: true }) .getColumnIndex() range.getSort().apply( // apply to entire range [{ ascending: true, // sort order key: sortedColumnIndex // on column to sort }], false, //case insensitive true, // range with headers ExcelScript .SortOrientation .rows // sort by rows ) }We select only range with data, not entire sheet. Find column number for the cell with "Amount". Apply sorting to entire range, not to one column. Use above column as one on which we do sorting.
- Thank you. This worked perfectly on a clean test file, but not on my .CSV file from another app. I would say I have some non-printable characters in my data that are messing things up.
Non-printable characters also could be removed by the script, but for that desirably to know does your text includes only English alphabet or not; if not which one. If possible non-printable characters are known - even better.
