How duplicate a Google Spreadsheet sheet, rename it, and paste the content as absolute values using Google App Scripts

Question

Hello Stackoverflowers,

I want to duplicate a sheet called "Dashboard" the 1.st of every month with the sheet name of the previous month and Year (MMMM YYYY) and the content as static values.

To do that, I have tried to adapt the two following scripts but it not doing what I want.

the problems are:

1. The values are not pasted as absoule values.

2. The sheet name is the name of the current month and not the previous month.

I believe that for the problem num. 1, the following statement has a mistake in the syntax, but i can´t find it:

asn.copyTo(getRange("MMMMM yyyy"),{contentsOnly: true});

For the problem num. 2 I don´t really know how to start.

=> Some ideas? Any help will be strongly appreciated

function duplicatesheet() {
  var as = SpreadsheetApp.getActiveSpreadsheet(); // active spreadsheet

    var s = as.getSheetByName('Dashboard'); // first sheet object

 // var s = as.getActiveSheet(); // first sheet object
  var dateCell = "H5"; // cell containing first date
  var N = 1; // number of copies to make

  var startDate = new Date(s.getRange(dateCell).getValue()); // get the date stored in dateCell
  var day = startDate.getDate(); // extract the day
  var month = startDate.getMonth(); // extract the month
  var year = startDate.getFullYear(); // extract the year

  // loop over N times
  for (var i = 0; i < N; i++) {

   var asn = s.copyTo(as); // make a duplicate of the first sheet



    var thisSheetDate = new Date(year, month, day+(i+1)); // store the new date as a variable temporarily

    asn.getRange(dateCell).setValue(thisSheetDate); // writes the date in cell "B3"
    asn.setName(Utilities.formatDate(thisSheetDate, undefined, "MMMMM yyyy")); // sets the name of the new sheet
    asn.copyTo(getRange("MMMMM yyyy"),{contentsOnly: true});
  }
}

function moveValuesDown() {
  var ss = SpreadsheetApp.getActiveSpreadsheet ();
  var source = ss.getRange ("Dashboard!A4:I");

  source.copyTo (ss.getRange ("MySheet1!A5"), {contentsOnly: true});
}

Answer 1

@Cooper and @Aerials Thank you very much for your contribution! Both are very elegant solutions.

Eventually, I ended up combining both of the scrips, to be able to duplicate the tab keeping the format and the values as absolute values.

This is how it looks like:

/// Duplicate Dashboard values in previous month tab

function createNewSheetFromTemplate(templatename="Dashboard") {
  const dt=new Date();
  //This runs the insert only on the first day of the month
  if(dt.getDate()==1) {
    const ss=SpreadsheetApp.getActive();
    const template=ss.getSheetByName(templatename);//default parameter
    const name="Pacing " + Utilities.formatDate(new Date(dt.getFullYear(),dt.getMonth(),0), Session.getScriptTimeZone(), "MMMM");
    ss.insertSheet(name, {template:template});

 // Select the new created sheet already renamed    
     var pastMonthSheet = ss.getSheetByName(name);
 // Copy values from dashboard
  var pastMonthDataRange = ss.getSheetByName(name).getDataRange();

  // Paste in the values form the dashboard as absolute values in the past month sheet
  pastMonthSheet.activate().getRange(pastMonthDataRange.getA1Notation()).setValues(pastMonthDataRange.getValues())

  }
}

Answer 2

Your goals:

1. Save last month's values in a sheet with last month's name.
2. Do this on the 1st of the month
3. Continue using the existing dashboard for the current month
4. Do it again next month

---

An example to achieve them:

function onMonthStart() {
  const monthNames = ['Jan','Feb','Mar','Apr','Jun','Jul','Aug','Sep','Oct','Nov','Dec'];

  // Whats last month's name?
  var thisMonth = Utilities.formatDate(new Date(),"GMT","MMM");
  var pastMonth = monthNames[monthNames.indexOf(thisMonth) - 1];
  if (monthNames.indexOf(thisMonth) == 0){
    pastMonth = 'Dec';
  }

  // Create new destination sheet for past month values
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var pastMonthSheet = ss.insertSheet(pastMonth);

  // Copy data from dashboard
  var pastMonthDataRange = ss.getSheetByName('Dashboard').getDataRange();

  // Paste in past month's sheet
  pastMonthSheet.activate().getRange(pastMonthDataRange.getA1Notation()).setValues(pastMonthDataRange.getValues())
}

How to trigger onMonthStart every 1st of the month?:

Install a time driven trigger on the first day of the month.

---

Notes:

1. Using method Sheet.copyTo takes a Spreadsheet object as param. You are passing a range with options. Not the same!
2. Try to be consistent in your variable naming style, and use names with meaning for readability.

Answer 3

Create copy of Dashboard every month on the first of the month

function createNewSheetFromTemplate(templatename="Dashboard") {
  const dt=new Date();
  //This runs the insert only on the first day of the month
  if(dt.getDate()==1) {
    const ss=SpreadsheetApp.getActive();
    const template=ss.getSheetByName(templatename);//default parameter
    const name=Utilities.formatDate(new Date(dt.getFullYear(),dt.getMonth(),0), Session.getScriptTimeZone(), "MMMM/yyyy");
    ss.insertSheet(name, {template:template});
  }
}

function createTimeBasedTrigger() {
  if(!isATrigger('createNewSheetFromTemplate')) {
    ScriptApp.newTrigger('createNewSheetFromTemplate').timeBased().everyDays(n).atHour(6).create();//The trigger happens daily at 6 am
  }
}

//You just need to run this once.   It's setup to  only allow one trigger to be created even if you run it again.
function isATrigger(funcName){
  var r=false;
  if(funcName){
    var allTriggers=ScriptApp.getProjectTriggers();
    for(let i=0;i<allTriggers.length;i++){
      if(funcName==allTriggers[i].getHandlerFunction()){
        r=true;
        break;
      }
    }
  }
  return r;
}

Note: this script requires V8.