Google-sheets – Scitpt to duplicate template sheet into new workbook, then rename

google sheetsgoogle-apps-scriptgoogle-sheets-custom-function

I am looking for a script that will create a menu item that will allow me to copy a template sheet that I have created (multiple times) into a new workbook, and rename the copies at the same time.

Example:

Workbook 1:
Tab Name: Cash End (Digital)

Copy Cash End (Digital) into new workbook 30 times, and Rename each copy's tab to (January 1)(January 31).

I found this script that does some of what I want. It will copy my sheet into a new workbook, but I need it to copy the sheet multiple times and rename them at the same time.

function copySheets() {
  var copySheetsContaining = Browser.inputBox("Copy sheets with names containing:");
  var destinationId = Browser.inputBox("Enter the destination spreadsheet ID:");
  if (sheetMatch(copySheetsContaining)){
    for (var i = 0; i < sheetsCount; i++){
      var sheet = sheets[i]; 
      var sheetName = sheet.getName();
      Logger.log(sheetName); 
      if (sheetName.indexOf(copySheetsContaining.toString()) !== -1){
        Logger.log("COPY!");
        var destination = SpreadsheetApp.openById(destinationId);
        sheet.copyTo(destination);
      }
    }
    successAlert('copied')
  } else {
    noMatchAlert();
  }
}

// determine if any sheets match the user input
function sheetMatch(sheetMatch){
  for (var i = 0; i < sheetsCount; i++){
    var sheetName = sheets[i].getName(); 
    if (sheetName.indexOf(sheetMatch.toString()) !== -1){
      return true
    }
  }
  return false
}

// alert if no sheets matched the user input
function noMatchAlert() {
  var ui = SpreadsheetApp.getUi();
  var result = ui.alert(
     'No Sheets Matched Your Input',
     "Try again and make sure you aren't using quotes.",
      ui.ButtonSet.OK);
}

// alert after succesful action (only used in copy)
function successAlert(action) {
  var ui = SpreadsheetApp.getUi();
  var result = ui.alert(
     'Success!',
     "You're sheets were " + action + " successfully.",
      ui.ButtonSet.OK);
}

Best Answer

function onOpen() {
    SpreadsheetApp.getUi().createMenu('My Menu')
        .addItem('Create New Tabs', 'createTabs')
        .addToUi()}
function createTabs() { var ss = SpreadsheetApp.getActive() ss.getSheetByName('Sheet999').getRange('A:A').getValues().filter(String) .forEach(function (sn) { if (!ss.getSheetByName(sn[0])) { ss.insertSheet(sn[0], ss.getSheets().length);}})}

This script will create a new custom menu/submenu with the option to create new tabs/sheets. The usage is: create a new sheet and rename it to Sheet999 and then populate column A with desired names

January 1
January 2
etc.

and then go to My Menu and select Create New Tabs

enter image description here