I have one source Google Sheet that copies to a number of different Google Sheets for different students. The information copied is for different subjects, each a different sheet on the student's personal Sheets file.
The source sheet is set up to cope with differently sized classes, but my problem is trying to break
the code when it reaches a section with no information. I don't know why my break statement is not working, since I know that copysheet
is null
as it results in an error every time the code runs.
function cloneGoogleSheet() {
//select source sheet
var sourcesheet = SpreadsheetApp.openById('some id');
var j;
for (var j = 1; j < 36; j++) {
//select student sheets to copy to
var studentid = sourcesheet.getSheetByName("Sheet11")
.getRange(j, 2).getValue();
var i;
for (var i = 1; i < 14; i++) {
//select subject name for referencing different sheets
var subject = sourcesheet.getSheetByName("Sheet6")
.getRange(i + 1, 7 * j - 5).getValue();
//find correct subject sheet on student sheet
var copysheet = SpreadsheetApp.openById(studentid).getSheetByName(subject);
if (copysheet != null) {break;}
else {
//copy "importrange" formula from sourcesheet
var info = sourcesheet.getSheetByName("Sheet6")
.getRange(i + 1, 7 * j).getValue();
//copy teacher name from sourcesheet
var teachername = sourcesheet.getSheetByName("Sheet6")
.getRange(i + 1, 7 * j - 4).getValue();
//copy student name
var studentname = sourcesheet.getSheetByName("Sheet6")
.getRange(2, 7 * j - 6).getValue();
//paste "importrange" formula into student sheet
copysheet.getRange(6, 2).setValue(info);
//paste teacher name into student sheet
copysheet.getRange(3, 2).setValue(teachername);
//paste student name into student sheet
copysheet.getRange(1, 2).setValue(studentname);
}
}
}
}
Change
if (copysheet != null) {break;}
to
if (copysheet == null) {break;}