Select entire sheet then sort on one column (automated)

[mikemilstead]

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.

1. 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 selectedSheet
     selectedSheet.getRange().getFormat().setHorizontalAlignment(ExcelScript.HorizontalAlignment.general);
     selectedSheet.getRange().getFormat().setIndentLevel(0);
     // Set vertical alignment to ExcelScript.VerticalAlignment.bottom for all cells on selectedSheet
     selectedSheet.getRange().getFormat().setVerticalAlignment(ExcelScript.VerticalAlignment.bottom);
     selectedSheet.getRange().getFormat().setIndentLevel(0);
     // Set wrap text to false for all cells on selectedSheet
     selectedSheet.getRange().getFormat().setWrapText(false);
     // Set text orientation to 0 for all cells on selectedSheet
     selectedSheet.getRange().getFormat().setTextOrientation(0);
     // Indent set to 0 for all cells on selectedSheet
     selectedSheet.getRange().getFormat().setIndentLevel(0);
   }
2. 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 selectedSheet
     selectedSheet.getRange("J2:J67").getSort().apply([{ key: 0, ascending: true }], false, true, ExcelScript.SortOrientation.rows);
   }
3. 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 selectedSheet
     selectedSheet.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. 

[#2 · Accepted solution]

@mikemilstead 

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.

[#3]

@mikemilstead 

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.

[#4]

@mikemilstead 

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)
}

[#5]

@SergeiBaklan 

As for step 3, it is a multi column sort:

[#6]

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.

[#7]

@mikemilstead 

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)
}

[#8]

@mikemilstead 

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.

[#9]

Perfect!
Thank you for all your help.

[#10]

@mikemilstead , you are welcome