I want to create a script for google sheets that will make multiple copies of a sheet and place the new sheets to the left of the active sheet

Question

I am very new to using google scripts. I have a spreadsheet with tabs that are titled by date. I found the following script that will enable me to create a variable number of duplicate sheets. The script will also automatically rename the new sheets by changing the date to the next date. The only problem is that all of the new sheets are added to the far right of the document. I want each copy to be placed to the left of the active sheet. So if I have a tab titled 1/3/21 and I make 3 copies of that tab, the new tabs will read from left to right 1/6/21; 1/5/21; 1/4/21. This is the script I am using. I had to disable "Enable Chrome V8 runtime" for this script to work.

function duplicatesheet() {
  var as = SpreadsheetApp.getActiveSpreadsheet(); // active spreadsheet
  var s = as.getActiveSheet(); // first sheet object
  var dateCell = "A6:N6"; // cell containing first date
  var N = 5; // 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, "MM/dd/yy")); // sets the name of     the new sheet
  }
}

user_11225291 · Answer

Explanation:

As the other answer explained you can use insertSheet(sheetName, sheetIndex, options). This answer explains how you can incorporate that function into your code but also how to make your code faster and more efficient.

In your code dateCell is a range of cells "A6:N6", but you only need a single value and that is A6. Therefore simply, change it to A6.
You don't need to create a date object of your value. If A6 in your sheet contains a date value, then GAS will pick it up correctly.
You don't need to build your own date objects iteratively. You can use:
```
startDate.setDate(startDate.getDate()+1);
```
and that will increase the date by one day. In this way you can get rid of day, month and year variables.
I am not sure why you put undefined as the second argument of Utilities.formatDate and also not sure why your script editor didn't drop an error. But you probably wanted to use as.getSpreadsheetTimeZone() instead.
To correctly insert the sheets to the left of the active sheet inside the for loop you need an index expression like that sindex-N+1. The +1 is used because getIndex starts counting from 1. Namely for the first sheet in your document getIndex would return 1. But the sheetIndex parameter in insertSheet accepts an index that starts from 0.

Solution:

function duplicatesheet() {
  var as = SpreadsheetApp.getActiveSpreadsheet(); 
  var s = as.getActiveSheet(); 
  var sindex = s.getIndex();
  var dateCell = "A6"; 
  var N = 2; 
  var startDate = s.getRange(dateCell).getValue();
  // loop over N times
  for (var i = 0; i < N; i++) {
    startDate.setDate(startDate.getDate()+1);
    var startDateStr = Utilities.formatDate(startDate, as.getSpreadsheetTimeZone(), "MM/dd/yy")
    var asn=as.insertSheet(startDateStr, sindex-N+1, {template: s});
    asn.getRange(dateCell).setValue(startDate); 
  }
}

user_11758896 · Answer

This seems kind of easy to do:

function myFunction() {
  let ss = SpreadsheetApp.getActiveSpreadsheet();
  let sheet = SpreadsheetApp.getActiveSheet(); 
  
  let index = sheet.getIndex();

  ss.insertSheet("New Sheet", index - 1, {template: sheet, });
}

After executing this code your sheet should look like this after being executed:

I want to create a script for google sheets that will make multiple copies of a sheet and place the new sheets to the left of the active sheet

2 Answers

Explanation:

Solution:

Reference