Triggering copying and renaming of spreadsheet onFormSubmit

Question

Using onFormSubmit bound to the spreadsheet to which the form is linked, I wrote a script that copies another spreadsheet and renames it. Currently the new name is set within the script. The point of the form, though, is for teachers to enter their email, last name, first name and teacher number and then have a registration form (spreadsheet) sent to them for them to fill out. Too I want the new name of the copied spreadsheet to use the teacher's last name and first name. So far, my script is not working. Based on research, though, it seems that it out to. So, here is the initial script, which works:

function onFormSubmit() {
  const teacher = 'GuessKevin';
  const SourceFolder = DriveApp.getFolderById('1ARnOku-h49TAZytmEGSnckAKaoMZqffQ');
  const Files = DriveApp.getFileById('1_uHtcG-qLEeETGNbhRA4WNwQYcaltXbAtxgupYQNWLY');
  const FileRename = Files.makeCopy(teacher + '-Registration-FallFestival', SourceFolder);
}

And here is the new script, which isn't working, not even the copying of the spreadsheet part:

function onFormSubmit(e) {
  const formResponses = e.getResponses();
  const teacherEmail = formResponses[1].getResponse();
  const teacherName = formResponses[2].getResponse() + formResponses[3].getResponse();
  const teacherNum = formResponses[4].getResponse();
  const SourceFolder = DriveApp.getFolderById('1ARnOku-h49TAZytmEGSnckAKaoMZqffQ');
  const Files = DriveApp.getFileById('1_uHtcG-qLEeETGNbhRA4WNwQYcaltXbAtxgupYQNWLY');
  const FileRename = Files.makeCopy(teacherName + '-Registration-FallFestival', SourceFolder);
}

I've also tried using the form's Id, as follows, but that hasn't worked either:

function onFormSubmit() {
  const form = formApp.openById('1hKoK1FNLYohXqK3hhkNkQ6z-IQgzXGRUcjm0YEmvOE4');
  const formResponses = form.getResponses();
  const teacherEmail = formResponses[1].getResponse();
  const teacherName = formResponses[2].getResponse() + formResponses[3].getResponse();
  const teacherNum = formResponses[4].getResponse();
  const SourceFolder = DriveApp.getFolderById('1ARnOku-h49TAZytmEGSnckAKaoMZqffQ');
  const Files = DriveApp.getFileById('1_uHtcG-qLEeETGNbhRA4WNwQYcaltXbAtxgupYQNWLY');
  const FileRename = Files.makeCopy(teacherName + '-Registration-FallFestival', SourceFolder);
}

Any help is greatly appreciated.

Answer 1

Your script needs to use the Event Objects that apply to a spreadsheet-bound onFormSubmit triggered script.

The Event Objects can be displayed by using Logger.log(JSON.stringify(e)) but in general terms they are:

• values in an array named values (of which the first entry is the timestamp) or
• key/value pairs in an object named namedValues.

In this answer, I have used the values array.

Reminder:
The function makeTeacherSheet must be triggered by an installable onFormSubmit trigger. Since the function is attached to the spreadsheet, the trigger must be created from the same Spreadsheet "Triggers" tab.

---

function makeTeacherSheet(e) {
    Logger.log(JSON.stringify(e))
    var values = e.values
    var teacherEmail = values[1]
    var teacherName = values[2]+values[3]
    var teacherNum = values[4]
    Logger.log("DEBUG: email:"+teacherEmail+", Name: "+teacherName+", Number: "+teacherNum)
    var SourceFolder = DriveApp.getFolderById('1ARnOku-h49TAZytmEGSnckAKaoMZqffQ')
    var Files = DriveApp.getFileById('1_uHtcG-qLEeETGNbhRA4WNwQYcaltXbAtxgupYQNWLY')
    var FileRename = Files.makeCopy(teacherName + '-Registration-FallFestival', SourceFolder)
}