Forum Discussion
B_H332200
Nov 03, 2021Copper Contributor
Help with Excel Script
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 ...
B_H33
Nov 03, 2021Copper Contributor
That only sorts column A alphabetically and also moved my headers.
JKPieterse
Nov 04, 2021Silver Contributor
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).
- B_H332200Nov 08, 2021Copper ContributorI'm getting this error:
Line 4: RangeSort apply: You cannot perform the requested operation.
Doesn't appear to like A1- JKPieterseNov 09, 2021Silver Contributor
B_H332200 I tested my script on a table like this and it works without error:
- B_H332200Nov 09, 2021Copper ContributorOk, 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