How one can Kind Google Sheets Robotically with Apps Script

This Google Spreadsheet on Udemy programs has about 50 sheets, one for every programming language, and the sheets are sorted in random order so it’s tough to discover a particular sheet.

It can take some time to kind the worksheets manually however we will simply automate the method with Google Apps Script and simply navigate by means of massive spreadsheets.

Automate Sheet Sorting with Google Apps Script

The next code snippet will mechanically kind the worksheets in a Google Sheet alphanumerically. The script can prepare the sheets in both ascending or descending order primarily based on the sheet names.

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

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

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

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

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

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

The compareFn perform compares two sheets and returns a price that signifies whether or not the primary sheet ought to come earlier than or after the second sheet. The perform returns the next values:

• -1 if the primary sheet ought to come earlier than the second sheet.

• 1 if the primary sheet ought to come after the second sheet.

Superior Kind Choices

const choices = {
  sensitivity: "base",
  ignorePunctuation: true,
  numeric: true,
};

The choices object specifies the choices for the locale comparability. Listed below are some essential issues to know:

• The numeric property specifies whether or not numbers must be handled as numbers as a substitute of strings. If this property is ready to false, “Sheet1” and “Sheet10” will come earlier than “Sheet2”.

• The ignorePunctuation property specifies whether or not areas, brackets and different punctuation must be ignored in the course of the comparability. If this property is ready to false, “Sheet 1” and “Sheet1” will probably be handled as completely different sheets.

• The sensitivity property specifies if the comparability must be case-sensitive or case-insensitive. Set this property to “accent” to deal with base letters and accented characters in another way (Sheet a and Sheet à will probably be handled as completely different sheets).

Kind Google Sheets by Date

In case your sheet names comprise dates, like “March 2023” or “01/03/23”, you’ll have to convert the dates to numbers earlier than evaluating 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