How do I get an array of Sheets that excludes certain sheets by name, ie. these sheets but NOT those IN this List?

Question

I know how to get all of the sheets from a spreadsheet. The spreadsheet will eventually get more and more sheets.

  const thisDoc = SpreadsheetApp.getActive()
  const allSheets = thisDoc.getSheets()

I have a list of string names of the two sheets in the spreadsheet that I never want to use:

 excludeList = ["template", "instructions"]

In Python I would do the following using the NOT IN keywords:

  listInclSheets = []
  for thisSheet in allSheets:
    if sheet.getName() not in excludeList:
      listInclSheets.append(thisSheet)

My Question is: How do I get a Subset of an Array excluding members based on a list of String Names of Sheets in Google Apps Script using the array.map() or array.filter() or some other cool method that is not doing it the long way?

I also know how to do it the long way using nested for loops and a boolean flag:

  const thisDoc = SpreadsheetApp.getActive()
  const arInclSheets = []
  const allSheets = thisDoc.getSheets();
  excludeList = ["template", "instructions"];
  for (thisSheet of allSheets)
  {
    var booInclude = true;
    for (anExcludeName of excludeList)
    {
      Logger.log("is "+ thisSheet.getName() + "equal to " + anExcludeName +"?");
      if (thisSheet.getName() == anExcludeName);
      {
        booInclude = false; 
      }
    }
    if ( booInclude)
    {
      arInclSheets.push(thisSheet);
    }
  }
  Logger.log(arInclSheets);
  for (thisSheet of arInclSheets)
  {
    Logger.log(thisSheet.getName());
  }
}

P.S. map functions with fancy one line coding always confuse me, so please explain them so I can get unconfused by them.

From this Post: https://stackoverflow.com/questions/72607333/exclude-sheets-from-list-of-sheet-names-with-script I was able to get the line: ```const arInclSheets = allSheets.map( sheet => sheet.getName()).filter(sheet => !excludeList.includes(sheet))``` but the arIncluSheets is an array of strings(the names) not an array of sheets. — AWoods, Oct 23 '22 at 05:53

score 1 · Accepted Answer · answered Oct 23 '22 at 06:42

1

Create a exclusion Set and filter the sheets by it's name:

const excludeSet = new Set(["template", "instructions"]);
const includedSheets = allSheets.filter(sheet => !excludeSet.has(sheet.getName()))

answered Oct 23 '22 at 06:42

TheMaster

45,448
6
62
85

That worked, In pseudo code I say out loud "using allSheets FILTER out the sheet that are NOT HAS same getName as in excludeSet." So sets have a built in method .has() ...hmmm curious. So, the only reason to use a set for the excludeSet is that arrays do not have a built in method .has() ? – AWoods Oct 23 '22 at 06:51
@AWoods Array has `.includes()`. But set is faster. Your pesudocode: We're not filtering out(what meets the criteria), but filtering "in". – TheMaster Oct 23 '22 at 08:58
The two Lines changed to use only arrays and not sets: `const excludeArray = ["template", "Instructions"];` and `const arInclSheets = allSheets.filter(sheet => !excludeArray.includes(sheet.getName()))` also worked, in my situation where the number of sheets will be well under 10e3 I don't know if I would see a any optimization differences between Arrays and Sets. It is fun to learn about Sets though. https://stackoverflow.com/questions/44243103/javascript-set-vs-array-vs-object-definition – AWoods Oct 23 '22 at 16:18

Cooper · Answer 2 · 2022-10-23T17:06:17.080

0

Get Sheets

function one() {
  const ss = SpreadsheetApp.getActive();
  const excl = ["Sheet0","Sheet1"];
  Logger.log(ss.getSheets().filter(sh => !~excl.indexOf(sh.getName())));
}

edited Oct 23 '22 at 17:06

answered Oct 23 '22 at 15:10

Cooper

59,616
6
23
54

I don't think this one works. It returns an Array of Strings, the names of the sheets; but not the array of sheet objects. – AWoods Oct 23 '22 at 16:10

How do I get an array of Sheets that excludes certain sheets by name, ie. these sheets but NOT those IN this List?

2 Answers2