0

I am designing a questionnaire that requires respondents to input their name and a company code that is specific to their employer. I would like to have a script so that a different company will get an email notification depending on which company code is specified. I created a Google Sheet in the Form that lists company codes, corresponding emails, and messages.

I have found a script that does this but I have to tailor it to my own form. Can anyone do this or has a better code?

Link to Demo Form: Here

Link to demo spreadsheet: Here

//using the obtained settings to set up email info
function onOpen(e) {
   FormApp.getUi().createMenu('Authorizer').addItem('Authorize', 'authorize').addToUi();
      
 }

//Easily authorize script to run from Form Editor interface
function authorize(){

var respEmail = Session.getActiveUser().getEmail();
MailApp.sendEmail(respEmail,"Form Authorizer", "Your form has now been authorized to send you emails");

}

function setSettings(e){

  
  var response = e.response; 
  var settings = getSettings();
  var emailSubject = settings[1][2];
  var emailMsg = settings[1][3];
  var formCompany = getResponse(response,"Company Code");
  var formName = getResponse(response,"Name");
  var emailAddr;
  var companycode;
  
  //loop through and grab the selected school from the Settings spreadsheet.
  for (i in settings){
    if (settings[i][0] === formcompanycode){
      
      school = settings[i][0];
      emailAddr = settings[i][1];    
        } 
       
  }

//build email and send to email send function
emailMsg = emailMsg + '\n' + 'Company Code: ' + formSchool +  '\n' + 'Name: ' + formName;
mailSendSimple(emailAddr,emailSubject,emailMsg);

 }
//function to find the selected company code. 
function getResponse(response,formItem){ 

  var selected;  
  //loop through the form response to find the company code 
  for (var i = 0; i < response.getItemResponses().length; i++){  
 if (formItem == response.getItemResponses()[i].getItem().getTitle()){
    selected = response.getItemResponses()[i].getResponse();       
   }  
  }
  return selected;
} 
 //grabbing general settings from spreadsheet sheet Settings
function getSettings() {
  var form = FormApp.getActiveForm();
  var ssID = form.getDestinationId();
  var ss = SpreadsheetApp.openById(ssID);
  var sheet = ss.getSheetByName("Settings");
  var lastRow = sheet.getLastRow();
  var lastColumn = sheet.getLastColumn();
  var range = sheet.getRange(1,1,lastRow,lastColumn);
  var values = range.getValues();
  return values;  
}


//function to send out mail
function mailSendSimple(emailAddr,emailSub, emailMsg){
MailApp.sendEmail(emailAddr,emailSub, emailMsg); //{cc: ccUser}
  
}
ptello
  • 1
  • 2
  • Did you already tried to adapt the code that you found? What problems did you faced? – Rubén Feb 09 '16 at 23:55
  • I've replaced "school" and "student" that I found in the code with the correlating value from my own settings. It still hasn't been working where it will send an email even after I've authorized it. – ptello Feb 10 '16 at 14:13
  • Did you already created the on form submit installable trigger? – Rubén Feb 10 '16 at 14:23
  • I have already added a Getsettings>from form>on form submit trigger. I have a feeling There is some inconsistency between the code itself and what it's trying to pull from form but I'm not too sure how to code that fix myself. – ptello Feb 10 '16 at 15:28
  • Add the details that you shared in the last comments to the question. Also create a demo form/spreadsheet share them with anyone with the link and add the links to the question too. – Rubén Feb 10 '16 at 15:32
  • I updated the snippet with the code I had edited and the links to a copy of the form and spreadsheet if that helps. – ptello Feb 10 '16 at 16:16
  • I suggest to change the sharing settings to view only. Otherwise there is a risk that people accessing at different times see different things. – Rubén Feb 10 '16 at 16:22
  • Is the formcompanycode line the only thing that needs to be revised? – ptello Feb 10 '16 at 18:28
  • No. That is why I added two sections to my [answer](http://stackoverflow.com/a/35320947/1595451). If an answer is not clear you could request clarifications adding a comment to the related answer. – Rubén Feb 10 '16 at 18:31
  • I suggested an edit to add a mention that your script is bounded to a Google Form but it was rejected by splited votes (2 Approved Vs 3 Rejected). Consider add yourself that mention. – Rubén Feb 12 '16 at 01:40

1 Answers1

1

Partial answer

In formcompanycode used in the line

if (settings[i][0] === formcompanycode){

is not defined.

Remarks

See How can I test a trigger function in GAS?. The technique described there will help you to debug your code and find other errors.

Community
  • 1
  • 1
Rubén
  • 34,714
  • 9
  • 70
  • 166