Business
Jobs
  • About Us
  • Solutions
    • Job Postings
      Post your job and receive qualified candidates in 48h.
    • Candidate Assessments
      500+ technical and psychological tests, plus anti-fraud.
    • Headhunting
      Tailor-made executive search from start to finish.
    • Payroll + EOR
      Payroll dispersal and EOR across 15+ LATAM countries.
  • Pricing
  • Jobs

0

237
Views
google app scripts - Cant get hide sheets after a month to function

i am trying to make a script in google app scripts that would allow me to copy a template into a new sheet, append a date in the file as well as the title and auto hide sheets older than a month. Here is my code so far and everything works except the auto hide of sheets(with the exception of the active main sheet). Also, im not a professional coder, the way that i am checking against an older month probably isnt the best code, if you have any advice sharing would be appreciated!

If its easier, i would also be ok with if it just auto hid every other sheet except the main sheet when the script is run.

function createNewStandupSheet() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var activeSheet = ss.getActiveSheet()

  //Sets date
  var options = { day: '2-digit', month: 'short', year: 'numeric' };
  var todaysDate = new Date().toLocaleDateString("en-GB", options);
  var data = Utilities.formatDate(new Date(), "GMT", "'Week'w");

  //set month variable
  
  var today = new Date();
  var m = today.getMonth(); 

  //copy template as a new sheet
  var newSheet = ss.getSheetByName('DAILY STANDUP TEMPLATE').copyTo(ss);

  //change the new sheet's name
  SpreadsheetApp.flush();
  newSheet.setName(data + "-" + todaysDate);
  var sheetname = newSheet.getName();

  //Set line one value to date
  var cell = newSheet.getRange("C1:G1");
  cell.setValue(todaysDate);

//check if month is the same as current month, if its less, hide the other sheets. Then change the value to the current month after checking.
  var monthcell = newSheet.getRange("A50:B50");
  if (monthcell < m){
    hideAllSheetsExcept(sheetname)
  }
  monthcell.setValue(m)

function hideAllSheetsExcept(sheetName) {
  var sheets=SpreadsheetApp.getActiveSpreadsheet().getSheets();

  for(var i =0;i<sheets.length;i++){
    Logger.log(i);
    if(sheets[i].getName()!=sheetName){
      sheets[i].hideSheet();
    }
  }
}
about 4 years ago · Juan Pablo Isaza
1 answers
Answer question

0

Insert New Sheet from Template and hide all sheets whose date values are lower than the first day of the current month.

  • And I think this eliminates the need for the monthcell altogether unless you're doing it for other reasons not discussed as yet.
    function createNewStandupSheet() {
      const mA = [...Array.from(new Array(12).keys(), x => Utilities.formatDate(new Date(new Date().getFullYear(), x, 15), Session.getScriptTimeZone(), "MMM"))];//generates an array of ["Jan","Feb"...
      const ss = SpreadsheetApp.getActive();
      const todaysDate = new Date().toLocaleDateString("en-GB", { day: '2-digit', month: 'short', year: 'numeric' });
      const data = Utilities.formatDate(new Date(), "GMT", "'Week'w");
      const hdtv = new Date(new Date().getFullYear(),new Date().getMonth(),1).valueOf();
      const sheetname = data + "-" + todaysDate;
      const nsh = ss.insertSheet(sheetname, 0, { template: ss.getSheetByName('DAILY STANDUP TEMPLATE') });
      nsh.getRange("C1").setValue(todaysDate);
      ss.getSheets().filter(sh => sh.getName() != sheetname).forEach(sh => {
        if (sh.getName().match(/^Week\d{1,2}-\d{2} [a-zA-Z]{3} \d{4}/)) {
          let t = sh.getName().split('-')[1].toString().split(' ');
          let dtv = new Date(t[2],mA.indexOf(t[1]),parseInt(t[0])).valueOf();
          if (dtv < hdtv) { sh.hideSheet(); }
        }
      });
    }

>This line matches sheet names to make sure I'm dealing with the correct sheets:

    if (sh.getName().match(/^Week\d{1,2}-\d{2} [a-zA-Z]{3} \d{4}/)) {

>and these two lines calculate the date value of the current sheet

    let t = sh.getName().split('-')[1].toString().split(' ');
    let dtv = new Date(t[2],mA.indexOf(t[1]),parseInt(t[0])).valueOf();

Appearance of a Sheet Title:

enter image description here

about 4 years ago · Juan Pablo Isaza Report
Answer question
Find remote jobs

Discover the new way to find a job!

Top jobs
Top job categories
Business
Post vacancy Pricing Sales
Legal
Terms and conditions Privacy policy
© 2026 PeakU Inc. All Rights Reserved.
Andres GPT
Show me some job opportunities
There's an error!