0

I want to duplicate and name multiple copies of a sheet that already has data. The rename would be based on a list from another sheet in the same workbook.

This script does that, but only generates new blank sheets. Can this be modified to duplicate and rename vs make a blank sheet and name?

Sheet to duplicate in this instance is "FO_VFX_010" but if that could be any name, even better.
The sheet with the names is labeled as is "All_shots" the column is A rows 2- whatever.

Here's the existing code from someone else's post.

function onOpen() {
    SpreadsheetApp.getUi().createMenu('My Menu')
        .addItem('Create New Tabs', 'createTabs')
        .addToUi()}

function createTabs() {
    var ss = SpreadsheetApp.getActive()
    ss.getSheetByName('Names').getRange('A:A').getValues().filter(String)
        .forEach(function (sn) {
            if (!ss.getSheetByName(sn[0])) {
                ss.insertSheet(sn[0], ss.getSheets().length);}})}

2 Answers 2

0

You want to duplicate a sheet containing an existing template AND give the new sheet a predetermined name.

In this answer, the list of sheet names is taken from Column A of "All shots", and the method to duplicate the template is a specifically designed for templates: insertSheet(sheetName, options) Doc Ref


function buildsheets() {
  
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  
  //define all shots
  var sourcename = "All_shots";
  var allshots = ss.getSheetByName(sourcename)
  
  // define shot_001 - initial template
  var templatename = "shot_001"
  var templatesheet = ss.getSheetByName(templatename)
  
  // get the number of sheet names
  // assume row 1 = shot_001 has already been created
  var Avals = allshots.getRange("A2:A").getValues();
  var Alast = Avals.filter(String).length;
  Logger.log("the number of rows of sheets = "+Alast);
  
  // get the data from all shots
  var sourcedata = allshots.getDataRange().getValues();
  
  // loop through the sheet names on All_Shots
  for (var i = 1; i<Alast+1; i++){
    
    var name = sourcedata[i][0]; 
    ss.insertSheet(name, {template: templatesheet});
    var newsheet = ss.getSheetByName(name)
    var target = newsheet.getRange("A1");
    target.setValue(name);

  }
  SpreadsheetApp.flush();
}

Sheet = "All_shots"

This shows the proposed sheet names in Column A, and other relevant data in Column B All Shots


The initial template - shot_001

This shows the format of the initial sheet to be used as the template Shot_001


Duplicate template

This shows the first duplicate of the template based on the names list on "All_shots". Note the value in cell C3 has been updated to reflect the matching data on "All_shots" Duplicate


New template sheets

This shows the newly created sheets "shot_002", "shot_003" and "shot_004" (the names come from the list in Column A of "All_shots"). In this example, there are only three duplicates made but the same code applies for any range in Column A of "All_shots.

New sheets

0
0

This is fantastic!

I kit-bashed what you provided, changed a couple names and viola! It's a masterpiece! totally works the way I need! you ROCK!

This is the final form:

function onOpen() {
    SpreadsheetApp.getUi().createMenu('buildsheets')
        .addItem('buildsheets', 'buildsheets')
        .addToUi()}

function buildsheets() {
  
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  
  //define All_shots
  var sourcename = "All_shots";
  var All_shots = ss.getSheetByName(sourcename)
  
  // define Template - initial template
  var templatename = "Template"
  var templatesheet = ss.getSheetByName(templatename)
  
  // get the number of sheet names
  // assume row 1 = Template has already been created
  var Avals = All_shots.getRange("A2:A").getValues();
  var Alast = Avals.filter(String).length;
  Logger.log("the number of rows of sheets = "+Alast);
  
  // get the data from all shots
  var sourcedata = All_shots.getDataRange().getValues();
  
  // loop through the sheet names on All_Shots
  for (var i = 1; i<Alast+1; i++){
    
    var name = sourcedata[i][0]; 
    ss.insertSheet(name, {template: templatesheet});
    var newsheet = ss.getSheetByName(name)
    var target = newsheet.getRange("A1");
    target.setValue(name);

  }
  SpreadsheetApp.flush();
}

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.