Using a Google Apps Form to submit a ticket in RT

From Request Tracker Wiki
Revision as of 13:33, 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 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


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)"; // CHANGE ME !!!
  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 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");

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.


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.


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.



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