Scitpt to duplicate template sheet into new workbook, then rename

Posted on

Scitpt to duplicate template sheet into new workbook, then rename – Whether you’re just starting out with Google Sheets or are a seasoned pro, sooner or later one of your formulas will give you a formula or script parse error message rather than the result you want. However, every Formula user, irrespective of whether it’s a beginner or expert, has invariably come across a formula parse error in Google Sheets at least once in their life (and if you haven’t. you soon will). It can be frustrating, especially if it’s a longer formula where the formula parse error may not be obvious. In this article, we will talk about some common script or formula parse errors in Google Sheets, like Scitpt to duplicate template sheet into new workbook, then rename, and how to fix it.

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);
}

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

Leave a Reply

Your email address will not be published.