Search code examples
javascriptgoogle-sheetsgoogle-apps-scriptcustom-function

How to get a list of all sheet names that contain "week of" that updates on edit

Im new to Apps Script and need some assistance with the code required to get a list of all sheet names (current and those to be created) that will start with "Week of" in the title. Ideally this would update as new sheets are created (either on edit or on open)

The code below is what I have so far, but need it to filter the sheets and only show those starting with "week of" as well as have it update when new sheets are added

/**
 * Returns the names of all sheets.
 * 
 * @return The name of all sheets in the Google Sheets spreadsheet.
 * @customfunction
 */
function ALLSHEETNAMES() {
  let ss = SpreadsheetApp.getActive();
  let sheets = ss.getSheets();
  let sheetNames = [];
  sheets.forEach(function (sheet) {
    sheetNames.push(sheet.getName());
  });
  return sheetNames;
}

If found some code (see below) that will not count the first x number of sheets, but i'm not certain that this will remain the case as the document grows so would rather find something that will use the "week of" filter vs a set number of sheets to ignore.

function sheetNameArray() {
var out = new Array()
var sheets = SpreadsheetApp.getActiveSpreadsheet().getSheets();
for (var i=6; i<sheets.length ; i++) out.push( [ sheets[i].getName() ] )
return out 
}
Solution

  • Get the Value of 'Week Of'

    Your Code is almost there, You just add a condition where you use toLowerCase() and includes() to get the desired output.

    Script used:

     /**
 * Returns the names of all sheets containing 'week of'.
 * 
 * @return The name of all sheets all sheets containing 'week of' in the Google Sheets spreadsheet.
 * @customfunction
 */

function getAllWeekOfInSheetNames() {
  let ss = SpreadsheetApp.getActive();
  let sheets = ss.getSheets();
  let sheetNames = [];
  sheets.forEach(function (sheet) {
    if(sheet.getName().toLowerCase().includes('week of')){
      sheetNames.push(sheet.getName());
    }
  });
  return sheetNames;
}

    Reference: