Looking to copy a sheet over as a new tab and renaming it based off a specific cell value-CodePudding

For work my team keeps a database of regular customers in which the team has to copy and paste a "template" page every time we log a new customer into the database.

I would like them to be able to click on a sheet labeled "create new customer" that shows only two cells "customer name" in cell A1 and in cell B1 they can manually type the name in. Once they have done so I would like to have some sort of submit button next to those cells that copies the original template sheet over and renames that new tab as the customer name in cell B1 while also keeping the protections from the template page.

I apologize if I caused any confusion but I am having multiple issues with the team deleting data/scripts etc resulting in myself backtracking to figure it out constantly. Thank you in advance!

CodePudding user response：

Description

Might I suggest using a Ui prompt to get the customer name. Below is a simple example of prompting the user for a name, copy a Template, apply the customer name and apply the templates protections. The prompt is triggered from a menu option rather than from a spreadsheet cell.

Script

function onOpen(e) {
  var menu = SpreadsheetApp.getUi().createMenu("My Menu");
  menu.addItem("Create New Customer","createNewCustomer");
  menu.addToUi();
}

function createNewCustomer() {
  try {
    let ui = SpreadsheetApp.getUi();
    let response = ui.prompt("Customer Name",ui.ButtonSet.OK_CANCEL);
    if( response.getSelectedButton() == ui.Button.OK ) {
      let spread = SpreadsheetApp.getActiveSpreadsheet();
      let sheets = spread.getSheets();
      let name = response.getResponseText();
      // make sure the sheet doesn't already exist
      sheets.forEach( sheet => { if( sheet.getName() === name ) { throw "Customer " name " already defined" } } );
      let template = spread.getSheetByName("Template");
      let copy = template.copyTo(spread);
      copy.setName(name);
      // get all protected ranges from template
      let protections = template.getProtections(SpreadsheetApp.ProtectionType.RANGE);
      for( let i = 0; i < protections.length; i   ) {
        let protection = protections[i];
        let rangeNotation = protection.getRange().getA1Notation();
        let protectRange = copy.getRange(rangeNotation).protect();
        protectRange.setDescription(protection.getDescription());
        protectRange.setWarningOnly(protection.isWarningOnly());
        // set all eligible editors
        if (!protection.isWarningOnly()) {
          protectRange.removeEditors(protectRange.getEditors());
          protectRange.addEditors(protection.getEditors());
        }
      }
    }
  }
  catch(err) {
    ui.alert(err);
  }
}

Reference