google-apps-scriptnangoogle-sheets-apigoogle-formsgoogle-apps-for-education

Why do e.values[] on formSubmitReply in Google Apps script has NaN?


I am trying to make a simple tool which stores ticket number and some details that are handovered to other team members. In that process i used a Form to submit a handover and store in Google Spreasheet. But i also want to notify the team members, so i wrote a script as below.

function formSubmitReply(e) 
{

var timeStamp = e.values[0];
var sendTo = e.values[1];
var ticket = e.values[2];
var description = e.values[3];
MailApp.sendEmail(sendTo,
                  "New Handover",
                  + ticket +" handovered to you at "+ timeStamp +"\n\nDescription:"+ description +
                  "\n\nKindly check it using the below link\n\nhttp://goo.gl/p16qdN",
                   {name:"Handover Mail"});

}

In the above code e.values[2] has the ticket number through form and in forms i have used a textbox with a regex validation [c.i][r,n][q,c] which means the first three words should be C/I,R/N/Q/C. for eg CRQ, IRC, INC..etc. Everything was working alright but suddenly one day NaN starting coming in the notification mail(below output). Details in Spreadsheet is fine.

NaN handovered to you at 12/4/2014 12:48:23

Description:Device Check after 4 hours

Kindly check it using the below link

http://goo.gl/p16qdN

The above is my primary issue that i want to solve. But also as a add on i need some advice or guide on how to set some value on a cell in spreadsheet which has a fixed column and variable row when a handover is submitted. I am referring http://goo.gl/wkD0Y7 for my work. But in that sheet.getRange(lastRow, getColIndexByName("Status")).setValue("New"); does not seem to work throwing some reference error.


Solution

  • Your error may have to do with the preceding "+" sign in the email body string and the GAS compiler interpreting the string as a number. Try this code instead:

    function formSubmitReply(e){
       var timeStamp = e.values[0];
       var sendTo = e.values[1];
       var ticket = e.values[2];
       var description = e.values[3];
    
       MailApp.sendEmail(
          sendTo,
          "New Handover",
             ticket + " handovered to you at " + 
             timeStamp + "\n\nDescription:" + 
             description + "\n\nKindly check it using the below link\n\nhttp://goo.gl/p16qdN",
           {name:"Handover Mail"}
       );
    }
    

    As for your other question, there's no built-in getColIndexByName() method, so you'll either need to copy over the function from the tutorial you referenced or just set it manually like this:

    var statusCol = 1;
    sheet.getRange(sheet.getLastRow() + 1, statusCol).setValue("New");