Google Script to email form answers and questions

问题:

I am trying to make a simple script that sends an email to a user who fills out a survey through a Google Form.

They will take the survey, answers are recorded in a spreadsheet, and this script will email them a copy of the questions (the headings), along with their answers (Should be the last row of the table.)

The email it is sent to was going to be the last column, which is not included in the email.

This was the script I was working on to get this working:

function sendFormByEmail(e) {

 var ss = SpreadsheetApp.getActiveSpreadsheet();
 var s = ss.getActiveSheet();

 var emailSubject = "EMAIL-SUBJECT-HERE";  
 var yourEmail    = "CURRENTLY-JUST-A-STATIC-EMAIL-ADDRESS"; //get this from a field.value then just cut off that field from being included in the email (just do column-1)...


 var headers = headersRange.getValues()[0];  //google api to get the header values.

 var lastRow = s.getLastRow();

 var message += headers.toString();
 message += (headersRange.getValue()[last]).toString(); //should get the last row of data?

  MailApp.sendEmail(yourEmail, emailSubject, message); 
}

回答1:

Add a trigger that executes whenever a form is submitted and attach this function to your trigger.
In your code, you can use e.values[] in your code to refer to the values submitted by the user. This way you will not have to read the spreadsheet

function  sendFormByEmail(e) {
  var email = e.values[0];  // assuming 1st field is email
  var firstName = e.values[1]; // assuming 2nd field is first name
  ...

}

回答2:

You will probably find it hard to read the results of an email with all the questions in one line, and all answers in another.

It's easy to loop through the headers & form values at once, and just skip over the email address if you wish.

function sendFormByEmail(e) {

  var emailSubject = "EMAIL-SUBJECT-HERE";  
  var emailQ = 1;  // Column number with email address

  var headers = SpreadsheetApp.getActiveSheet().getDataRange().getValues()[0];  //google api to get the header values.

  var message = "Your survey responses:\n\n";

  // Print Questions & Answers in pairs
  for (i=0; i < headers.length; i++) {
    if (i == emailQ) continue; // skip email address
    message += headers[i]+"\n"
             + e.values[i]+"\n\n";
  }

  MailApp.sendEmail(e.values[emailQ], emailSubject, message); 
}