Javascript Question posted in
A very good W3school tutorial can be found here.

Javascript – 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 
}
Tags:

3

Answers


  1. 0

    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:

    Login or Signup to reply.
  2. 0

    Get the value of "Week of" | Not Case sensitive

    I have different approach getting the value of Week of.
    It’s not case sensitive whether you wanted to get the value of "Week Of", "WEEk OF" ,"week of" or etc

    I used regex

    here’s the code:

    function ALLSHEETNAMES() {
  let ss = SpreadsheetApp.getActive();
  let sheets = ss.getSheets();
  let sheetNames = [];
  sheets.forEach( sheet => {
    sheetNames.push(sheet.getName());
  });
  
  const regex = /bweeks+ofb/i;
  const matchedSheets = [];

  for(let i = 0; i < sheetNames.length; i++){
    if(regex.test(sheetNames[i])){
      matchedSheets.push(sheetNames[i]);
    }
  }

  return matchedSheets;

}

    Reference:

    Login or Signup to reply.
  3. 0

    To get the sheetNames starting with "Week of":

    let sheetNames = sheets.filter((sheet)=> sheet.startsWith('Week of'))
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search