Using a Google Apps Form to submit a ticket in RT

Introduction

Sometimes you want to 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.

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.

It is wise to format the columns as "raw text", otherwise you might get surprises when Google tries to transform the input into dates or other fancy formats.

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

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.

Templates

Autoreply

We do not want the standard global Autoreply template to be used when a ticket is created. Recreate this template in the Queue and make it empty.

Notify Potential Owners

When a ticket is created, we want to let the team know. In this example, it's a group of sysadmins.

RT-Attach-Message: yes
Bcc: {
$RT::Logger->debug("Inside the On Create Notify Potential Template");

# We stop unless the ticket is owned by Nobody
return 0 unless $Ticket->Owner == $RT::Nobody->id;

my $GroupName = 'Sysadmin'; CHANGEME!!!
 
# instantiate a group object
my $addGroupObj = RT::Group->new($RT::SystemUser);
$addGroupObj->LoadUserDefinedGroup($GroupName);
return undef unless $addGroupObj;
my $addGroupMembersObj = $addGroupObj->UserMembersObj;
 
my $res = ;
# walk through members of group
while ( my $userObj = $addGroupMembersObj->Next) {
   my $email = $userObj->EmailAddress;
   next unless $email; # email can be empty
 
   $res .= ', ' if $res;
   $res .= $email;
}
   $res;
}


A request was filled out via a GApps form. You should take a look at it.

Ticket #: {$Ticket->id} 
Queue : {$Ticket->QueueObj->Name}
Requestor: {$Ticket->RequestorAddresses}

{
my $CFs = $Ticket->QueueObj->TicketCustomFields();
my $cf = RT::CustomField->new( $RT::SystemUser );
my $OUT = "";
while ($cf = $CFs->Next) {
    my $val = undef;
    $OUT .= $cf->Name . ":\n";
    $cf->SingleValue ? $val = $Ticket->FirstCustomFieldValue($cf->id) : $val = $Ticket->CustomFieldValuesAsString($cf->id);
    if ( (defined $val) and ($val !~ /^\s*$/) ) {
       $OUT .= $val;
    } else {
       $OUT .= "<n/a>";
    }
    $OUT .= "\n" . "=" x 60 . "\n";
}
$OUT;
}

Notify Requestor

This is the template we used to inform the real requestor (see scrip later) that the form was filled out properly and we have received all the info

To: {$Ticket-> RequestorAddresses}
Subject: {$Ticket->Subject}

Hello,

We thank you for filling out the form. All the elements have been properly received. A ticket has been created in our internal system with the number {$Ticket->id}.

If you would like to add some information such as attaching files, please reply to this email. For your information, you submission is shown below.

{
my $CFs = $Ticket->QueueObj->TicketCustomFields();
my $cf = RT::CustomField->new( $RT::SystemUser );
my $OUT = "";
while ($cf = $CFs->Next) {
    my $val = undef;
    next if ($cf->Name =~ /GApps Daily Mail Quota/);
    $OUT .= $cf->Name . ":\n";
    $cf->SingleValue ? $val = $Ticket->FirstCustomFieldValue($cf->id) : $val = $Ticket->CustomFieldValuesAsString($cf->id);
    if ( (defined $val) and ($val !~ /^\s*$/) ) {
       $OUT .= $val;
    } else {
       $OUT .= "<n/a>";
    }
    $OUT .= "\n" . "=" x 60 . "\n";
}
$OUT;
}

Custom Condition and Custom Action

Before we get to the meat of the scrips, you must know about a custom condition called On Create From Email and a custom action called Populate Custom Fields Manually From Email.

On Create From Email is a simple modification of the regular "On Create" condition. I just added a few checks to make sure the process was done over email. Since we use this condition for all the scrips below, it is easier to have it in a module in case you want to make some changes to it. Here is the code, please save it under $RTHOME/local/lib/RT/Condition/CreatedFromEmail.pm

package RT::Condition::CreatedFromEmail;
use base 'RT::Condition';
use strict;

=head2 IsApplicable

Returns true if the transaction is Create and the ticket was created from an incoming email

=cut

sub IsApplicable {
	my $self = shift;
	my $DebugDescription = $self->ScripObj->Description;
	my $Transaction = $self->TransactionObj;
	my $Ticket = $self->TicketObj;

# We need to be in a creation
	return 0 unless $Transaction->Type eq "Create";

# That creation must happen by mail
	my $MsgAttr = $Transaction->Message->First;
	return 0 unless $MsgAttr;
	return 1 if defined $MsgAttr->GetHeader('Received');
	return 0;
}

