Untitled

 avatar
unknown
javascript
a year ago
3.0 kB
3
Indexable
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