Help with Excel Script

[B_H332200]

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?

function main(workbook: ExcelScript.Workbook) {

  let selectedSheet = workbook.getActiveWorksheet();

  // Sort the range range F2:F1048576 on selectedSheet

  selectedSheet.getRange("F2:F1048576").getSort().apply([{ key: 0, ascending: true }], undefined, false, ExcelScript.SortOrientation.rows);

}

[#2]

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

[#3]

That only sorts column A alphabetically and also moved my headers.

[#4]

@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).

[#5]

I'm getting this error:
Line 4: RangeSort apply: You cannot perform the requested operation.

Doesn't appear to like A1

[#6]

@B_H332200 I tested my script on a table like this and it works without error:

[#7]

Ok, I can see it works when the spreadsheet is unlocked.
I need to keep my spreadsheet locked - is there an addition to the code for this please?

Also, how can I run the automation on a timer?

Thanks,
Brooke

[#8]

@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.

[#9]

Thanks for all your help Jan.
Unfortunately I'll have to wait until Excel Online improves on functionality before I can use the script.
Appreciate your help very much.
Regards,
Brooke

[#10]

Hi Brooke,
It pays off to regularly check because Microsoft adds new features to Office Script on a regular basis.