Nov 02 2021 07:56 PM
Hi,
I'm using the below script to sort column F in my spreadsheet however, it doesn't extend to sort the rest of the cells. Therfore it's jumbled all the info. Can someone help please?
Nov 03 2021 05:53 AM
@B_H332200 Change your script to:
function main(workbook: ExcelScript.Workbook) {
let selectedSheet = workbook.getActiveWorksheet();
// Sort the current region starting from cell F2 on selectedSheet
selectedSheet.getRange("F2").getSurroundingRegion().getSort().apply([{ key: 0, ascending: true }], undefined, false, ExcelScript.SortOrientation.rows);
}
Nov 03 2021 04:32 PM
Nov 04 2021 03:31 AM
@B_H33 Hmm, I wasn't expecting the current region to include columns to the left of F2.
What about this then:
function main(workbook: ExcelScript.Workbook) {
let selectedSheet = workbook.getActiveWorksheet();
// Sort the current region starting from cell A1 on selectedSheet
selectedSheet.getRange("A1").getSurroundingRegion().getSort().apply([{ key: 5, ascending: true }], undefined, true, ExcelScript.SortOrientation.rows);
}
The "key: 5" bit in this code denotes the sixth column from the left (column F).
Nov 08 2021 03:37 PM
Nov 09 2021 01:26 AM
@B_H332200 I tested my script on a table like this and it works without error:
Nov 09 2021 03:32 PM
Nov 10 2021 01:33 AM - edited Nov 10 2021 01:34 AM
@B_H332200 I'm afraid OfficeScript can only do those things Excel on-line allow you to do (for the moment). Since you cannot protect/unprotect worksheets with Excel on-line, there is no way to add this. However, if you unlock the range of cells that need to be sorted and then protect the worksheet in Desktop Excel and check the "Sort" box, the script will work.
You can call a script using Power Automate. There should be a way to schedule the script too. Here is an article about Power Automate.
https://jkp-ads.com/Articles/Excel-office-script-call-from-power-automate.asp
The difference with your timed script is the first step that you take when setting up the Flow. See attached GIF.
Nov 12 2021 12:07 AM
Nov 12 2021 02:20 AM