Difference between revisions of "Using a Google Apps Form to submit a ticket in RT"

From Request Tracker Wiki
Jump to navigation Jump to search
Line 1: Line 1:
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 idea:
#Create a form in Google Apps.
#Add a script to send the newly inputed data by email (and the triggers that go with it)
#Configure rt-mailgate to have the form create a new ticket in a particular queue
#Take each field from the filled form and transpose them into CustomFields
#Send an email to the support team letting them know a form was submitted
#Send an email to the person who filled out the form, letting him know we received the info
It is not an difficult task but it does take a bit of time, as you must get everything right and the devil is in the details. I suggest you read the whole thing once before proceeding.


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:
Haïm Dimermanas
#Create a form in Google Apps
#Add a script to send the newly inputed data by email
#Configure rt-mailgate to have this email create a new ticket in a queue
#Create the ticket and fill in the data in the appropriate Custom Fields
#Send an email to the support team
#Send an email to the requestor


==Create a form in Google Apps==
==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".
I assume you know how to create a form with Google Documents. Keep in mind that the data is stored in a spreadsheet. That Google Document needs to have 2 sheets: "Answers" and "EmailSent". Answers is where we keep the list of what's been filled in. EmailSent is simply a sheet we use to keep track of the fact that, well, the data was sent over email. It's primitive logging.
==Add a script to send the newly inputed data by email==
==Add a script to send the newly inputed data by email==
The concept is simple: once someone fills out your form, you want to get the content over email in a format RT will understand. For this to happen we need two items:
#A program written in JavaScript that will take every field in the form and write its name and content to an email
#A trigger letting Google Apps know that whenever someone fills out the form, the program described above needs to be launched.
From the spreadsheet, go to Tools - Script Editor
From the spreadsheet, go to Tools - Script Editor


Line 37: Line 42:
   var FormName = SpreadsheetApp.getActiveSpreadsheet().getName();
   var FormName = SpreadsheetApp.getActiveSpreadsheet().getName();
   
   
   var To = "your@address.rt";
   var To = "your@address.rt"; //CHANGE ME!!!!
   var maintainer = "thatIsYourRealEmailAdress(I mean you reading this)@foo.com";
   var maintainer = "thatIsYourRealEmailAdress(I mean you reading this)@foo.com"; // CHANGE ME !!!
   var Subject = "Form " + FormName;
   var Subject = "Form " + FormName;
   
   
Line 65: Line 70:
   var data = ss.getRange(ss.getLastRow(), 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
   // We start at 1 since we don't care about the GOOGLE TIMESTAMP
   for (var i = 1; i < ss.getLastColumn(); ++i) {
   for (var i = 1; i < ss.getLastColumn(); ++i) {
     var labelName = removeAccents(labels[0][i]);
     var labelName = labels[0][i];
     var dataCell = data[0][i];
     var dataCell = data[0][i];
     Content = Content + "CF-FROMGAPPS.{" + labelName + "}.CF-FROMGAPPS: " + dataCell + "\n";
     Content = Content + "CF-FROMGAPPS.{" + labelName + "}.CF-FROMGAPPS: " + dataCell + "\n";
Line 73: Line 78:
   Content = Content + "CF-FROMGAPPS.{GApps Daily Mail Quota}.CF-FROMGAPPS: " + MailApp.getRemainingDailyQuota() + "\n";
   Content = Content + "CF-FROMGAPPS.{GApps Daily Mail Quota}.CF-FROMGAPPS: " + MailApp.getRemainingDailyQuota() + "\n";
   return Content;
   return Content;
}
// There is a bug in RT-CommandByMail as of April 23 2012
// https://rt.cpan.org/Public/Bug/Display.html?id=63702
// 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;
  }
  }
   
   
Line 101: Line 87:
  }
  }
===Set up the triggers===
===Set up the triggers===
From the script editor, go to Ressources - Triggers
From the script editor, go to Ressources - Triggers. It should read:
 
'''OnFormSubmit - From spreadsheet - On form submit'''
 
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 so.
 
The GApss Daily Mail Quota is the number of emails you can send today. It starts at 1,500 and it looks like it gets back to that number every 24 hours.
===Test===
At this point, you should be able to dry run the script and the form to make sure it correctly sends the content of what's been submitted (or if you run it from the script editor, it will send the content of the last row in the Sheet "Answers"). It's a good way to test.
==Set up RT==
===Creating a Queue===
Since all forms are different, mainly because their fields are different, I prefer to associate each form with a Queue. The CorrespondAddress should be set in /etc/aliases. This process is explained in [[ManualEmailConfig]].
===Custom Fields===
The idea behind this whole exercise is to have the value of each field in the form be inputed into a Custom Field (or CF for short) in the newly created ticket. To do so, we need to have one CF for one field on the form. For example, if you have a field "First Name" in your form, you should have a CF "First Name" applied to the newly created Queue. It helps of course if the Custom Field type matches the type of input you will fill in the form (text, blob, multiple choice question, etc). Please note that CF names and form field names are case sensitive, you will want to respect that when creating your CFs
 
