Home > Blockchain >  Google Sheet Script to Duplicate Master Sheet & Rename
Google Sheet Script to Duplicate Master Sheet & Rename

Time:04-19

I'm trying to automate duplication of master sheet & at the same time rename the current master sheet to the current day.

Process:

  1. Lets say the Master Sheet was named for yesterday's date "04/18/2022"
  2. Duplicate the Master Sheet and rename it after duplication to the current date "04/19/2022"
  3. 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.

  • Related