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