Using data from var in subjectLine

Question

I'm modifying Amit's code ( found here: http://labnol.org/?p=20884) to try to send email with the data from a Google Form. But what I'm trying to grab is from his keys and columns. I want to specifically take the first 1 and 2 column's data from the row in question and use it as a var in the subject field. But the output (in email and when sent to asana) is listed as undefined. Where did I go wrong?

/*
   Send Google Form Data by Email v4.2
   Written by Amit Agarwal [email protected]
   Source: http://labnol.org/?p=20884
*/

/**
 * @OnlyCurrentDoc
 */

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 = "[email protected]";

      // Enter your subject for Google Form email notifications

      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";
        var first = entry[1];
        var last = entry[2];

              var subject = first+" "+last+": Interested Candidate";

      }

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

/* For support, contact developer at www.ctrlq.org */

Answer 1

entry is a string, defined here:

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

...which you later treat as an array:

    var first = entry[1];
    var last = entry[2];

At this point, first and last will both be undefined, because entry isn't an array. Further, this is inside a for loop that's traversing all the columns in the row - you can't see any bad side-effect from that, but these assignments and generation of a subject are happening multiple times.

That last clue suggests a better way to achieve your goal. Define the first and last variables before the loop, with default values. Then when looping over columns, watch for the columns containing the candidates' name, and update the default contents. Finally, after the loop, generate the subject line.

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 = "[email protected]";

      // Enter your subject for Google Form email notifications

      var key, entry,
        first = "unknown", last = "unknown",
        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";

        if (key == "first") {  // Assumes "first" is column header
          first = entry;
        }
        if (key == "last") {  // Assumes "last" is column header
          last= entry;
        }

      }

      var subject = first+" "+last+": Interested Candidate";

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