How to Sort Google Sheets Automatically with Apps Script

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.

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

• localeCompare() method
• Intl.Collator API

source:https://ift.tt/VGnvb1p