Auto Create New Google Sheet With Column Headers Based on List of Names

Question

I'm trying to create a VERY basic CRM tool. I want to create a running list of customer names. Once I enter a name in Column A I'd like a new Sheet automatically created with pre-set columns I can enter values and information into. The columns I'd like are:

1. Name
2. Address
3. Phone Number
4. Email Address
5. Quote
6. Sold

Also, I'd like to be able to click on the name and be taken directly to the corresponding sheet that has been created.

Answer 1

Once I enter a name in Column A I'd like a new Sheet automatically created with pre-set columns

function onOpen() {
    SpreadsheetApp.getUi().createMenu('My Menu')
        .addItem('Create New Tabs', 'createTabs')
        .addToUi()}

function createTabs() {
    var ss = SpreadsheetApp.getActive()
    ss.getSheetByName('SheetXX').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 SheetXX and then populate column A with desired names

Hina
Yuki
etc.

and then go to My Menu and select Create New Tabs

---

The columns I'd like are: 1. Name 2. Address 3. Phone Number 4. Email Address 5. Quote 6. Sold

Template copy/duplication you may want to check:

• https://webapps.stackexchange.com/a/115857/186471
• http://googleappscripting.com/copy-sheets-from-template/#script
• https://stackoverflow.com/a/56300863/5632629

---

click on the name and be taken directly to the corresponding sheet that has been created

For that checkout hyperlinks and harness the power of gid numbers

• https://webapps.stackexchange.com/a/127548/186471
• https://webapps.stackexchange.com/a/130109/186471
• https://stackoverflow.com/a/55665868/5632629

Answer 2

I am using the below script for asking the user input and it creates a new sheet from the template with all formatting and protections with the name from the input. It will copy the variables in the sheet. You can customize it easily to save the names in another sheet.

function new_order() {
  var ss = SpreadsheetApp.getActiveSpreadsheet(); 
  var idno =  SpreadsheetApp.getUi().prompt("ID No. Example: 12345 or 12345-01").getResponseText();
  if (idno==""){Browser.msgBox('Sorry. Blank ID no not allowed');return};
  var cust =  SpreadsheetApp.getUi().prompt("Customer Name (Keep it short, without Lot No").getResponseText();
  if (cust==""){Browser.msgBox('Sorry. Blank not allowed');return};
  var cust=cust.toUpperCase();
  var sheets = ss.getSheets();
  for (j = 0; j < sheets.length; j++) {
     var shtn=sheets[j].getSheetName();
     if (shtn==idno+" "+cust) {
        var lotno =  SpreadsheetApp.getUi().prompt("Enter Lot no starting from 2. Do not repeat existing Lot no").getResponseText();
        var cust = cust+" LOT "+lotno;
     }//endif
  }//endfor j
  var qty =  SpreadsheetApp.getUi().prompt("No of panels").getResponseText();
  if (qty==""){Browser.msgBox('Sorry. Blank not allowed');return};
  var descrip =  SpreadsheetApp.getUi().prompt("Discription of panel").getResponseText();
  if (descrip==""){Browser.msgBox('Sorry. Blank not allowed');return};
  var today=new Date();
  var tgtdt1 = new Date(today.getTime()+(50*24*3600*1000));  
  var today = new Date();
  var sheet = ss.getSheetByName('Template');
  var sheet2 = sheet.copyTo(ss).setName(idno+" "+cust); 
  sheet2.getRange('B2').setValue(idno.toString().toUpperCase());  
  sheet2.getRange('D2').setValue(cust.toUpperCase());  
  sheet2.getRange('G2').setValue(qty);  
  sheet2.getRange('I2').setValue(descrip.toUpperCase());  
  sheet2.getRange('B37').setValue(tgtdt1);  
  var protections = sheet.getProtections(SpreadsheetApp.ProtectionType.RANGE);
  for (var i = 0; i < protections.length; i++) {
    var p = protections[i];
    var rangeNotation = p.getRange().getA1Notation();
    var p2 = sheet2.getRange(rangeNotation).protect();
    p2.setDescription(p.getDescription());
    p2.setWarningOnly(p.isWarningOnly());
    if (!p.isWarningOnly()) {
      p2.removeEditors(p2.getEditors());
      p2.addEditors(p.getEditors());
      // p2.setDomainEdit(p.canDomainEdit()); //  only if using an Apps domain 
   }
  }
}