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.
Try the following script:
var WOMEN_COL_IDX=1; // WOMEN are listed in column 1 (column B)
var MEN_COL_IDX=2; // MEN are listed in column 2 (column C)
var ALT_COL_IDX=3; // ALTERNATES are listed in column 3 (column D)
function countVotes(what) {
var colIdx;
switch (what.toUpperCase()) {
case "WOMEN": colIdx = WOMEN_COL_IDX; break;
case "MEN": colIdx = MEN_COL_IDX; break;
case "ALTERNATES": colIdx = ALT_COL_IDX; break;
default: throw new Error("Unsupported argument: " + what + " - specify MEN, WOMEN or ALTERNATES." );
}
var ranges = Array.prototype.slice.call(arguments, 1); // All arguments except the first to this function are ranges to count
// Collect votes in an object literal
var votes = {};
for (var i = 0; i < ranges.length; i++) {
countRange(ranges[i], colIdx, votes);
}
// Transfer votes object to an array
var result = [];
for (var name in votes) {
if (!votes.hasOwnProperty(name)) {
continue;
}
result.push([name, votes[name]]);
}
// Now sort the results by votes, descending order
result.sort(function(a, b) {
return b[1] - a[1];
});
return result.slice(0, 7); // Return only 8 first rows
}
function countRange(data, colIdx, votes) {
for (var i = 0; i < data.length; i++) { // Iterate rows on this sheet
var row = data[i];
var vote = row[colIdx];
if (vote == "") {
continue; // Skip blank cells
}
if (votes[vote]) {
votes[vote] = votes[vote] + 1;
} else {
votes[vote] = 1;
}
}
}
The script returns a list of names and their votes, sorted by votes descending. Only the 8 most popular names are returned.
To use the script, you must first install it: In your spreadsheet, click Tools -> Script editor, and paste the script. From the drop-down menu in the Script Editor Toolbar, select countVotes, and click the Run button (you only need to do this once). This will give you an authorization dialog, in which you must accept.
In the cell where you want to display the result of the script (in your example spreadsheet that is B2
in the Master
sheet), enter the following formula:
=countVotes("WOMEN", Paul!A2:D; John!A2:D; David!A2:D; Sally!A2:D)
This means: Count the names in the WOMEN
column in the ranges A2:D
on everyone's sheet.
Repeat this formula for the other results, so in D2
enter
=countVotes("MEN", Paul!A2:D; John!A2:D; David!A2:D; Sally!A2:D)
and in F2
enter
=countVotes("ALTERNATES", Paul!A2:D; John!A2:D; David!A2:D; Sally!A2:D)
I have set up an example spreadsheet to demonstrate the script. Feel free to copy it to your own Google Drive for further experimentation.
Best Answer
There is a widely used script for this:
Following that, you refer to it by placing
=sheetnames()
in a cell.PS: It is a very useful way when you make a range out of it and use it in formulas.