Google Sheets - Duplicate Master tabs daily, rename by date and hide previous duplicates

Question

I've got a spreadsheet with 3 tabs - Master X, Master Y and Master Z.

I'd like these tabs to automatically duplicate at midnight, and be renamed with the date they're copied in the following format - Master X 23/07.

Preferably these master tabs would be hidden, and then old tabs duplicated the previous date would automatically be hidden - so on any given day, there would only be today's 3 tabs visible.

The process is the following:

calculate the date of today and yesterday according to the format: DD/MM,
Find the master sheets and duplicate them
rename the newly created sheets with the today date,
hide the yesterday sheets,
create a daily trigger to make this process automated.

Would anyone be able to support with a script which actions all of the above please?

soMario soMario · Accepted Answer · 2020-07-23T11:41:24

The first step you need to do is to add the following code snippet to a new script:

 function MasterXYZ() {
  
 var source = SpreadsheetApp.getActiveSpreadsheet();
 
 const patternX = 'Master X';
 const patternY = 'Master Y';
 const patternZ = 'Master Z';
 
 var masterX = source.getSheetByName(patternX);
 var masterY = source.getSheetByName(patternY);
 var masterZ = source.getSheetByName(patternZ);

 masterX.copyTo(source).setName(patternX+" " + getDates()[1]).showSheet();
 masterY.copyTo(source).setName(patternY+ " " + getDates()[1]).showSheet();
 masterZ.copyTo(source).setName(patternZ+ " " + getDates()[1]).showSheet();
  
 masterX.hideSheet();
 masterY.hideSheet();
 masterZ.hideSheet();
 
  try{
 source.getSheetByName(patternX+" " + getDates()[0]).hideSheet();
 source.getSheetByName(patternY+ " " + getDates()[0]).hideSheet();
 source.getSheetByName(patternZ+ " " + getDates()[0]).hideSheet();  
  }
  catch(e){Logger.log("There are no yesterday's sheets")}
 
}

function getDates() {
 
  var today = new Date();
  
  var yesterday = new Date()
  yesterday.setDate(yesterday.getDate()-1)
   
  var today_month = addZero(today.getMonth()+1)
  var today_day = addZero(today.getDate())
  
  var yesterday_month = addZero(yesterday.getMonth()+1)
  var yesterday_day = addZero(yesterday.getDate())
 
  var today_date =  today_day.toString() + "/" + today_month.toString()
  var yesterday_date =  yesterday_day.toString() + "/" + yesterday_month.toString()
  
  return [yesterday_date,today_date]


}

function addZero(i) {
  if (i < 10) {
    i = "0" + i;
  }
  return i;
}

Then you need to setup a daily trigger for the MasterXYZ() by clicking on the current project's trigger menu and then add new trigger.

You only need to execute the function: MasterXYZ(). The rest are helper functions.

Google Sheets - Duplicate Master tabs daily, rename by date and hide previous duplicates

1 Answers