Sorting a Repeating Section

It is a very reasonable desire to want to change how the rows of a repeating section are sorted on the sheet. While reasonable, unfortunately, it’s not advisable to attempt this.

There is an official function, setSectionOrder, but this is a buggy function and not recommended (as described on that wiki link). The only way to attempt this is to delete all the existing rows and replace them with the same rows in the order you want.

This post will demonstrate how to do that. But generally speaking, you should not do this – let sheet users manually resort rows as they desire. That method is safe.

The Basic HTML

Let’s say you have a repeating section that contains a potentially massive spell list, and you want to let the user sort by the spell name, class, or level. You want to user to change the sorting on demand.

Here’s one way you might go about it.

Sorting The Rows

In the HTML above, the sort occurs whenever you change the value in the select dropdown. You might instead limit to run only when a button is clicked. Either way, the code then needs to:

• get a list of the row IDs (getSectionIDs is made for this)
• Copy the existing entries – all of them
• Sort those entries in the order you want.
• Paste those new entries into the table.
• Delete the original entries.

If the deletion is kept to last, you reduce the chance of data loss. Otherwise, a disconnection could cause the entire section contents to be lost!

Here is a sheet worker you might use, based on the limited section above. The fields array at the start must include the name of every attribute within the row of the section.

    const fields = ['name', 'class', 'level'];
    on('change:spell_sort', (event) => {
        if(!fields.includes(event.newValue)) {
            return;
        }
        getSectionIDs('repeating_spells', idarray => {
            const fieldnames = [];
            // get the name of EVERY attribute on every row.
            idarray.forEach(id => fields.forEach(field => fieldnames.push(`repeating_spells_${id}_${field}`)));
            
            getAttrs([...fieldnames, 'spell_sort'], v => {
                const spell_sort = v.spell_sort;
                let allrows = [];
                // need to go throw every row and get the values, and store them with out the id
                idarray.forEach(id => {
                    // get all the value of every attribute in a row, and story it in object with the field name
                    const thisrow = {};
                    fields.forEach(field => thisrow[field] = v[`repeating_spells_${id}_${field}`]);
                    allrows = [...allrows, thisrow];
                });

                /* at this point we have all the attributes from the repeating section in an array of objects that looks something like
                [
                    {name: 'a spell', class: 'cleric', level: 3},
                    {name: 'another spell', class: 'cleric', level: 3},
                    {name: 'yet another spell', class: 'wizard', level: 1},

                ]
                now we need to sort the array. The Underscore function _.sortBy is easiest here
                */

                allrows = _.sortBy(allrows, spell_sort);

                // now we create a new output function
                const output = {};
                allrows.forEach(row => {
                    //need to create a new id for each row, and create new complete repeating section attribute names for each field
                    const rowid = generateRowID();
                    fields.forEach(field => output[`repeating_spells_${rowid}_${field}`] = row[field]);
                });
                // now we have a sorted copy of the rows we can add to the repeating section. But we also need to delete the old set.

                // finally update the sheet. If a connection error happens between the previous row and next row, the entire repeating section is lost forever.
                setAttrs(output, {silent:true}, function() {
                    idarray.forEach(id => removeRepeatingRow(`repeating_spells_${id}`));
                });
            });
        });
    });Code language: JavaScript (javascript)

Putting the deletion in the setAttrs, after sorted rows are added to the section, should hopefully eliminate the data loss possibility. The risk of data loss is very small, and might be mitigated completely by the way the worker is built, but I don’t know. There is an official function that you should be able to use, setSectionorder, but you shouldn’t use it – it is too buggy. So there is no good way to do this.

If you do use this, it would be a good idea to look into an undo function which saves all data in the section before you change it. But if you you want such a function, this is a good starting point.