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

Javascript – Get sheet with name that includes the most recent date using apps script

I have multiple sheets all with names "Pipeline History mm-dd-yyyy" with different dates. I want to search the names of the sheets for the most recent date. And then hide that sheet. I have started writing some code, but I don’t even know where to start.

This is what I’ve tried to get started with:

var allSheets = ss.getSheets();
var searchText = "Pipeline History";
var pipelineHistorySheets = allSheets.filter(s => s.getSheetName().includes(searchText));
Tags:

2

Answers


  1. 0

    Try something like this:

    I have seven sheets named Sheet0 through Sheet6 and I named three them like this
    Sheet0 03-01-2024, Sheet1 02-01-2024, Sheet2 01-01-2024 the others are just named Sheet3, Sheeet4, Sheet5 and Sheet6. I used the following code to find the sheet with the most recent date. Note: the most recent date has the largest valueOf();

    function myfunk01() {
  const ss = SpreadsheetApp.getActive();
  var shts = ss.getSheets().filter(sh => sh.getName().includes("Sheet")).sort((a,b)=>{
    let ta = a.getName().split(/[ -]/g);
    let vA = ta.length > 1 ? new Date(ta[3],ta[1],ta[2]).valueOf(): 0;
    let tb = b.getName().split(/[ -]/g);
    let vB = tb.length > 1 ? new Date(tb[3],tb[1],tb[2]).valueOf(): 0;
    return vB - vA;//descending order
  }).map(sh => sh.getName());
  Logger.log(JSON.stringify(shts[0]))
  return shts[0];
}

Execution log
3:30:51 PM  Notice  Execution started
3:30:50 PM  Info    "Sheet0 03-01-2024"
3:30:53 PM  Notice  Execution completed
    Login or Signup to reply.
  2. 0

    Use a regular expression to extract the date portion in sheet names, and Array.sort() to get the most recent one, like this:

    function hideLatestSheet(ss = SpreadsheetApp.getActive()) {
  ss.getSheetByName(findLatestSheetName_(ss)).hideSheet();
}

function findLatestSheetName_(ss) {
  const sheetRegex = /^Pipeline History (dd-dd-dddd)$/i;
  const _extractDate = (s) => new Date(s.match(sheetRegex)[1]);
  return ss.getSheets()
    .map(sheet => sheet.getName())
    .filter(sheetName => sheetName.match(sheetRegex))
    .sort((a, b) => _extractDate(b) - _extractDate(a))[0];
}
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search