Monthly Sheet Duplication via Time Trigger-CodePudding

I have a spreadsheet to track monthly memberships and payments. I duplicate the sheet at the end of the month rename it to the date and lock it to archive it. I have a marco recorded and setup to do this and have a button on the sheet to click to do this. It works great.

However, I'd like to make this script triggered via time which I have setup in the Tigger area of google scripts. But when it runs, it fails and I get the following error:

Exception: Please select an active sheet first. at ArchiveSheet(macros:5:15)

Here is my code:

function ArchiveSheet() {
var spreadsheet = SpreadsheetApp.getActiveSpreadsheet()
spreadsheet.duplicateActiveSheet();
var sheetname = Utilities.formatDate(new Date(), Session.getScriptTimeZone(), "MMM/yyyy");
spreadsheet.getActiveSheet().setName("Archive "  sheetname);
spreadsheet.setActiveSheet(spreadsheet.getSheetByName('Current Month'), true);
spreadsheet.getRange('J3:J263').activate();
};

Any help would be appreciated.

CodePudding user response：

getActiveSpreasheet() can only be used on bounded scripts or on change, on edit and on open installable triggers created programmatically, on a time-driven trigger you have to use one of the following

open(file) (this should be used together with DriveApp / Advanced Drive Service
openById(id)
openByUrl(url)

Time-Driven triggers not working properly

CodePudding user response：

Sheet Duplication with a trigger

function myfunk101() {
  const ss = SpreadsheetApp.getActive();
  const sh = ss.getSheetByName('Sheet0');
  sh.activate();
  const nsh = ss.duplicateActiveSheet();
  const n = Utilities.formatDate(new Date(), Session.getScriptTimeZone(), "HH:mm:ss");
  nsh.setName("Archive "   n);
};

The active sheet is always ss.getSheets()[0] so it's better to pick on by name. And it won't activate a sheet on a current session because the instance its running on is only opened on the server.

function createTriggerFormyfunk101() {
  if (ScriptApp.getProjectTriggers().filter(t => t.getHandlerFunction() == 'myfunk101').length == 0) {
    ScriptApp.newTrigger('myfunk101').timeBased().everyMinutes(5).create();//I used every five minutes because I didn't want to wait for the end of the month
  }
}

I think this is a better way to go:

function myfunk101() {
  const ss = SpreadsheetApp.getActive();
  const sh = ss.getSheetByName("Sheet0");//Pick your sheet
  ss.insertSheet(Utilities.formatDate(new Date(), Session.getScriptTimeZone(), "HH:mm:ss"), {template: sh});//change you time format for your name.  Since I was running 5 minute triggers I wanted more precision.
};

insertSheet(sheetName, options)