Empresas
Empleos
  • Sobre nosotros
  • Soluciones
    • Publicación de vacantes
      Publica tu vacante y recibe candidatos calificados en 48h.
    • Evaluación de candidatos
      500+ pruebas técnicas y psicológicas, más anti-fraude.
    • Headhunting
      Búsqueda ejecutiva a la medida de principio a fin.
    • Nómina + EOR
      Dispersión de nómina y EOR en más de 15 países de LATAM.
  • Precios
  • Empleos

0

223
Vistas
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 Respuestas
Responde la pregunta

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 Denunciar
Responde la pregunta
Encuentra empleos remotos

¡Descubre la nueva forma de encontrar empleo!

Top de empleos
Top categorías de empleo
Empresas
Publicar vacante Precios Comercial
Legal
Términos y condiciones Política de privacidad
© 2026 PeakU Inc. All Rights Reserved.
Andres GPT
Recomiéndame algunas ofertas
Necesito ayuda