CODEWITHSUNDEEP
javascriptgoogle-apps-scriptgoogle-sheets
Dave
Dave

Reputation: 513

Google Sheets script filter by sheet name

I need to get a list of sheets which contain a specific word. For example, I have 4 sheets, big tree, small tree, big house, small house and I need to get only the sheets which contains a word "tree". I tried using indexOf function but it did not help. I am new to this, any help will me appreciated. This is what I have tried so far, but it does not work.

const ss = SpreadsheetApp.getActiveSpreadsheet();
const allSheets = ss.getSheets();
const neededSheets= ["tree"];
   
const filteredListOfSheets = allSheets.filter(s=> neededSheets.lastIndexOf(s.getSheetName()) == 0);

Upvotes: 1

Views: 878

Answers (1)

Marios
Marios

Reputation: 27348

Solution:

This is what you are looking for:

function myFunction() {
  
const ss = SpreadsheetApp.getActiveSpreadsheet();
const allSheets = ss.getSheets();
const allSheets_names=allSheets.map(sheet=>sheet.getSheetName())  
const neededSheets= ["tree"];   
const filteredListOfSheetsNames = []   
neededSheets.forEach(ns =>               
        allSheets_names.forEach( (as,index) =>
       {if (as.indexOf(ns)>-1){filteredListOfSheetsNames.push(as)}}))                   
const filteredListOfSheets =  filteredListOfSheetsNames.map(name =>ss.getSheetByName(name))   
}

Explanation:

  • filteredListOfSheetsNames contains the sheet names you are looking for and filteredListOfSheets contains the sheet objects you are looking for.
  • Therefore, you can use the elements of filteredListOfSheets to apply sheet operations e.g. filteredListOfSheets[0].getSheetName().
  • Additionally, the code is flexible. Namely, you can have more items in the neededSheets array: const neededSheets= ["tree","house"];

Upvotes: 2

Related Questions