Untitled
function myFunction() { var spreadsheet = SpreadsheetApp.getActive(); var responseSheet = spreadsheet.getSheetByName('Form Responses 1'); // Returns the position of the last row that has content. var rLastRow = responseSheet.getLastRow(); // Returns the position of the last column that has content. var lastCol = responseSheet.getLastColumn(); var values = responseSheet.getRange(rLastRow, 1, 1, lastCol).getValues()[0]; /// NEW FORM //// var ticketId = values[0]; //var timestamp = values[1]; var timestamp = Utilities.formatDate(new Date(values[1]), 'GMT+2', 'dd/MM/yyyy HH:mm a'); var requestDescription = values[3]; var requestPhoto = values[4]; var staffDept = values[5]; var staffEmail = values[2]; emailIT(ticketId, timestamp, requestDescription,requestPhoto, staffDept, staffEmail); emailStaff(ticketId, timestamp, requestDescription, requestPhoto,staffDept, staffEmail); } function emailIT(ticketId, timestamp, requestDescription, requestPhoto, staffDept, staffEmail) { var subject = '[Ticket ID: ' + ticketId + '] ' + requestDescription; // Email Text. You can add HTML code here - see ctrlq.org/html-mail var htmlBody = 'Dear IT staff,'; htmlBody += '<p>This is to notify that there is a new service request from IT portal.</p>'; htmlBody += '<br><strong>Request Date:</strong> ' + timestamp; htmlBody += '<br><strong>Description:</strong> ' + requestDescription; htmlBody += '<br><strong>Photo/Screenshot:</strong>' + requestPhoto; htmlBody += '<br><strong>Staff Department:</strong> ' + staffDept; htmlBody += '<br><strong>Staff Email:</strong> ' + staffEmail + '</p>'; htmlBody += '<p>Thank you.</p>'; htmlBody += '<p>Regards,<br>IT Team</p>'; GmailApp.sendEmail('mejlo tuka', subject, '', {htmlBody:htmlBody, name: 'IT Service Portal', replyTo: staffEmail}); } function emailStaff(ticketId, timestamp, requestDescription, requestPhoto, staffDept, staffEmail) { var subject = '[Ticket ID: ' + ticketId + '] ' + requestDescription; // Email Text. You can add HTML code here - see ctrlq.org/html-mail var htmlBody = 'Dear ' + staffEmail + ','; htmlBody += '<p>Thank you for contacting IT team.'; htmlBody += '<br>A support ticket has now been opened.'; htmlBody += '<br>You will be notified when a response is made by email.'; htmlBody += '<br>The details of your ticket are shown below.</p>'; htmlBody += '<br><strong>Request Date:</strong> ' + timestamp; htmlBody += '<br><strong>Description:</strong> ' + requestDescription; htmlBody += '<br><strong>Photo/Screenshot:</strong>' + requestPhoto; htmlBody += '<br><strong>Staff Department:</strong> ' + staffDept; htmlBody += '<br><strong>Staff Email:</strong> ' + staffEmail + '</p>'; htmlBody += '<p>Thank you.</p>'; htmlBody += '<p>Regards,<br>IT Team</p>'; GmailApp.sendEmail(staffEmail, subject, '', {htmlBody:htmlBody, name: 'IT Team', replyTo: 'mejlo tuka'}); }
Leave a Comment