Skip to content Skip to sidebar Skip to footer
Home / Datetime / Google Apps Script / Google Forms / Javascript

Compare Timestamp In Two Different Formats - GAS

Post a Comment
I am using the enhanced workflow script that was posted by Mogsdad here. I have managed to work out a few issues but one that I am stuck on at the moment is the error that comes up

Solution 1:

Create a new Date object for the timestamp value, so that you can ensure they can be compared. The code should look like:

  var dateFromTimestamp = new Date(timestamp);

Solution 2:

After looking around at a few other posts I came up with a solution that seems to work pretty well and overcomes the issues with using the timestamp.

I put an array formula in the first column of the response sheet that created a ticket number - =ArrayFormula(if(B2:B,"AFR"&text(row(A2:A)-1,"00000"),iferror(1/0)))

Then I retrieved the ticket number (var cellVal) and sent it with the email. The response email brings the approval value to the correct line every time....so far.

function sendEmail(e) {
  // Response columns: Timestamp    Requester Email Item    Cost
  var email = e.namedValues["Requester Email"];
  var item = e.namedValues["Item"];
  var cost = e.namedValues["Cost"];
  //var timestamp = e.namedValues["Timestamp"];
  var row = e.range.getRow();
  var seq = e.values[1];
  var url = ScriptApp.getService().getUrl();
  var sheet = SpreadsheetApp.openById('1pFL0CEW5foe8nAtk0ZwwTleYrBn2YulMu_eKPDEFQaw').getSheetByName("Form Responses 1");
  var range = sheet.getDataRange();
  var cellval = range.getCell(row,1).getValue();
  //var origMail = range.getCell(row,3).getValue();
  Logger.log(cellval);
  //Logger.log(origMail);

  var options = '?approval=%APPROVE%&reply=%EMAIL%'
           .replace("%EMAIL%",e.namedValues["Requester Email"])         
  var approve = url+options.replace("%APPROVE%","Approved")+'&row='+row+'&cellval='+cellval; 
  var reject = url+options.replace("%APPROVE%","Rejected")+'&row='+row+'&cellval='+cellval;

  var html = "<body>"+
                "<h2>Please review</h2><br />"+
                "Request from: " + email + "<br />"+
                "Ticket No: " + cellval + "<br />"+
                "For: "+item +", at a cost of: $" + cost + "<br /><br />"+ 
                "<a href="+ approve +">Approve</a><br />"+
                "<a href="+ reject +">Reject</a><br />"+
             "</body>";

  MailApp.sendEmail(Session.getEffectiveUser().getEmail(),
                    "Approval Request", 
                    "Requires html",
                    {htmlBody: html});  
}

Post a Comment for "Compare Timestamp In Two Different Formats - GAS"