Using a Google Apps Form to submit a ticket in RT

From Request Tracker Wiki
Revision as of 07:48, 9 May 2012 by (talk)
Jump to navigation Jump to search

Sometimes you wish you could have people fill in a form and have all the data appear in a ticket. After a few weekends toying with the idea, I believe I have something worthy of this wiki. We use this system in production for tasks ranging from asking for new hardware to having the vendors notify us when a new software version is available. Here is the general principal:

  1. Create a form in Google Apps
  2. Add a script to send the newly inputed data by email
  3. Configure rt-mailgate to have this email create a new ticket in a queue
  4. Create the ticket and fill in the data in the appropriate Custom Fields
  5. Send an email to the support team
  6. Send an email to the requestor

Create a form in Google Apps

I will assume you know how to create a form with Google Documents. Keep in mind that the data is stored in spreadsheet. That document needs to have 2 sheets: "Answers" and "EmailSent".

Add a script to send the newly inputed data by email

From the spreadsheet, go to Tools - Script Editor


As of the date of this writing (05/09/2012), the MailApp can send emails if you have the paid version of Google Apps. Be sure to check often and update this wiki if the policy has changed.

Here is the script:

// We get the content of the form
// We attempt to send it by mail
// If we succeed, we note it in the sheet "EmailSent"

function onFormSubmit() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  if (sendEmailMessage(getContent(ss.getSheetByName("Answers")))) {
    validateEmailAsSent(ss.getSheetByName("EmailSent"), ss.getSheetByName("Answers").getLastRow());

// Emails Content
// Returns 1 on success

function sendEmailMessage(Content) {
  var remainingDailyQuota = MailApp.getRemainingDailyQuota();
  var FormName = SpreadsheetApp.getActiveSpreadsheet().getName();

  var To = "your@address.rt";
  var maintainer = "thatIsYourRealEmailAdress(I mean you reading this)";
  var Subject = "Form " + FormName;

  if (remainingDailyQuota > 2) {
    return 1;
  } else {
                      "IMPORTANT: Can't send " + FormName, 
                      "Help, MailApp.getRemainingDailyQuota() <= 2, which means that soon I won't be able to send " + FormName + " to anyone!");
    return 0;
  // We should never reach this point
  return 0;

// Gets the content of the form that was just submitted
// Returns the data in this format:

function getContent(ss){  
  var Content = "";
  var labels = ss.getRange(1, 1, 1, ss.getLastColumn()).getValues();
  var data = ss.getRange(ss.getLastRow(), 1, 1, ss.getLastColumn()).getValues();
  // We start at 1 since we don't care about the HORODATEUR
  for (var i = 1; i < ss.getLastColumn(); ++i) {
    var labelName = removeAccents(labels[0][i]);
    var dataCell = data[0][i];
    Content = Content + "CF-FROMGAPPS.{" + labelName + "}.CF-FROMGAPPS: " + dataCell + "\n";
  Content = Content + "CF-FROMGAPPS.{GApps Daily Mail Quota}.CF-FROMGAPPS: " + MailApp.getRemainingDailyQuota() + "\n";
  return Content;

// There is a bug in RT-CommandByMail as of April 23 2012
// Therefore we must remove all accents

function removeAccents(s) {
  var r = s;
  r = r.replace(new RegExp(/[àáâãäå]/g),"a");
  r = r.replace(new RegExp(/æ/g),"ae");
  r = r.replace(new RegExp(/ç/g),"c");
  r = r.replace(new RegExp(/[èéêë]/g),"e");
  r = r.replace(new RegExp(/[ìíîï]/g),"i");
  r = r.replace(new RegExp(/ñ/g),"n");                
  r = r.replace(new RegExp(/[òóôõö]/g),"o");
  r = r.replace(new RegExp(/œ/g),"oe");
  r = r.replace(new RegExp(/[ùúûü]/g),"u");
  r = r.replace(new RegExp(/[ýÿ]/g),"y");
  return r;

// Records that the email was sent in a seperate sheet

function validateEmailAsSent(ss, row) {
  ss.getRange(row, 1).setValue("EMAIL_SENT");

Set up the triggers

From the script editor, go to Ressources - Triggers

OnFormSubmit - From spreadsheet - On form submit

And be sure to Authorize the script to send emails. After that you can even setup notifications in case the script fails to run. I recommend you do.

Not done yet, I'll get to finishing it