1

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

1 Answer 1

0

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

1
  • This does some of what I want, but not all. I also need it to copy the contents of the template sheet into those tabs. It also needs to save them into a new "workbook" for lack of a better term. It needs to move them into a new file.
    – No Name
    May 31, 2019 at 3:30

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.