How to make this script run in all tabs except certain tabs (Sheets)?

user_12211138 0
Viewed 37

I'm a total newbie when it comes to scripts and I honestly don't really use it often at all but I thought it'd be fun to automatize an alphabetical order sorting I wanted to do and so I used this script:

/** Build a menu item
From https://developers.google.com/apps-script/guides/menus#menus_for_add-ons_in_google_docs_or_sheets 
**/

function onOpen(e) {
  var menu = SpreadsheetApp.getUi().createMenu('Sort'); 
if (e && e.authMode == ScriptApp.AuthMode.NONE) {
    // Add a normal menu item (works in all authorization modes).
        menu.addItem('Sort Sheet', 'sort');
  } else {
    // Add a menu item based on properties (doesn't work in AuthMode.NONE).
    var properties = PropertiesService.getDocumentProperties();
    var workflowStarted = properties.getProperty('workflowStarted');
    if (workflowStarted) {
          menu.addItem('Sort Sheet', 'sort');
    } else {
          menu.addItem('Sort Sheet', 'sort');
  }
   menu.addToUi();
  }
}

function sort() {
  
  /**  Variables for customization:
  
  Each column to sort takes two variables: 
      1) the column index (i.e. column A has a colum index of 1
      2) Sort Asecnding -- default is to sort ascending. Set to false to sort descending
  
  **/

  //Variable for column to sort first
  
  var sortFirst = 3; //index of column to be sorted by; 1 = column A, 2 = column B, etc.
  var sortFirstAsc = true; //Set to false to sort descending
  
  //Variables for column to sort second
 
  var sortSecond = 1;
  var sortSecondAsc = true;
  
  //Number of header rows
  
  var headerRows = 1; 

  /** End Variables for customization**/
  
  /** Begin sorting function **/

  var activeSheet = SpreadsheetApp.getActiveSheet();
  var sheetName = activeSheet.getSheetName(); //name of sheet to be sorted
  var sheet = SpreadsheetApp.getActive().getSheetByName(sheetName);
  var range = sheet.getRange(headerRows+1, 1, sheet.getMaxRows()-headerRows, sheet.getLastColumn());
  range.sort([{column: sortFirst, ascending: sortFirstAsc}, {column: sortSecond, ascending: sortSecondAsc}]);

}

It worked very well but I wondered if there was a way to not have it work in two specific tabs of the same sheets?

google-apps-script google-sheets sorting
1 Answers

I believe your goal as follows.

  • You want to execute the script of sort for the sheets except for the specific sheets.

In this case, how about declaring the excluded sheet names and checking the current sheet using the excluded sheet names? When this is reflected to your script, it becomes as follows.

Modified script:

In this case, please set the sheet names you want to exclude to excludeSheetNames. At sample script, when the active sheet is "Sheet1" and "Sheet2", the script below the if statement is not run.

function sort() {
  var excludeSheetNames = ["Sheet1", "Sheet2"]; // <--- Added

  var sortFirst = 3;
  var sortFirstAsc = true;
  var sortSecond = 1;
  var sortSecondAsc = true;
  var headerRows = 1;
  var activeSheet = SpreadsheetApp.getActiveSheet();
  var sheetName = activeSheet.getSheetName();

  if (excludeSheetNames.includes(sheetName)) return; // <--- Added

  var sheet = SpreadsheetApp.getActive().getSheetByName(sheetName);
  var range = sheet.getRange(headerRows + 1, 1, sheet.getMaxRows() - headerRows, sheet.getLastColumn());
  range.sort([{ column: sortFirst, ascending: sortFirstAsc }, { column: sortSecond, ascending: sortSecondAsc }]);
}
  • For example, if you want to run the script below the if statement for only excludeSheetNames, please modify if (excludeSheetNames.includes(sheetName)) return; to if (!excludeSheetNames.includes(sheetName)) return;.

Reference:

user_71086530
Related