1
votes

Google Script send form values by email, error: Cannot read property "Timestamp" from undefined/Cannot read property "0" from undefined. I add two options with two bugs, the script receive the info from exel file. the code is below:

    function sendFormByEmail(e) {
    var Subject = "Subject";
    var Email = "email";
    var Key = "keynumber";
    var Editors = 0;
    var extraColumns = 8;

    if (useEditors) {
        var editors = DocsList.getFileById(Key).getEditors();
        if (editors) {
            var notify = editors.join(',');
        } else var notify = Email;
    } else {
        var notify = Email;
    }

    var s = SpreadsheetApp.getActive().getSheetByName("Form Responses 1");
    if (extraColumns) {
        var headers = s.getRange(1, 1, 1, extraColumns).getValues()[0];
    } else var headers = s.getRange(1, 1, 1, s.getLastColumn()).getValues()[0];

    var message = "";
    for (var i in headers) {
        //First case:
        // message += headers[i] + ' = '+ s.namedValues[headers[i]].toString() + "\n\n";   
        //var i is "Timestamp"  
        //TypeError: Cannot read property "Timestamp" from undefined.
        //Second case:
        message += headers[i] + ' = ' + s.values[i].toString() + '\n\n'; 
        //TypeError: Cannot read property "0" from undefined.

    }
    MailApp.sendEmail(notify, Subject, message);
}

I have the following two errors in first and second types: 1)TypeError: Cannot read property "Timestamp" from undefined 2)TypeError: Cannot read property "0" from undefined. What is my error?

1

1 Answers

0
votes

That's because you are trying to access a non existing properties, and it seems that Sheet object doesn't contain namedValues or values properties, if you want to add all cells to message variable, try this:

var s = SpreadsheetApp.getActive().getSheetByName("Form Responses 1");

if (extraColumns) {
    var values = s.getRange(1, 1, s.getLastRow(), extraColumns).getValues();
} else {
    var values = s.getRange(1, 1, s.getLastRow(), s.getLastColumn()).getValues();
}

var message = "";
for (var i=1; i < values.length; i++) { 

    for (var j=0; j < values[i].length; j++) {
        message += values[0][j] + ' = ' + values[i][j] + '\n\n'; 
    }

}

Edit add only the last row to message variable:

var s = SpreadsheetApp.getActive().getSheetByName("Form Responses 1");

if (extraColumns) {
    var values = s.getRange(1, 1, s.getLastRow(), extraColumns).getValues();
} else {
    var values = s.getRange(1, 1, s.getLastRow(), s.getLastColumn()).getValues();
}

var message = "";
var lastRow = values.length-1;

for (var j=0; j < values[lastRow].length; j++) {
    message += values[0][j] + ' = ' + values[lastRow][j] + '\n\n'; 
}

Hope this helps