RT::Base->_ImportOverlays();

1;

The second part of the work is the custom action Populate Custom Fields Manually From Email. In short, I first tried to have all the work done by the great RT-Extension-CommandByMail. There is one problem: Google will insert breaklines in the email it sends to create the ticket and that will stop the processing by the CommandByMail extension. Therefore, I re-invented a much less powerful wheel, but one that does the job exactly for what we need here. Since we must use subroutines in this condition, we have to use it as a module. Please save the code below under $RTHOME/local/lib/RT/Action/PopulateCustomFieldsManuallyFromEmail.pm

package RT::Action::PopulateCustomFieldsManuallyFromEmail;
use base 'RT::Action';

use strict;

=head2 Prepare

Unused, returns 1 everytime

=cut

sub Prepare {
	return 1;
}

=head2 Commit

This is the meat of the module.

Takes the content of the creation text (aka Body of the email) and looks at each line
We insert the values into the respective CustomFields while dealing with values spread 
over multiple lines

=cut

sub Commit {
    my $self = shift;
	my $DebugDescription = $self->ScripObj->Description;
	
	my $lastLine = undef;	
	foreach my $line (split (/\n/, $self->TicketObj->Transactions->First->Content)) {
    	chomp $line;
    	if ($self->_getCF($line, "check")) {
			$self->_processCF($lastLine) if defined $lastLine;
			$lastLine = $line;
    	} else {
			$lastLine .= " " if $lastLine =~ /\.$/;
			$lastLine .= $line;
    	}
	}
	$self->_processCF($lastLine);
	return 1;
}

sub _processCF {
	my $self = shift;
	my $line = shift;
	my $DebugDescription = $self->ScripObj->Description;
	
	unless ( (defined $line) and ( $self->_getCF($line, "check"))) {
		$RT::Logger->error($DebugDescription . " - Trying to process a Custom Field on a wrong value: $line"); 
		return 0;
	}
	
	my $cfName = $self->_getCF($line, "name");
	return 0 unless $cfName;
	my $cf = RT::CustomField->new($RT::SystemUser);
	$cf->LoadByName( Name => $cfName );

	my $cfValue = $self->_getCF($line, "value");
	return 0 unless $cfValue;

# Process single value and multiple choice CF differently
	if ( $cf->SingleValue ) {
		$cf->AddValueForObject( Object  => $self->TicketObj, Content => $cfValue);
		$RT::Logger->debug( $DebugDescription . " - added value $cfValue to CF $cfName" );
	} else {
		return 0 unless $cfValue =~ /, /;
		foreach my $e (split( /, /, $cfValue)) {
			$cf->AddValueForObject( Object  => $self->TicketObj, Content => $e);
			$RT::Logger->debug( $DebugDescription . " - added value $e to CF $cfName" );
		}
	}

	return 1;
}

#
# $self->_getCF($line, $arg)
#
# Extracts the name or value of a line based on the $arg
# Example of a line:
#
#	CF-FROMGAPPS.{Change in Hardware}.CF-FROMGAPPS: No
#
# Returns undef if not applicable or empty
#

sub _getCF {
	my $self = shift;
	my $line = shift;
	my $arg = shift;
	
	return undef unless defined $line;
	return undef unless $line =~ /^CF-FROMGAPPS\.\{(.+)\}\.CF-FROMGAPPS:\s*(.*)$/;
	if ($arg eq "value") {
		return undef if $2 =~ /^\s*$/;
		return $2;
	} elsif ($arg eq "name") {
		return undef if $1 =~ /^\s*$/;
		return $1
	} elsif ($arg eq "check") {
		return 1;
	}
	return undef;
}

RT::Base->_ImportOverlays();

return 1;

Now we must import these modules into the database. First, create a file named /tmp/import.txt with the following content:

@ScripActions = (
  {
    Name        => 'Populate Custom Fields Manually From Email',
    Description => 'When receiving a form from GApps, this will take care of what CommandByMail cannot do' ,
    ExecModule => 'PopulateCustomFieldsManuallyFromEmail',
  },
);

@ScripConditions = (
    { Name                 => 'On Create From Email',
      Description          => 'When a ticket is created by an incoming email',
      ApplicableTransTypes => 'Create',
      ExecModule           => 'CreatedFromEmail', }
);

Next, run these commands as root

# $RTHOME/sbin/rt-setup-database --action insert --datafile /tmp/import.txt
# apachectl restart

Scrips

The logic is as follows:

