-2

I'm working on creating a budget on Google Sheets. I have two sheets for January (titled Jan Summary and Jan Transactions). I'm trying to create a script that will automatically duplicate those sheets at the beginning of each new month and change the sheet names that month as well. I'm not sure how to do this since I'm new to coding - does anyone have ideas?

Jb11
  • 1

2 Answers2

1
function duplicateAndRenameSheet() {
  const prefix="Copy of ";//add in front of new name
  const postfix= ' ' + Utilities.formatDate(new Date(),Session.getScriptTimeZone,"E yyMMdd:HHmmss");//add to back of new name
  const ss=SpreadsheetApp.getActive();
  const sh=ss.getActiveSheet();
  let name=sh.getName();//active sheets name
  let nsh=ss.insertSheet({template:sh});//inserts a new sheet that's a copy of the active sheet
  nsh.setName(prefix + name + postfix);//renames the new sheet
}

Spreadsheet Methods

Sheet Methods

Cooper
  • 59,616
  • 6
  • 23
  • 54
1

What you want can be achieved simply by using a time-driven installable trigger in Apps Script.

As for making copies of the two sheets, the following methods might be of help to you:

  • copyTo(spreadsheet) - this will allow you to copy a sheet to a spreadhseet;

In your case, since you want to copy the two sheets, you will have to use the method above for each sheet.

  • setName(name) - this will allow you to change the name for the sheet;

Since you want to get the current month, a solution for this is to get the current date and afterwards use the getMonth.

As for the trigger, you can create one like this:

function createTrigger() {
    ScriptApp.newTrigger('copySheets')
         .timeBased()
         .onMonthDay(1)
         .atHour(9)
         .create();
    }
}

The above function creates a time-based trigger on the copySheets function (assuming this is the function which will copy the sheets) and it will run on the 1st of each month.

Reference

ale13
  • 5,679
  • 3
  • 10
  • 25