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

}

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

}

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.

Total newb here…This should be very simple.Automate > Record ActionsRight-Click to the left of Column A (This highlights the entire sheet)Sort > Custom SortSort 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 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);}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);}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. Read More

Cart

Cart