We use Google Sheets for concert production. A TEMPLATE tab serves as template for a given concert. 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:

• JANUARY
• 01/01
• 02/01
• [...]
• 31/01
• FEBRUARY
• 01/02
• 02/02
• [...]
• 28/02
• MARCH
• 01/03
• [...]

Thank you very much for your time!

R

CodePudding user response：

Try

function reOrderTabs() {
  var ss = SpreadsheetApp.getActiveSpreadsheet()
  const tabs = ss.getSheets().map(sh => sh.getName())
  const monthes = listOfMonthes()
  var list = []
  tabs.forEach(t => {
    try {
      if (monthes.indexOf(t) == -1) {
        list.push([t, '2022-'   t.split('/')[1]   '-'   t.split('/')[0]])
      }
      else {
        list.push([
          t,
          Utilities.formatDate(new Date('2022/'   (monthes.indexOf(t)   1)   '/'   15), Session.getScriptTimeZone(), "yyyy-MM")   '-00'
        ])
      }
    }
    catch (e) { list.push([t, 'z']) }
  })
  list = list.sort(function (a, b) {
    return (''   a[1]).localeCompare(b[1]);
  })
  list.forEach((t, i) => { 
    ss.setActiveSheet(ss.getSheetByName(t[0]));
    ss.moveActiveSheet(i   1)
  })
}

function listOfMonthes() {
  return [...Array.from(Array(12).keys(), x => Utilities.formatDate(new Date(2022, x, 15), Session.getScriptTimeZone(), "MMMM").toUpperCase())]
}

CodePudding user response：

SUGGESTION

For sorting sheet tabs, the only way to do this in Apps Script is by using its