Google Forms Script send email malfunction

Question

I'm trying to make a google form of mine send me the contents of the form via email each time someone fills out the form. I'm using the instructions below, and I've followed them exactly. For some reason, however, when I fill out a form, I get an error saying "TypeError: Cannot call method "getRange" of null. (line 7, file "Code")"

I look at the line of code right above it, in which "s" is defined and I'm thinking that the problem is that getActiveSpreadsheet is not working for some reason. I think that maybe the script can't find the active sheet. Below is the code snippet which is causing the problem, according to the error email I get from google.

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

Whole piece of code I'm using;

function sendFormByEmail(e) 
{    
  // Remember to replace this email address with your own email address
  var email = "[email protected]"; 

  var s = SpreadsheetApp.getActiveSheet();
  var headers = s.getRange(1,1,1,s.getLastColumn()).getValues()[0];    
  var message = "";
  var subject = "Jones IT - New User Form";

  // The variable e holds all the form values in an array.
  // Loop through the array and append values to the body.

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

  // Insert variables from the spreadsheet into the subject.
  // In this case, I wanted the new hire's name and start date as part of the
  // email subject. These are the 3rd and 16th columns in my form.
  // This creates an email subject like "New Hire: Jane Doe - starts 4/23/2013"
  subject += e.namedValues[headers[1]].toString() + " - starts " + e.namedValues[headers[2]].toString();

  // Send the email
  MailApp.sendEmail(email, subject, message); 

}

Jones IT Jones IT · Accepted Answer · 2015-02-13T18:13:11

Ok, thank you, that makes sense but when I changed my code to use that code snippet I get this error.

2/12/15 4:56 PM sendFormByEmail TypeError: Cannot call method "getRange" of null. (line 9, file "Code") formSubmit 2/12/15 4:56 PM

Line 9 is the line with the function call for sheet.getRange().

  var s = SpreadsheetApp.openById('1WYhDQct8vF-3rlca9dP6lYC2Z23vLLofwJt_DpQU--c');
  var sheet = s.getSheetByName('Jones IT - New User Form (Responses)');
  var headers = sheet.getRange(1,1,1,s.getLastColumn()).getValues()[0];

Is there any way you recommend me changing my code to get the desired result. I don't really care what I have to change, I just want it to email me the contents of the forms when submitted.

Google Forms Script send email malfunction

2 Answers