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.
This can be done with a script. Enter the following in Script Editor, found under Tools. Change the 'Name' to whatever is the name of the form field that should be used.
function formSheet(e) {
var sheetName = e.namedValues['Name'][0];
var sheet = SpreadsheetApp.getActiveSpreadsheet().insertSheet(sheetName);
sheet.getRange(1, 1, 1, e.values.length).setValues([e.values]);
}
Then set a trigger using "Resources -> current project's triggers" in the menu. It should be "from spreadsheet", "on form submit".
This function also copies the submitted value to the first row of the new sheet.
Note: there cannot be two sheets with the same name, so if some name is submitted more than once, sheet creation will fail.
References: Event object, Sheet class methods.
Best Answer
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
and then go to My Menu and select Create New Tabs
Template copy/duplication you may want to check:
For that checkout hyperlinks and harness the power of gid numbers