Sorting not working when more than 20 columns

Posted by: bsnthr on 5 July 2022, 7:09 pm EST

  • Posted 5 July 2022, 7:09 pm EST


    when having a table with more than 20 columns the sorting functionality via the filter dialog does not work anymore.

    By using this example: the filtering works as we have just a six columns.

    The column definition looks like this:

    salesData: [
    ["SalesPers", "Birth", "Region", "SaleAmt", "ComPct", "ComAmt"],
    ["Joe", new Date("2000/01/23"), "North", 260, 0.1, 26],
    ["Robert", new Date("1988/08/21"), "South", 660, 0.15, 99],
    ["Michelle", new Date("1995/08/03"), "East", 940, 0.15, 141],
    ["Erich", new Date("1994/05/23"), "West", 410, 0.12, 49.2],
    ["Dafna", new Date("1992/07/21"), "North", 800, 0.15, 120],
    ["Rob", new Date("1995/11/03"), "South", 900, 0.15, 135],
    ["Jonason", new Date("1987/02/11"), "West", 300, 0.17, 110],
    ["Enana", new Date("1997/04/01"), "West", 310, 0.16, 99.2],
    ["Dania", new Date("1997/02/15"), "North", 500, 0.10, 76],
    ["Robin", new Date("1991/12/28"), "East", 450, 0.18, 35]

    But when adding more columns to the table the sorting via the filter dialog does not work anymore.

    The columns definition now looks like this with 25 columns:

    salesData: [
    ["SalesPers", "Birth", "Region", "SaleAmt", "ComPct",
    "ComAmt", "SalesPers1", "Birth1", "Region1", "SaleAmt1",
    "ComPct1", "ComAmt1", "1SalesPers", "1Birth", "1Region",
    "1SaleAmt", "1ComPct", "1ComAmt", "1SalesPers1", "B1irth1",
    "R1egion1", "Sal1eAmt1", "Co1mPct1", "ComA1mt1", "ComA1mt12"
    ["Joe", new Date("2000/01/23"), "North", 260, 0.1, 26, "Joe", "Joe", "Joe", "Joe", 26, "Joe", "Joe", "Joe", "Joe", 26, "Joe", "Joe", "Joe", "Joe", 26, "Joe", "Joe", "Joe", "Joe"],
    ["Robert", new Date("1988/08/21"), "South", 660, 0.15, 99, "Joe", "Joe", "Joe", "Joe", 26, "Joe", "Joe", "Joe", "Joe", 26, "Joe", "Joe", "Joe", "Joe", 26, "Joe", "Joe", "Joe", "Joe"],
    ["Michelle", new Date("1995/08/03"), "East", 940, 0.15, 141, "Joe", "Joe", "Joe", "Joe", 26, "Joe", "Joe", "Joe", "Joe", 26, "Joe", "Joe", "Joe", "Joe", 26, "Joe", "Joe", "Joe", "Joe"],
    ["Erich", new Date("1994/05/23"), "West", 410, 0.12, 49.2, "Joe", "Joe", "Joe", "Joe", 26, "Joe", "Joe", "Joe", "Joe", 26, "Joe", "Joe", "Joe", "Joe", 26, "Joe", "Joe", "Joe", "Joe"],
    ["Dafna", new Date("1992/07/21"), "North", 800, 0.15, 120, "Joe", "Joe", "Joe", "Joe", 26, "Joe", "Joe", "Joe", "Joe", 26, "Joe", "Joe", "Joe", "Joe", 26, "Joe", "Joe", "Joe", "Joe"],
    ["Rob", new Date("1995/11/03"), "South", 900, 0.15, 135, "Joe", "Joe", "Joe", "Joe", 26, "Joe", "Joe", "Joe", "Joe", 26, "Joe", "Joe", "Joe", "Joe", 26, "Joe", "Joe", "Joe", "Joe"],
    ["Jonason", new Date("1987/02/11"), "West", 300, 0.17, 110, "Joe", "Joe", "Joe", "Joe", 26, "Joe", "Joe", "Joe", "Joe", 26, "Joe", "Joe", "Joe", "Joe", 26, "Joe", "Joe", "Joe", "Joe"],
    ["Enana", new Date("1997/04/01"), "West", 310, 0.16, 99.2, "Joe", "Joe", "Joe", "Joe", 26, "Joe", "Joe", "Joe", "Joe", 26, "Joe", "Joe", "Joe", "Joe", 26, "Joe", "Joe", "Joe", "Joe"],
    ["Dania", new Date("1997/02/15"), "North", 500, 0.10, 76, "Joe", "Joe", "Joe", "Joe", 26, "Joe", "Joe", "Joe", "Joe", 26, "Joe", "Joe", "Joe", "Joe", 26, "Joe", "Joe", "Joe", "Joe"],
    ["Robin", new Date("1991/12/28"), "East", 450, 0.18, 35, "Joe", "Joe", "Joe", "Joe", 26, "Joe", "Joe", "Joe", "Joe", 26, "Joe", "Joe", "Joe", "Joe", 26, "Joe", "Joe", "Joe", "Joe"]

    Please see the updated example here:

    Is this a known bug?

    Any help is appreciated. Thanks.
  • Replied 6 July 2022, 5:26 pm EST


    It seems like you are not defining the the row filter correctly. If you examine the sample that is shared by you, you can see that you are defining the HideRowFilter for the range
    new spreadNS.Range(2, 1, salesData.length - 1, salesData[0].length)

    salesData[0].length is 25, but the column count of the sheet is 20. You either need to modify the range of the HideRowFilter to be less than the sheet's column count or you can increase the column count of the sheet.

    You can refer to the modified sample that I have created for you. In the sample, I just increase the column count of the sheet to salesData[0].length + 1(one is added to include the "A" column). Now, it works as expected:

    API Docs:

    getColumnCount method:

    setColumnCount method:

    I hope this clarifies the issue. Please let us know if you need further assistance on this query. We would be happy to help you.

  • Replied 6 July 2022, 5:26 pm EST

  • Marked as Answer

    Replied 1 August 2022, 2:27 am EST

    Thank you very much, Ankit.
Need extra support?

Upgrade your support plan and get personal unlimited phone support with our customer engagement team

Learn More

Forum Channels