I'm trying to automate duplication of master sheet & at the same time rename the current master sheet to the current day.
Process:
- Lets say the Master Sheet was named for yesterday's date "04/18/2022"
- Duplicate the Master Sheet and rename it after duplication to the current date "04/19/2022"
- Rename the duplicated sheet to remove "Copy of"
Greatly appreciate the help
CodePudding user response:
Make a duplicate every day from yesterdays master
function dupsht() {
const ss = SpreadsheetApp.getActive();
const dt = new Date();//today
const ydt = new Date(dt.getFullYear(),dt.getMonth(),dt.getDate() - 1);//yesterday
const sh = ss.getSheetByName(Utilities.formatDate(ydt,Session.getScriptTimeZone(),"MM/dd/yyyy"));
ss.insertSheet(Utilities.formatDate(dt,Session.getScriptTimeZone(),"MM/dd/yyyy"),{template:sh})
}
CodePudding user response:
Try
function duplicateTemplate() {
var today = new Date();
var yesterday = new Date();
yesterday.setDate(today.getDate() - 1)
var d0 = Utilities.formatDate(yesterday, Session.getScriptTimeZone(), "MM/dd/yyyy");
var d1 = Utilities.formatDate(today, Session.getScriptTimeZone(), "MM/ddyyyy");
SpreadsheetApp.getActiveSpreadsheet().getSheetByName(d0).activate()
SpreadsheetApp.getActiveSpreadsheet().duplicateActiveSheet().setName(d1)
};
Take care of the time zone in the script editor. To change if needed, click the gear on the left side of the script editor, check the third box, go back to the script editor and select appsscript.json. Change as necessary.