Google Apps Script for Form, Generate Unique ID Nu

2019-05-16 13:13发布

问题:

I created a simple Google form with the fields:

Name, E-mail, Salary and Request.

When the user completes the form, I want to send him/her an e-mail with the info. However, I want to use the "Request" field to plug in a unique number that the user can refer to if they need further correspondence. If the user enters anything in the 'Request' field, I want to discard it and use the number that I generate (both in the response and in the spreadsheet).

I've been able to piece together the script using other info I've found. It seems to work for everything except the e-mail response going back to the user.- It does not contain the number that I want to use in the 'Request' field, but instead sends back any input that the user puts in the 'Request' field. The spreadsheet looks ok (it has my number in the 'Request' field.)

Here's my script:

function sendFormByEmail(e) {      
  var capsht = SpreadsheetApp.getActiveSheet();  
  var caprow =  SpreadsheetApp.getActiveSheet().getLastRow();  
  capsht.getRange(caprow,5).setValue("Cap-"+caprow);  

  var admin = "admin@xxx.com";  

  try {  

    var recipient = e.namedValues["Email"];  
    var subject = "Capacity Request Form Received";    

    var s = SpreadsheetApp.getActiveSheet();  
    var headers = s.getRange(1,1,1,s.getLastColumn()).getValues()[0];      
    var message = "";      

    for (var i in headers)  
      message += headers[i] + ' = '+ e.namedValues[headers[i]].toString() + "\n\n";   
      MailApp.sendEmail(recipient, subject, message);   

    }   

  catch (error) {  
    MailApp.sendEmail(admin, "Error with form submission response email", error.message);  
  }  

}

回答1:

try it like this :

(added comment in code and deleted repetitions)

function sendFormByEmail(e)   
{      
var capsht = SpreadsheetApp.getActiveSheet();  
var caprow = capsht.getLastRow();  
var codenumber = "Cap-"+caprow
capsht.getRange(caprow,5).setValue(codenumber);  

var admin = "admin@xxx.com";  

try {  

var recipient = e.namedValues["Email"];  
var subject = "Capacity Request Form Received";    

var headers = capsht.getRange(1,1,1,capsht.getLastColumn()).getValues()[0];      
var message = "";      

for(var i in headers)  
if(i!=4){ 
message += headers[i] + ' = '+ e.namedValues[headers[i]].toString() + "\n\n";   
}else{  // if i==4 (column nr5)then use your code number
message += headers[i] + ' = '+ codenumber + "\n\n"; 
}
MailApp.sendEmail(recipient, subject, message);   

}   

catch (error)  
{  
MailApp.sendEmail(admin, "Error with form submission response email", error.message);  
}  

}