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.
I've been struggling with the same problem. Instead of writing a custom function, I add a different query string to spreadsheet_url
in IMPORTRANGE
in the hope that each time the page is refreshed, Google thinks it needs to fetch data from a new spreadsheet. I simply append a new timestamp to make the url unique each time. It's a blatant hack, but it has been working for me across a lot of sheets.
My formula previously looked something like:
=IMPORTRANGE("https://docs.google.com/spreadsheets/d/123123123123/edit#gid=1816927174","'Sheet1'!A1:B25")
And now it looks like:
=IMPORTRANGE("https://docs.google.com/spreadsheets/d/123123123123/edit#gid=1816927174"&"?"&now(),"'Sheet1'!A1:B25")
Update:
This method no longer works since Google no longer permits now()
inside importrange()
. See comment from Hugh below.
Best Answer
Sorting a range (for example A and B columns) is a built-in function:
To duplicate to another sheet, you have to first decide how wide the original ones are going to get. For example, suppose each sheet only uses five columns A:E.
Then on Sheet8 you can enter
and so on. This way, the content of Sheet1 is in columns A:E of Sheet8, the content of Sheet2 is in columns G:K, and so on.