Google Sheets Script to Duplicate & Rename Spreadsheets

2
votes

I'm creating a script in Google Sheets that will copy the active sheet and create 30 duplicate sheets within the same workbook. Each duplicated sheet will have a different name based on the value within a cell on the active sheet. The cell will contain a date; duplicated sheets will have names of dates after the date listed in the cell. Example, cell B3 is "7/5/2019". The duplicated sheets should be named, "July 6, 2019" (B3+1), "July 7, 2019" (B3+2), & "July 8, 2019" (B3+3), etc.

I'm using code that is already embedded within Google Sheets. Some of it was created by recording a macro and other parts were created through what little I know about coding and research online.

function duplicatesheet(){

//copy active sheet
var as = SpreadsheetApp.getActiveSpreadsheet()
SpreadsheetApp.getActiveSpreadsheet().duplicateActiveSheet();

//rename sheet
var myValue = 
SpreadsheetApp.getActiveSpreadsheet().getRange('B3').getValue();
SpreadsheetApp.getActiveSpreadsheet().renameActiveSheet(myValue);

}

The code works in duplicating the active sheet once, but it is not making 30 duplicates. It is also not renaming the sheets properly as described above based on the date listed in cell B3. I need help creating code that will accomplish both of those tasks.

2
google-apps-scriptgoogle-sheets

2 Answers

4
votes

This is edited w.r.t. comments. You can also see the excellent answer by Tedinoz.

Try this code:

function duplicatesheet() {
  var as = SpreadsheetApp.getActiveSpreadsheet(); // active spreadsheet
  var s = as.getActiveSheet(); // first sheet object
  var dateCell = "B3"; // cell containing first date
  var N = 30; // 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 d, yyyy")); // sets the name of the new sheet
  }
}

I suggest putting N=30 there to something small, like N=2 to see whether it works with your formatting first.

The formatting here used by the Utilities.formatDate() method, I have assumed it to be MMMMM d, yyyy, which will print tab names in this format:

July 6, 2019

You may change it as you wish according to the reference [3] below.

You can see the references for all the functions used here:

1
votes

This code takes the single date entered by the OP in Cell "B3" of sheet name, say, "Sheet1; it loops thirty times creating a duplicate of the initial spreadsheet, incrementing the data-based sheet name by 1 day each time.

To ensure accurate date math, it's suggested that the format of Cell "B3" should be in the same style ("MMMM dd, yyyy") as the proposed sheet names.

function so5691088602(){

  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheetname = "Sheet1";
  var basesheet = ss.getSheetByName(sheetname);
  var newSheetName = new Date(basesheet.getRange("B3").getValue());
  var NumDups = 30;
  for (var i=0;i<NumDups;i++){
    basesheet.activate;
    var tempDate = new Date();
    var printdate01 = Utilities.formatDate(new Date(tempDate.setDate(newSheetName.getDate()+1+i)), "GMT+10", "MMMM dd, yyyy");
    // Logger.log("DEBUG: Sheet Date = "+printdate01);
    ss.insertSheet(printdate01, {template: basesheet});
  }
}

Screenshot

enter image description here