Google-sheets – Auto create and name new sheet in Google Spreadsheet

google sheetsgoogle-apps-script

How can I automatically create and name new sheets in a google spreadsheet from a list of names, such as a student roll sheet? I would like each new sheet to be created when I add corresponding names to a list in a spreadsheet column. The new sheets can be in the same spreadsheet.

Best Answer

The following piece of script will create a menu item in the spreadsheet named Maintenance. There are three menu option present named:

Add Student one by one, Add Student's at once (FIFO), Add Student's at once (LIFO), Delete Sheets

function onOpen() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var menuEntries = [{name: "Add Student one by one", functionName: "addSheet"},
    {name: "Add Student's at once (FIFO)", functionName: "addSheets"},
    {name: "Add Student's at once (LIFO)", functionName: "addSheetsLIFO"},
    {name: "Delete Sheets", functionName: "delSheets"}
  ];
  ss.addMenu("Maintenance", menuEntries);
}

The next piece of code will create a new sheet, only if all information is entered properly. Once the information is entered, select the student that needs to be added and press Add Student:

function addSheet() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sh = ss.getActiveSheet();
  var row = sh.getActiveRange().getRowIndex();
  var rData = sh.getRange(row, 1, 1, 3).getValues();

  if (row == 1) {
    ss.toast("This is the header");    
    return
  }

  if(rData[0][0] != null || rData[0][1] != null || rData[0][2] != null) {
    try {      
      ss.insertSheet(rData[0][2]);
      ss.setActiveSheet(ss.getSheets()[0]);
      sh.getRange("D"+(row)).setValue(new Date());
    } catch(e) {
      throw 'This student allready has a sheet. Try another sheet name.';      
    }
  }
}

The third piece of code will create new sheets, based on the sheet names, press Add Student's at once (FIFO):

function addSheets() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sh = ss.getActiveSheet();
  var rData = sh.getDataRange().getValues();

  var message = [];    
  for(var i=1, len=rData.length; i<len; i++) {
    if(rData[i][3] == null || rData[i][3] == "") {   
      if(rData[i][0] != null || rData[i][1] != null || rData[i][2] != null) {
        try {      
          ss.insertSheet(rData[i][2]);
          ss.setActiveSheet(ss.getSheets()[0]);
          sh.getRange("D"+(i+1)).setValue(new Date());
        } catch(e) {
          message.push("row " + (i+1));
        }
      }    
    }
  }
  ss.toast("These sheets allready exist: " + message);
  ss.setActiveSheet(ss.getSheets()[0]);
}

The fourth piece of code will create new sheets, based on the sheet names, but by itterating backwards, press Add Student's at once (LIFO):

function addSheetsLIFO() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sh = ss.getActiveSheet();
  var rData = sh.getDataRange().getValues();

  var message = [], i=rData.length; 
  while(i--) {
    if(rData[i][3] == null || rData[i][3] == "") {   
      if(rData[i][0] != null || rData[i][1] != null || rData[i][2] != null) {
        try {      
          ss.insertSheet(rData[i][2]);
          ss.setActiveSheet(ss.getSheets()[0]);
          sh.getRange("D"+(i+1)).setValue(new Date());
        } catch(e) {
          message.push("row " + (i+1));
        }
      }    
    }
  }
  ss.toast("These sheets allready exist: " + message);
  ss.setActiveSheet(ss.getSheets()[0]);
}

The last script is a bonus to delete all the created sheets, execpt the first sheet, press Delete Sheets:

function delSheets() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var shs = ss.getNumSheets();

  for(var i=shs-1;i>0;i--){
    ss.setActiveSheet(ss.getSheets()[i]);
    ss.deleteActiveSheet();
  }
  ss.setActiveSheet(ss.getSheets()[0]);
  ss.getRange("D2:D").clear();
}

I've prepared an example file, to see things happening: Add Student to New Sheet.

Related Topic