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.
Best Answer
You could try using an ARRAY FUNCTION I haven't tried it for this use case at the moment but i have used it in a situation which uses the .appendRow() function which I'm pretty sure the form response uses hence why your formula isn't present. Instead of dragging down your formula:
If not I would use something like
=COUNTUNIQUE(FORM_RESPONSES!H2:H)
in a separate sheet which should return the number of unique values in the entire H column. Or you could open the Script editor in the tools menu and try your hand at some javascript. Which could then even write an email and autom fill a templated word doc for you based on the conditions you mentioned.I highly recommend that option as automation rewards you with time, and coding is a lot of fun and easier than you may think.
Use developers.google.com/sheets to learn more about Apps Script and to find a scenario or tutorial for most levels of skill including beginner