Forum Discussion

B_H332200's avatar
B_H332200
Copper Contributor
Nov 03, 2021

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

 

Office Scripts

9 Replies

  • JKPieterse's avatar
    JKPieterse
    Silver Contributor
    Nov 03, 2021

    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);
}
    • B_H33's avatar
      B_H33
      Copper Contributor
      Nov 03, 2021
      That only sorts column A alphabetically and also moved my headers.
      • JKPieterse's avatar
        JKPieterse
        Silver Contributor
        Nov 04, 2021

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

Resources