Automating Sheet Creation in newly created Google Sheet+Form

Question

I am trying to create a script that adds a Google Form response sheet to a newly created Google Sheet, then creates subsequent Sheets in the same Spreadsheet. The sheets are named upon creation. Here is my code thus far:

var statusSheet, form, ss = SpreadsheetApp.openById("SPREADSHEET_ID")
if (ss.getFormUrl() == null){
form = FormApp.create("NEWFORM")
    .setCollectEmail(true)
    .setProgressBar(false)
    .setRequireLogin(true)
    .setDestination(FormApp.DestinationType.SPREADSHEET, ss.getId())
Utilities.sleep(3000)
  }
  else{form = FormApp.openByUrl(ss.getFormUrl())}
  if (ss.getSheets()[1].getRange("A1").getValue() != 'Status'){
ss.getSheets()[1].setName("Status")
.getRange("A1:C8")
.setValues([["Status",'',"Staff"],["Staff",'',"Student"],["Students",,"building"],["Penalty Box",'',"scope"],["Current Offenders",'',"formCreated"],["Audit Log",'',"formPopulated"],['','',"TimeTrigger"],["Form ID",'',"EditTrigger"]])
Utilities.sleep(3000)
  }
  statusSheet = ss.getSheetByName("Status")
  ss.setActiveSheet(ss.getSheets()[(ss.getSheets().length)-1])
while (ss.getSheets().length < 7){
var newSheet = ss.insertSheet()
var range = statusSheet.getRange(newSheet.getIndex(),2, 1, 1)
range.setValue(newSheet.getSheetId())
newSheet.setName(statusSheet.getRange(newSheet.getIndex(),1, 1, 1).getValue())
Logger.log(newSheet.getName())
}
  var staffSheet = ss.getSheetByName("Staff")
  var studentsSheet = ss.getSheetByName("Students")
  var PenaltyOUSheet = ss.getSheetByName("OrgUnits")
  var currentBoxUsersSheet = ss.getSheetByName("Current Users")
  var auditLogSheet = ss.getSheetByName("Audit Log")

It seems that all of the sheets are created, except "Staff". This is the case whether the form is created and/or the values are populated either before executing the function or during the execution. To make matters worse, the Logger within the for Loop isn't recording errors.

I have tried encasing the whole function in a try/catch statement, but there are no indications what the error might be other than the reference to staffSheet fails, because no sheets are named "Staff".

Bigfella Bigfella · Accepted Answer · 2015-11-10T06:15:44

Moving the copied range down a row fixed the issue. I'm not positive why it was skipping the Staff sheet rather than the Audit Log, but I will figure that out after I get everything else fixed.

Automating Sheet Creation in newly created Google Sheet+Form

1 Answers