Google Sheets App Script: Automatically sort tabs based on custom order

Question

We use Google Sheets for concert production. A TEMPLATE tab serves as template for a given concert. An ALL SHOWS tab serves as a summary. Each time a new concert is planned, we duplicate TEMPLATE and name it according to the concert date (e.g. 01/01, 04/12), which we then move along the tab bar at the appropriate location.

In-between concert tabs are MONTHS tabs (e.g. JANUARY, FEBRUARY)

After tweaking a little with App Script, I was wondering whether anyone had a smart insight into how I could automatically sort newly created concert tabs along the tab bar based on a custom order.

Our custom sort order would look something like:

• HELPER (to be left in place)

• TEMPLATE (to be left in place)

• ALL CONCERTS (to be left in place)

• JANUARY

• 01/01

• 02/01

• [...]

• 31/01

• FEBRUARY

• 01/02

• 02/02

• [...]

• 28/02

• MARCH

• 01/03

• [...]

• HOLIDAYS (Occasional tab that should also be left in place)

Thank you very much for your time!

R

Answer 1

SUGGESTION

For sorting sheet tabs, the only way to do this in Apps Script is by using its moveActiveSheet() method. What I have done on my sample script below is that I gathered all the current tabs information into an array, sorted them out based on your custom sort order using a combination of .forEach, .map, .filter & .sort methods & then rearranged them.

Sample Script [UPDATED]

function main() {
  const sheet = SpreadsheetApp.getActive();
  const months = ["January", "February", "March", "April", "May", "June", "July", "August", "September", "October", "November", "December"];
  var res = [];
  var mainDates = [];

  //This will sort the sheet tabs by month name and date in ascending order ?
  sheet.getSheets().map((data, i) => {
    return (months.join(' ').toUpperCase().includes(data.getName())) ?
      [(parseInt(months.map((element, index) => { if (element.toUpperCase() == data.getName()) return index }).join('')) + 1), data.getName(), (i + 1)] :
      (/^[a-zA-Z]+$/.test(data.getName().replace(/\s+/g, ''))) ? res.unshift(['', data.getName(), '']) : mainDates.push([indetifyDate(data.getName(), months), data.getName(), (i + 1)])
  }).
    filter(x => { return x.length > 1 ? x : null }).sort(function (a, b) { return a[0] - b[0] }).
    forEach(month => {
      res.push(month);
      mainDates.sort(function (a, b) { return a[1].slice(0, 2) - b[1].slice(0, 2) }).
        forEach(data => { return data[0] == month[0] ? res.push(data.join(',').split(',')) : null })
    });
  for (var x in res) res[x][1] == "HOLIDAYS" ? res.push(res.splice(x, 1)[0]) : 0;console.log(res)
  arrangeTabs(res, sheet);
}

//This function will assign an indetifier for each tab that is in date form (e.g. 01/01) for sorting?
function indetifyDate(data, months) {
  var curDate = new Date(data.split('/')[1] + "/" + data.split('/')[0]).getMonth() + 1;
  return months.map((_, index) => { if ((index + 1) == curDate) return (index + 1) }).filter(x => x).join('');
}

//This function will move sheets tabs based on their order from process above ?
function arrangeTabs(res, sheet) {
  res.forEach((data, pos) => {
    sheet.setActiveSheet(sheet.getSheetByName(data[1]))
    sheet.moveActiveSheet((pos + 1));
  })
}

1. Script will get all the tab names (in original positions) & places them into an array
2. Then, it will iterate through the array & filter the tabs with monthly names (e.g. JANUARY) using the first ternary condition
3. Then the next ternary condition will filter the non-date format and will unshift them to be at the beginning of the res array variable & the remaining tabs in dd/mm format will be further sorted and filtered thoroughly based on the months they belong to before being added to the res variable.
4. Lastly, the HOLIDAYS tab will be placed at the end of the res array variable
5. Once the res array has been arranged accordingly, the function arrangeTabs will then arrange them on the actual spreadsheet.

Demonstration

• After running the main function, the tabs get sorted as seen below: