Compare timestamp in two different formats - GAS

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 from this section -

// Record approval or rejection in spreadsheet
  var row = ArrayLib.indexOf(data, 0, timestamp);
  if (row < 0) throw new Error ("Request not available.");  // Throw error if request was not found
  sheet.getRange(row+1, approvalCol).setValue(e.parameter.approval);

I get the "Request not available" error because the ArrayLib.indexOf object is comparing the time stamp that is being presented from the same source but via two different 'routes'.

The timestamp from the 'timestamp' variable looks like this - "17/03/2015 18:00:11"

...and the timestamp contained in the 'data' variable (that should match the timestamp variable) looks like this - "Tue Mar 17 2015 00:30:10 GMT-0700 (PDT)".

I am assuming that the two different formats is what is resulting in the ArrayLib.indexOf object returning a '-1' result and hence the error message.

Any thoughts on what I need to do to get the matching working successfully ?

标签： javascript datetime google-apps-script google-form

2条回答

地球回转人心会变

2楼-- · 2019-08-30 09:55

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);

0人赞添加讨论(0) 举报

Lonely孤独者°

3楼-- · 2019-08-30 09:58

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});  
}

0人赞添加讨论(0) 举报

Compare timestamp in two different formats - GAS

采纳回答

编辑标签

举报内容

检举类型

检举原因

检举说明(必填)

打开微信“扫一扫”，打开网页后点击屏幕右上角分享按钮

付费偷看金额在0.1-10元之间