Google Form Script - Email Submission

Question

I'm trying to get google to email me the submitted information from a form. I can get it to work with the default sheet (Form Responses) But I'm using a seperate sheet called "ConversionTracker" that actually uses the query function to import the data from the default sheet to the second sheet so I can use formulas within the responses. Basically I want the email script to reference the second sheet rather than the first sheet when it sends the email of the last submission.. Every time I use the script I have it sends the data from the first sheet (Form Responses) Rather than Sheet named "Conversion Tracker" I've tried different variations of the script but I can't seem to get it. See script below.

function Initialize() {

 try {

var triggers = ScriptApp.getProjectTriggers();

for (var i in triggers)
  ScriptApp.deleteTrigger(triggers[i]);

ScriptApp.newTrigger("EmailGoogleFormData")
  .forSpreadsheet(SpreadsheetApp.getActiveSpreadsheet())
  .onFormSubmit().create();

 } catch (error) {
throw new Error("Please add this code in the Google Spreadsheet");
 }
 }

  function EmailGoogleFormData(e) {

if (!e) {
  throw new Error("Please go the Run menu and choose Initialize");
}

  try {

if (MailApp.getRemainingDailyQuota() > 0) {


  // You may replace this with another email address
  var email = "dustin.****@b****.com";

  // Enter your subject for Google Form email notifications
  var subject = "Google Form Submitted";

  var key, entry,
    message = "",
    ss = SpreadsheetApp.getActiveSheet(),
    sheet = ss.getSheets()["ConversionTracker"],
    ss.setActiveSheet(sheet),
    cols = ss.getRange(1, 1, 1, ss.getLastColumn()).getValues()[0];

  // Iterate through the Form Fields
  for (var keys in cols) {

    key = cols[keys];
    entry = e.namedValues[key] ? e.namedValues[key].toString() : "";

    // Only include form fields that are not blank
    if ((entry !== "") && (entry.replace(/,/g, "") !== ""))
      message += key + ' :: ' + entry + "\n\n";
  }

  MailApp.sendEmail(email, subject, message);
}
 } catch (error) {
   Logger.log(error.toString());
 }
   }

The script that works and sends the data from the first sheet is:

function Initialize() { try {

var triggers = ScriptApp.getProjectTriggers();

for (var i in triggers)
  ScriptApp.deleteTrigger(triggers[i]);

ScriptApp.newTrigger("EmailGoogleFormData")
  .forSpreadsheet(SpreadsheetApp.getActiveSpreadsheet())
  .onFormSubmit().create();
} catch (error) {
throw new Error("Please add this code in the Google Spreadsheet");   } }   function EmailGoogleFormData(e) {
if (!e) {
throw new Error("Please go the Run menu and choose Initialize");   }
try {

if (MailApp.getRemainingDailyQuota() > 0) {

  // You may replace this with another email address
  var email = "dustin****@b****.com";

  // Enter your subject for Google Form email notifications
  var subject = "Google Form Submitted";

  var key, entry,
    message = "",
    ss = SpreadsheetApp.getActiveSheet(),
    cols = ss.getRange(1, 1, 1, ss.getLastColumn()).getValues()[0];

  // Iterate through the Form Fields
  for (var keys in cols) {

    key = cols[keys];
    entry = e.namedValues[key] ? e.namedValues[key].toString() : "";

    // Only include form fields that are not blank
    if ((entry !== "") && (entry.replace(/,/g, "") !== ""))
      message += key + ' :: ' + entry + "\n\n";
  }

  MailApp.sendEmail(email, subject, message);
}   } catch (error) {
Logger.log(error.toString());   } }

Alan Wells Alan Wells · Accepted Answer · 2015-10-27T15:48:35

I don't think that you can use text inside the square brackets to get the sheet:

sheet = ss.getSheets()["ConversionTracker"]

I'd use the .getSheetByName() method.

getSheetByName("ConversionTracker");

The code:

ss = SpreadsheetApp.getActiveSpreadsheet();
sheet = ss.getSheetByName("ConversionTracker");

Google Form Script - Email Submission

1 Answers