I'm trying to automatically hide sheets in google sheets

Question

I've set up timesheets in google sheets for my employees. Each employee has their own document, and each week at the start of the pay period, I'll make a new sheet in each document and hide the sheet from the previous week. The new sheet is named by the dates of that pay period (i.e. Feb 12 - Feb 18) I'm trying to write a script that can do this automatically for me. The script involves two functions that run on a time-based trigger. The first function is working perfectly and creates a new sheet each week at the start of the pay period with the correct dates as the name of the sheet. The second function to hide the old sheet is not working. I have the triggers staggered, so that the new sheet is created several hours before the old one should be hidden. Here is what I've tried for hiding the old sheet (the Template sheet is what is getting copied to each of the employee's documents, where B2 is the start date of the pay period and H2 is the end of the pay period).

  var ss = SpreadsheetApp.openById('TEMPLATE SHEET ID').getActiveSheet()
  var newName1 = Utilities.formatDate(ss.getRange("B2").getValue(),"CST","MMM dd");
  var newName2 = Utilities.formatDate(ss.getRange("H2").getValue(),"CST","MMM dd");

  var EMPLOYEE = SpreadsheetApp.openById('EMPLOYEE SHEET ID');
  var hideEMPLOYEE = EMPLOYEE.getSheets();
    for(var i =0;i<hideEMPLOYEE.length;i++){
    Logger.log(i);
    if(hideEMPLOYEE[i].getName()!== newName1 + "-" + newName2){
    hideEMPLOYEE[i].hideSheet();
     }
  }

Looking at your code, it looks like you are trying to hide all of the sheets except for the new one. Is that ok? Also, in the example you give the sheet name is "Feb 12 - Feb 18", son in your code you should change "-" to " - ". (Add the spaces). Also, please specify the error you get. — user11221377
That is correct, I want to hide all sheets except for the new one. I actually prefer the sheet name without the spaces, that was a mistake when I was typing the example. — Jake Rubinstein
Most of the time there is no error message, it just doesn't execute. I've gotten this error message "Cannot find method formatDate(string,string,string)" before, but it's only happened once, while the function has not executed for several weeks in a row now, with no error message. — Jake Rubinstein

Cooper Cooper · Accepted Answer · 2020-02-20T02:39:46

Try this:

If B2 or H2 are already dates that's okay this will just create another date from them but if they are strings that the constructor is familiar with then this will create a new date for them as well.

  var ss = SpreadsheetApp.openById('TEMPLATE SHEET ID').getActiveSheet()
  var newName1 = Utilities.formatDate(new Date(ss.getRange("B2").getValue()),"CST","MMM dd");
  var newName2 = Utilities.formatDate(new Date(ss.getRange("H2").getValue()),"CST","MMM dd");

  var EMPLOYEE = SpreadsheetApp.openById('EMPLOYEE SHEET ID');
  var hideEMPLOYEE = EMPLOYEE.getSheets();
    for(var i=0;i<hideEMPLOYEE.length;i++){
    Logger.log(i);
    if(hideEMPLOYEE[i].getName()!= newName1 + "-" + newName2){
    hideEMPLOYEE[i].hideSheet();
     }
  }

I'm trying to automatically hide sheets in google sheets

1 Answers