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

      • B_H332200's avatar
        B_H332200
        Copper Contributor
        Nov 08, 2021
        I'm getting this error:
        Line 4: RangeSort apply: You cannot perform the requested operation.

        Doesn't appear to like A1

Resources