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();
}
}
}
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: