How to write the name of script-created sheet to a cell in that sheet?

Question

I'm a teacher, and I frequently need to copy blank grading rubrics, one copy per student in my class. The rubric is a Google Sheet. I cobbled together a script that will (1) access another sheet that contains the class roster, (2) read the students' names off the roster, and (3) create a new spreadsheet for each student and save it with the student's name as the file name. All of that works (it's super slow for some reason, but it works).

Now I'm trying to write the student's name to a cell on that student's copy of the rubric. My idea was to write the student's name to the relevant cell on the original rubric, then save a copy of that under the student's name. But that part doesn't work; the name in the cell does not match the file name. Instead, the name in the cell matches the prior student's name. Here's the code:

function onerubricperstudent(){

//"roster" is the roster spreadsheet. Change the value in quotation marks to whatever your roster's ID is.  
  var roster = SpreadsheetApp.openById("1SN2x6bKzga6bRwwvt5diuaETBiXeHqY7bLJMY5If9js");

//"sheet" is sheet to be copied  
  var sheet = SpreadsheetApp.getActiveSpreadsheet();

//range is the range on the roster sheet containing the student names  
  var range = roster.getRange('Sheet1!A1:A17'); 

//do this as many times as there are students in the class
  for (var i = 1; i < 18; i++) {

//get the next student name    
    var studentname = range.getCell(i, 1);



//log the name (for troubleshooting if the script fails)
    Logger.log(studentname.getValue());


//Write the student's name to the name cell on the sheet

    sheet.getRange('B2').setValue(studentname.getValue());


//Make a copy of the sheet and save it with the student's name    
    DriveApp.getFileById(sheet.getId()).makeCopy(studentname.getValue());
  }
}

The line that does not work as expected is:

sheet.getRange('B2').setValue(studentname.getValue());

What am I doing wrong?

Note: As a workaround, I added this OnOpen script, which copies the sheet name to the student-name cell when I open the rubric to grade the student's work:

function onOpen() {
  var sheetname = SpreadsheetApp.getActive().getName();
  var sheet = SpreadsheetApp.getActiveSpreadsheet();
  sheet.getRange('B2').setValue(sheetname);

}

This gets the job done, but I'd still like to know what's wrong with the original code.

Thanks!

Will Will · Accepted Answer · 2016-09-03T09:08:00

Your script does successfully set the value of cell B2 but the copy of the spreadsheet does not include the pending changes. In order for pending changes to be copied within the for loop, it appears that you must call SpreadsheetApp.flush(). This will apply all pending Spreadsheet changes (see docs on flush()).

Here is some code:

function onerubricperstudent(){

  var roster = SpreadsheetApp.openById("YOUR-ROSTER-SHEET-ID-HERE");
  var rubricSheet = SpreadsheetApp.getActiveSpreadsheet();
  var rubricFile = DriveApp.getFileById(rubricSheet.getId());

  // replace the worksheet name and desired range with your name/range below
  var range = roster.getRange('Roster Master!A2:A21'); 
  var studentNames = range.getValues();

  for (var i = 0; i < studentNames.length; i++) {
    rubricSheet.getRange('B2').setValue(studentNames[i]);

    // SpreadsheetApp.flush() "forcefully" applies all pending Spreadsheet changes.
    SpreadsheetApp.flush();

    rubricFile.makeCopy(studentNames[i]);
  }
}

You also note that creating copies with DriveApp seems slow. That is also my experience. Unfortunately, I don't think this can be optimized (if someone has tips - it would be great to see them). There is another question on this here: Google Apps Script : copying spreadsheet is very slow (but the answer isn't super helpful).

Here is an option: If you don't need individual spreadsheet files, you might want to consider making new sheets within the same spreadsheet. This would be significantly faster.

Here is some code:

function onerubricperstudent(){

  var roster = SpreadsheetApp.openById("YOUR-ROSTER-SHEET-ID-HERE");
  var rubricSpreadSheet = SpreadsheetApp.getActiveSpreadsheet();
  var rubricFile = DriveApp.getFileById(rubricSpreadSheet.getId());

  // replace the sheet name with the name of the sheet you want to use as a template
  var templateSheet = rubricSpreadSheet.getSheetByName("Rubric Master");

  var range = roster.getRange('Roster Master!A2:A4'); 
  var studentNames = range.getValues();

  for (var i = 0; i < studentNames.length; i++) {
    var newSheet = rubricSpreadSheet.insertSheet(studentNames[i], i+1, {template: templateSheet})
    newSheet.getRange('B2').setValue(studentNames[i]);
  }
}

How to write the name of script-created sheet to a cell in that sheet?

1 Answers