Google-sheets – Script to Duplicate Template Tab Multiple Times and Rename Tabs from Cell Range

google sheetsgoogle-apps-script

Basically, I have a list of 200 people's names in the range 'Names'!A1:A200. I have another tab named Template. I need a script to duplicate Template 200 times and rename each of the tabs to the name of each of the people in the range 'Names'!A1:A200. Is this possible?

Best Answer

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

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 Names and then populate column A with desired names

Ema
Emily
etc.

and then go to My Menu and select Create New Tabs

and for template copy/duplication you may want to check:

Related Solutions

Google-sheets – Duplicate and rename sheet in one action on Google Sheets

This can be done with a script, using insertSheet method which takes the new sheet name and the template after which it's to be created. The first function adds a custom menu item when the spreadsheet is opened, the second performs duplication when that menu item is invoked.

As far as I can tell, the contents are copied over in the same way as they are with "Duplicate" function.

function onOpen() {
  var menu = [{name: "Duplicate and name", functionName: "dupName"}];
  SpreadsheetApp.getActiveSpreadsheet().addMenu("Custom", menu);
}

function dupName() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getActiveSheet();
  var name = Browser.inputBox('Enter new sheet name');
  ss.insertSheet(name, {template: sheet});
}

Google-sheets – How to pull data from multiple sheets in Google Spreadsheets 1 and transfer to multiple sheets in Google Spreadsheets 2

Copying data from other spreadsheets is certainly possible with the IMPORTRANGE formula. Example usage form one of my spreadsheets:

=IMPORTRANGE("https://docs.google.com/spreadsheets/d/35A4onc5he6l-dy3K_R6zMPd2Nc7o4LjFFEQA8Cx0hCE","April!A2:AH6")

Don't know how to answer about the automation of doing this only at a specified time during the day. Normally IMPORTRANGE works as soon as you open the target spreadsheet.

Best Answer

Related Solutions

Google-sheets – Duplicate and rename sheet in one action on Google Sheets

Google-sheets – How to pull data from multiple sheets in Google Spreadsheets 1 and transfer to multiple sheets in Google Spreadsheets 2

Related Topic