The order in which your CFs appear on the ticket is also important. When it's time to send an email with the content of the ticket and its CFs, the CF will appear in the order you chose. You can move them up and down from the Queue page listing Tickets CF.
===Rights===
The email will arrive into the Queue as coming from the person who '''created''' the form, not the person who filled it in. That means if you took the time to create the form in GApps, setup the JavaScript program, etc, the email will be sent as '''YOU'''. You must make sure YOU have the rights on the Queue to Create a Ticket, See Custom Fields and See the Queue.
===Scrips===


OnFormSubmit - From spreadsheet - On form submit
===Templates===


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.
===Cloning a queue===
The whole process of setting up the Queue can be time consuming (espcially the part about the Custom Fields). If you want to create a new Queue to accept tickets for a new form, I recommend the excellent [[Rt-clonequeue]] command line tool.


Not done yet, I'll get to finishing it
Not done yet, I'll get to finishing it

Revision as of 13:33, 9 May 2012

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 idea:

  1. Create a form in Google Apps.
  2. Add a script to send the newly inputed data by email (and the triggers that go with it)
  3. Configure rt-mailgate to have the form create a new ticket in a particular queue
  4. Take each field from the filled form and transpose them into CustomFields
  5. Send an email to the support team letting them know a form was submitted
  6. Send an email to the person who filled out the form, letting him know we received the info

It is not an difficult task but it does take a bit of time, as you must get everything right and the devil is in the details. I suggest you read the whole thing once before proceeding.

Haïm Dimermanas

Create a form in Google Apps

I assume you know how to create a form with Google Documents. Keep in mind that the data is stored in a spreadsheet. That Google Document needs to have 2 sheets: "Answers" and "EmailSent". Answers is where we keep the list of what's been filled in. EmailSent is simply a sheet we use to keep track of the fact that, well, the data was sent over email. It's primitive logging.

Add a script to send the newly inputed data by email

The concept is simple: once someone fills out your form, you want to get the content over email in a format RT will understand. For this to happen we need two items:

  1. A program written in JavaScript that will take every field in the form and write its name and content to an email
  2. A trigger letting Google Apps know that whenever someone fills out the form, the program described above needs to be launched.

From the spreadsheet, go to Tools - Script Editor

CAUTION

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"; //CHANGE ME!!!!
  var maintainer = "thatIsYourRealEmailAdress(I mean you reading this)@foo.com"; // CHANGE ME !!!
  var Subject = "Form " + FormName;

  if (remainingDailyQuota > 2) {
    MailApp.sendEmail(To,
                      Subject, 
                      Content);
    return 1;
  } else {
    MailApp.sendEmail(maintainer, 
                      "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 GOOGLE TIMESTAMP
  for (var i = 1; i < ss.getLastColumn(); ++i) {
    var labelName = 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;
}

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

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

Set up the triggers

From the script editor, go to Ressources - Triggers. It should read:

OnFormSubmit - From spreadsheet - On form submit

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 so.

The GApss Daily Mail Quota is the number of emails you can send today. It starts at 1,500 and it looks like it gets back to that number every 24 hours.

Test

At this point, you should be able to dry run the script and the form to make sure it correctly sends the content of what's been submitted (or if you run it from the script editor, it will send the content of the last row in the Sheet "Answers"). It's a good way to test.

Set up RT

Creating a Queue

Since all forms are different, mainly because their fields are different, I prefer to associate each form with a Queue. The CorrespondAddress should be set in /etc/aliases. This process is explained in ManualEmailConfig.

Custom Fields

The idea behind this whole exercise is to have the value of each field in the form be inputed into a Custom Field (or CF for short) in the newly created ticket. To do so, we need to have one CF for one field on the form. For example, if you have a field "First Name" in your form, you should have a CF "First Name" applied to the newly created Queue. It helps of course if the Custom Field type matches the type of input you will fill in the form (text, blob, multiple choice question, etc). Please note that CF names and form field names are case sensitive, you will want to respect that when creating your CFs

The order in which your CFs appear on the ticket is also important. When it's time to send an email with the content of the ticket and its CFs, the CF will appear in the order you chose. You can move them up and down from the Queue page listing Tickets CF.

Rights

The email will arrive into the Queue as coming from the person who created the form, not the person who filled it in. That means if you took the time to create the form in GApps, setup the JavaScript program, etc, the email will be sent as YOU. You must make sure YOU have the rights on the Queue to Create a Ticket, See Custom Fields and See the Queue.

Scrips

Templates

Cloning a queue

The whole process of setting up the Queue can be time consuming (espcially the part about the Custom Fields). If you want to create a new Queue to accept tickets for a new form, I recommend the excellent Rt-clonequeue command line tool.

Not done yet, I'll get to finishing it