CODEWITHSUNDEEP
google-apps-script
Random
Random

Reputation: 47

variables seem undefined but I can't find it

script failing with Invalid email: undefined (line 29, file "newCode")

Have worked through some errors but am stuck on this. Besides the error message I found a debugger option and ran that. It didn't make a lot of sense but this line seem to match the complaint. "emailAddress undefined"

I feel like it might have to do with what I've done here:

    var emailAddress = row[3]; // columnD

All I think I did in the borrowed code was change which column I want to pull the info from. In this case an email address. As far as I know Column D in Google Sheets is referenced as "3" ColA=0, ColB=1...ColD=3.

I can't hard code the email address as it will vary one row to the next. Can someone advise how this is in error?

    function Initialize() {
      var triggers = ScriptApp.getProjectTriggers();
      for(var i in triggers) {
        ScriptApp.deleteTrigger(triggers[i]);
      } 
      ScriptApp.newTrigger("SndMail()")
      .forSpreadsheet(SpreadsheetApp.getActiveSpreadsheet())
      .onFormSubmit()
      .create();
    }

    // Write a value to column G for rows where an email sent successfully and sends non-duplicate emails with data from the current         spreadsheet.
    function SndMail() {
      var EMAIL_SENT = 'Sent';
      var sheet = SpreadsheetApp.getActiveSheet();
      var startRow = 2; // First row of data to process
      var numRows = 75; // Number of rows to process. 
      // Fetch the range of cells A2:G76
      var dataRange = sheet.getRange(startRow, 2, numRows);
      // Fetch values for each row in the Range.
      var data = dataRange.getValues();
      for (var i = 0; i < data.length; ++i) {
        var row = data[i];
        var emailAddress = row[3]; // columnD
        var message = 'Please create a Logistics Case and log the CAS# for: <br/> row[4 + 5]'; // columnE
    var mailout = row[6]; // columnG
        if (mailout != EMAIL_SENT) { // Prevents sending duplicates
          var subject = 'Logistics Claim case request';
          MailApp.sendEmail(emailAddress, subject, message);
          sheet.getRange(startRow + i, 3).setValue(Sent);
          // Make sure the cell is updated right away in case the script is interrupted
          SpreadsheetApp.flush();
        }
      }
    }

What should be happening is that when a google form is submitted the sheet is updated and a script runs to email the intended recipient (email addy in Column D) and subsequently put the text "Sent" in Column G. Which is then filters subsequent function runs, avoiding duplicate email sends.

Upvotes: 1

Views: 57

Answers (1)

Tanaike
Tanaike

Reputation: 201388

How about this modification?

Modification point:

  • When value is retrieved from sheet.getRange(startRow, 2, numRows), only column "B" is retrieved.
    • By this, var emailAddress = row[3] becomes undefined.

I think that the reason of your issue is this. In order to avoid the error, how about modifying as follows?

Modified script:

From: 
var dataRange = sheet.getRange(startRow, 2, numRows);
To: 
var dataRange = sheet.getRange(startRow, 2, numRows, sheet.getLastColumn());

or

var dataRange = sheet.getRange(startRow, 2, numRows, 7); // A2:G76 ?

Reference:

If I misunderstood your question, I apologize.

Upvotes: 1

Related Questions