Get all the data from the email into the appropriate custom fields (aka CF)
Set the Ticket's subject to something relevant based on the information from some CF
Do the same as 2. but this time, with the requestor instead of the subject
Notify the potential owners
Notify the requestor

All scrips use the new On Create From Email condition.

The first three scrips use the Global Blank template. The last two use the appropriate template created earlier.

The order in which the scrips are run is crucial. Each scrip's description starts with a number to ensure that they are run in the proper order. The first three scrips must be run on the TransactionCreate stage. The last two (and any further scrip using templates) must be run on the TransactionBatch stage. For more information on these stages, read TransactionBatchStage and ScripExecOrder.

Here is a table showing the details:

Description	Stage	Condition	Action	Template
05 On Create Populate Custom Fields Manually From Email	TransactionCreate	On Create From Email	PopulateCustomFieldsManuallyFromEmail	Blank
10 On Create Setup Subject	TransactionCreate	On Create From Email	User Defined	Blank
11 On Create Set Requestor As Indiquated in CF	TransactionCreate	On Create From Email	User Defined	Blank
50 On Create Notify Potential Owners	TransactionBatch	On Create From Email	Notify Other Recipients	Notify Potential Owners
90 On Create By Email Notify Real Requestors	TransactionBatch	On Create From Email	Notify Other Recipients	Notify Requestor

There are two scrip who have User Defined as their Action. Here is the code for the first one, 10 On Create Setup Subject. Note that this is really shown here as an example since you probably won't have the same names for CF as I do.

Custom Action Preparation Code: 1;

Custom Action Cleanup Code:

#
# Sets the subject based on a few CFs
#
my $DebugDescription = $self->ScripObj->Description;
my $Ticket = $self->TicketObj;
my $softwareName = $Ticket->FirstCustomFieldValue("Software Name");
my $softwareVersion = $Ticket->FirstCustomFieldValue("Version");

# We bail in the very unlikely case either of those CF are empty or undefined
if ( !(defined $softwareName) or ($softwareName =~ /^\s*$/) or !(defined $softwareVersion) or ($softwareVersion =~ /^\s*$/) ) { 
    $RT::Logger->error($DebugDescription . " - Required value is empty");
    return 0; 
}

my $subject = "$softwareName - $softwareVersion";
my ($status, $msg) = $Ticket->SetSubject($subject);
unless ($status) {
    $RT::Logger->error("Unable to set new subject: $subject");
    return 0;
}
$RT::Logger->debug( $DebugDescription . " - Subject changed to $subject");

return 1;;

and the Custom Action for the scrip 11 On Create Set Requestor As Indiquated In CF

Custom Action Preparation Code: 1;

Custom Action Cleanup Code:

#
# When a ticket is created by our GApps form, the user who submitted  
# the form has his email address in 
#     CF.{email address}:
# This scrip will make him the requestor on the Ticket
#
my $DebugDescription = $self->ScripObj->Description;
my $cfName = "email address";
my $Ticket = $self->TicketObj;

# We bail in the very unlikely case there is already more than one requestor
$RT::Logger->debug($DebugDescription . " - Ticket Requestor Addresses: " . $Ticket->RequestorAddresses);
return 0 if $Ticket->RequestorAddresses =~ /, /;

my $value = $Ticket->FirstCustomFieldValue($cfName);
return 1 unless defined $value;
chomp $value;
$RT::Logger->debug( $DebugDescription . " - we have a value: $value");
$RT::Logger->debug( $DebugDescription . " - Current Requestor : ".  $Ticket->RequestorAddresses);

$Ticket->DeleteWatcher(Type => 'Requestor', Email => $Ticket->RequestorAddresses);
$Ticket->AddWatcher(Type => 'Requestor', Email =>$value);
$RT::Logger->debug( $DebugDescription . " - Requestor changed to $value");

return 1;

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.

That's all folks!

Using a Google Apps Form to submit a ticket in RT

Contents

Introduction

Create a form in Google Apps

Add a script to send the newly inputed data by email

Set up the triggers

Test

Set up RT

Creating a Queue

Custom Fields

Rights

Templates

Autoreply

Notify Potential Owners

Notify Requestor

Custom Condition and Custom Action

Scrips

Cloning a queue

Navigation menu

Using a Google Apps Form to submit a ticket in RT

Introduction

Create a form in Google Apps

Add a script to send the newly inputed data by email

Set up the triggers

Test

Set up RT

Creating a Queue

Custom Fields

Rights

Templates

Autoreply

Notify Potential Owners

Notify Requestor

Custom Condition and Custom Action

Scrips

Cloning a queue

Navigation menu

Search