Google Form Send Email to Owner after Response Submission

Question

I am just starting Google Forms. I need to email the form owner (myself and some others) a response as soon the the user submit the data. I need the data in the email which would include fields and their values that were submitted by the user as soon as they submit the form.

I am unable to use add-on as per my google account settings via my employer where add-on are blocked.

I am exploring app scripts but with little success as I am very new. As there some sample codes to help me get started with create a basic script to send email.

I have the following Code:

function sendFormByEmail(e) 
{    

  var email = "[email protected]"; 

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


  for(var i in headers) 
    message += headers[i] + ': '+ e.namedValues[headers[i]].toString() + "\n\n";     


  subject += e.namedValues[headers[2]].toString() + " - starts " + e.namedValues[headers[15]].toString();


  MailApp.sendEmail(email, subject, message); 

}

Then I added this script in the form trigger like so:

I tried submitting the form but nothings heppens. How do I know that the script ran or there was a problem?

If I try to run this in the script editor :

It gives me an error : TypeError: Cannot call method "getRange" of null. (line 7, file "Code")

Update

I tested the email functionality and it worked. So the problem has to be in Spread Sheet value retrieval.

function sendFormByEmail(e) 
{    

  var email = "[email protected]"; 
  MailApp.sendEmail(email, "Test", "Test"); 
}

I also created a excel file on my google drive that holds these response from google form

Final Solution

function testExcel() {

  var email = "[email protected]"; 
  var s = SpreadsheetApp.openById("GoogleDocsID");
  var sheet = s.getSheets()[0];
  var headers = sheet.getRange(1,1,1,sheet.getLastColumn()).getValues()[0]; 
  var datarow = sheet.getRange(sheet.getLastRow(),1,1,sheet.getLastColumn()).getValues()[0];
  var message = "";

  for(var i in headers)
  {
    message += "" + headers[i] + " : " + datarow[i] + "\n\n";
    //Logger.log(message);
  }

  MailApp.sendEmail(email, "Submitted Data Test", message); 
}

Answer 1

Here is a shell for you to start with. I use this code for a very similar reason. This shell includes creating a Google Doc from template and adding data from the sheet into that Doc. You can use similar methods to set variables and add them into the email. I use an html template file(s) to manage exactly what is being sent each time.

The merge portion checks through the Doc (you can set it to look through html file) and finds my tags using RegEx; structed as so: <<columnHeader>>. In this way, You have a consistent template that replaces those tags with the data, in that column, for that row. Modify to your needs as you see fit.

This also displays the progress of the merge. That way, it won't repeat your emails/ merge.

NOTE: There are several data points missing as I removed the personal information; it won't run straight from this sample. You will have to add your document IDs, correct for variable placement, etc.

function mergeApplication() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName("");
  var formSheet = ss.getSheetByName("");
  var lastRow = formSheet.getLastRow();
  var lastColumn = sheet.getMaxColumns();
  function checkAndComplete() {
    var urlColumn = lastColumn;
    var checkColumn = (urlColumn - 1);
    var checkRange = sheet.getRange(2, checkColumn, (lastRow - 1), 1);
    var check = checkRange.getBackgrounds();
    var red = "#ff0404";
    var yellow = "#ffec0a";
    var green = "#3bec3b";
    for (var i = 0; i < check.length; i++) {
      if (check[i] == green) {
        continue;
      } else {
        var statusCell = sheet.getRange((i+2), checkColumn, 1, 1);
        var urlCell = sheet.getRange((i+2), urlColumn, 1, 1);
        var dataRow = sheet.getRange((i+2), 1, 1, (lastColumn - 2));
        function mergeTasks() {    
          function docCreator() {
            var docTemplate1 = DriveApp.getFileById("");
            var docTemplate2 = DriveApp.getFileById("");
            var folderDestination = DriveApp.getFolderById("");
            var clientName = sheet.getRange((i+2), 2, 1, 1).getValue();
            var rawSubmitDate = sheet.getRange((i+2), 1, 1, 1).getValue();
            var submitDate = Utilities.formatDate(rawSubmitDate, "PST", "MM/dd/yy");
            var typeCheck = sheet.getRange((i+2), (checkColumn - 1), 1, 1).getValue();
            if (typeCheck == "Type 1") {
              var docToUse = docTemplate1;
              var emailBody = HtmlService.createHtmlOutputFromFile("").getContent();
            } else {
              var docToUse = docTemplate2;
              var emailBody = HtmlService.createHtmlOutputFromFile("").getContent();
            } 
            var docName = "" + clientName + " - " + submitDate;
            var docCopy = docToUse.makeCopy(docName, folderDestination);
            var docId = docCopy.getId();
            var docURL = DriveApp.getFileById(docId).getUrl();
            var docToSend = DriveApp.getFileById(docId);
            var docInUse = DocumentApp.openById(docId);
            var docBody = docInUse.getBody();
            var docText = docBody.getText(); 
            function tagReplace() {
                var DOBCell = sheet.getRange((i+2), 3, 1, 1);
                var rawDOB = DOBCell.getValue();
                if (rawDOB !== "") {
                  var DOB = Utilities.formatDate(rawDOB, "PST", "MM/dd/yy");
                } else {
                  var DOB = ""
                  }
              var taggedArray = docText.match(/\<{2}[\w\d\S]+\>{2}/g);
              var headerArray = sheet.getRange(1, 1, 1, (lastColumn - 2)).getValues();
              var dataArray = dataRow.getValues();
              dataArray[0][2] = DOB;
              var strippedArray = [];
              function tagStrip() {
                for (var t = 0; t < taggedArray.length; t++) {
                  strippedArray.push(taggedArray[t].toString().slice(2, -2));
                }
              }
              function dataMatch() {
                for (var s = 0; s < strippedArray.length; s++) {
                  for (var h = 0; h < headerArray[0].length; h++) {
                    if (strippedArray[s] == headerArray[0][h]) {
                      docBody.replaceText(taggedArray[s], dataArray[0][h]);
                    }
                  }
                }
                docInUse.saveAndClose();
              }
              tagStrip(); 
              dataMatch();
            }           
            function emailCreator() {
              var emailTag = sheet.getRange((i+2), (checkColumn - 9)).getValue();
              var emailSubject = "" + clientName;
              MailApp.sendEmail({
                to: emailTag,
                subject: emailSubject,
                htmlBody: emailBody,
                attachments: [docToSend.getAs(MimeType.PDF)],
                replyTo: "",
              });
            }
            tagReplace();
            statusCell.setBackground(yellow);
            emailCreator();
            urlCell.setValue(docURL);
          }
          statusCell.setBackground(red);
          docCreator();
          statusCell.setBackground(green);
        }
        mergeTasks();
      }
    }
  }
  checkAndComplete();
}