How to Sort Google Sheets Automatically with Apps Script

If you are working with a Google Sheet that has a large number of tabs, it can be difficult to find the sheet you need if they are not organized properly. Use Google Apps Script to sort sheets automatically

This Google Spreadsheet on Udemy courses has about 50 sheets, one for each programming language, and the sheets are sorted in random order so it is difficult to find a specific sheet.

Sort Google Sheets

It will take a while to sort the worksheets manually but we can easily automate the process with Google Apps Script and easily navigate through large spreadsheets.

Automate Sheet Sorting with Google Apps Script

The following code snippet will automatically sort the worksheets in a Google Sheet alphanumerically. The script can arrange the sheets in either ascending or descending order based on the sheet names.

To get started, go to Extensions > Apps Script to open the script editor. Then, copy and paste the following code:

const sortGoogleSheets = (ascending = true) => {
  const options = {
    sensitivity: 'base',
    ignorePunctuation: true,
    numeric: true,
  };

  const compareFn = (sheet1, sheet2) => {
    return ascending
      ? sheet1.getName().localeCompare(sheet2.getName(), undefined, options)
      : sheet2.getName().localeCompare(sheet1.getName(), undefined, options);
  };

  // Get the active spreadsheet.
  const ss = SpreadsheetApp.getActiveSpreadsheet();

  ss.getSheets()
    .sort(compareFn)
    .reverse()
    .forEach((sheet) => {
      ss.setActiveSheet(sheet);
      ss.moveActiveSheet(1);
    });

  // Flush the changes to the spreadsheet.
  SpreadsheetApp.flush();
};

The compareFn function compares two sheets and returns a value that indicates whether the first sheet should come before or after the second sheet. The function returns the following values:

  • -1 if the first sheet should come before the second sheet.
  • 1 if the first sheet should come after the second sheet.

Advanced Sort Options

const options = {
  sensitivity: 'base',
  ignorePunctuation: true,
  numeric: true,
};

The options object specifies the options for the locale comparison. Here are some important things to know:

  • The numeric property specifies whether numbers should be treated as numbers instead of strings. If this property is set to false, “Sheet1” and “Sheet10” will come before “Sheet2”.

  • The ignorePunctuation property specifies whether spaces, brackets and other punctuation should be ignored during the comparison. If this property is set to false, “Sheet 1” and “Sheet1” will be treated as different sheets.

  • The sensitivity property specifies if the comparison should be case-sensitive or case-insensitive. Set this property to “accent” to treat base letters and accented characters differently (Sheet a and Sheet à will be treated as different sheets).

Sort Google Sheets by Date

If your sheet names contain dates, like “March 2023” or “01/03/23”, you’ll need to convert the dates to numbers before comparing them.

const compareFn = (sheet1, sheet2) => {
  return ascending
    ? new Date(sheet1.getName()).getTime() - new Date(sheet2.getName()).getTime()
    : new Date(sheet2.getName()).getTime() - new Date(sheet1.getName()).getTime();
};

References

Amit Agarwal

Amit Agarwal

Google Developer Expert, Google Cloud Champion

Amit Agarwal is a Google Developer Expert in Google Workspace and Google Apps Script. He holds an engineering degree in Computer Science (I.I.T.) and is the first professional blogger in India.

Amit has developed several popular Google add-ons including Mail Merge for Gmail and Document Studio. Read more on Lifehacker and YourStory

0

Awards & Titles

Digital Inspiration has won several awards since it's launch in 2004.

Google Developer Expert

Google Developer Expert

Google awarded us the Google Developer Expert award recogizing our work in Google Workspace.

ProductHunt Golden Kitty

ProductHunt Golden Kitty

Our Gmail tool won the Lifehack of the Year award at ProductHunt Golden Kitty Awards in 2017.

Microsoft MVP Alumni

Microsoft MVP Alumni

Microsoft awarded us the Most Valuable Professional (MVP) title for 5 years in a row.

Google Cloud Champion

Google Cloud Champion

Google awarded us the Champion Innovator title recognizing our technical skill and expertise.

Email Newsletter

Sign up for our email newsletter to stay up to date.

We will never send any spam emails. Promise.