Insert new sheet using sheet template is not working

user_13130188 0
Viewed 111

I have a sheet names in column A of the active sheet.

I am trying to get each new tab to be a duplicate of my Template tab, but they are just blank.

Here is the code:

/** @OnlyCurrentDoc */

function makeTabs(){
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getActiveSheet();
  var last = sheet.getLastRow();
  for(var i = 1; i < last ; i++){
    var tabName = sheet.getRange(i+1,1).getValue();
    var templateSheet = ss.getSheetByName("Template");
    var create = ss.insertSheet(tabName,{template: templateSheet});
  }
}

Can anyone tell why created sheets are not based on template?

google-apps-script google-sheets
1 Answers

Solution:

Here is what you are looking for:

function copySheets() {
  
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sheet = ss.getActiveSheet();
  const last = sheet.getLastRow();
  const template = ss.getSheetByName('Template');
  const sh_names = sheet.getRange('A2:A'+last).getValues().flat([1]);
 
  sh_names.forEach((name)=>{            
  const copy=template.copyTo(ss);
  copy.setName(name);        
  })
}

Output:

example

Explanation:

I am using forEach() to iterate over the list of the names in column A of the active sheet. For each name, I am creating a copy of the template with this code:

const copy=template.copyTo(ss);
copy.setName(name);

Please keep in mind that you need to run this function when you have selected the active sheet (see my screenshot); the sheet that contains the name of the sheets you want to create. That is the reason I believe it is safer to get a sheet based on its name to be sure that you are getting the right input:

const sheet = ss.getSheetByName('Sheet1');

Obviously, you can't run this function more than once; it will output an error that sheets with these sheet names already exist.

Last but not least, all the duplicate sheets will have exactly the same content and format of Template sheet. I assume based on your question that this is your goal.

user_112252910
Related