Create a Ticket in Zammad Via Google Form using Apps Script
[UPDATE 26 Jan 2023 — Our workgroup refactored the script in order to include an error handling layer (in addition to the tools integrated in appscript). We’ve posted the new code here: https://github.com/Universita-di-Ferrara/GoogleForm-to-Zammad/blob/main/gFormToZammad.js]
In this tutorial i’m documenting the procedure to create a ticket in Zammad, a ruby-based open source and free to use, ticket management system, using Google Forms as a user interface.
Why?
Well, while a do love Zammad i’ve felt constricted by the very simple options available in the Channel > Form available in Zammad.
Since i’m not sure if they’re planning on implement more functions in it or they’ll focus on the ticket mask creation (as it seems judging at the new super cool “Core Workflow” feature) i’ve decided to fiddle around with Apps Script in order to learn some Javascript.
About this tutorial
I’ll use sometimes generic terms for some Google Forms or Apps Script features since I’ve noticed fast changes in the Apps Script and Google Apps Script. So you’ll find a generic “add a trigger” instead of the full patch to this option.
Allons-y!
The setup
- Google Form / Create a Form and activate the “collect e-mail” option. This will come in handy later on since our tickets depends on our customer having an e-mail associated with their account on Zammad and/or create a new account if no customer account already exists in our Ticket system.
- Zammad / Create your Token
Go to your Zammad installation (shortened in yzp from now on)
> Profile (low corner left)
> Token Access
> Create
> Permissions required: Ticket.Agent
> Copy your Zammad Token for later - Google Form / Go to your form and on the top right of the screen click the three dots icon to open a drop down menu. From that menu select Script editor.
- Google Form / From the left column menu select “Triggers” (at the moment is a little stopwatch icon)
- Google Form / Select your function, specify “On submit”, save. (At this point you may be required to allow some stuff for your script to work. Go ahead. Notice: since they’ll be presented as pop up windows watch for any pop-up related alert from Google Chrome or other window blocking apps).
- Go the the responses tab and open/create the google sheets that will store the answers.
A note: gSheet stores up to 5 milions cells, just keep that in mind in case you plan using this method in the long term.
The code
Be on the look out for caps lock and bold text which i’ll use to highlight where you have to edit the code with your data to make it fit your needs.
- Set up our Zammad Endpoint in the variable POST_URL
- Select the last response and store it in the response variable.
Note: In your Google Sheet you’ll find two columns: date/time and email.
This two columns are not to be considered when working with indexes, loops or arrays. In our script, column [0] it’s actually the third one.
var POST_URL = "YOUR-ZAMMAD-URL/api/v1/tickets";
function onSubmit(e) {
var form = FormApp.getActiveForm();
var allResponses = form.getResponses();
var latestResponse = allResponses[allResponses.length - 1];
var response = latestResponse.getItemResponses();The only way to access the mandatory e-mail is the following line. After that let’s start creating the ticket object.
var mail = allResponses[allResponses.length - 1].getRespondentEmail();Important: As you’ll see the “Ticket” in Zammad is the container and the Articles are the actual datas from the Google Form / Sheet.
So now we’ll create what your agent will see when she/he opens up a ticket.
Keep in mind that this will not work if you have a “attachment” where your users can upload a file but i’ll have another tutorial about that.
var ticketBody ="";
for (let i = 0; i < response.length; i++)
{
// Only needed if you have your users uploading files.
if (response[i].getItem().getType() == "FILE_UPLOAD")
{
var string = response[i].getResponse().toString();
var splitted = string.split(",");
for (let h = 0; h < splitted.length; h++)
{
ticketBody += "<br/>https://drive.google.com/open?id=" + splitted[h]; } continue; }
ticketBody += "<strong>"+response[i].getItem().getTitle() +"</strong>: " + response[i].getResponse() + "<br/>"; }At this point you may user the following function to have a preview of your ticket.
Logger.log(ticketBody);First let’s setup our variables then we’ll setup the JSON Object needed to create the article and the article container, the ticket.
/* Setup your variables hereKeep in mind that the column number 0 it is usually your 3rd column since the time stamp and the e-mail are not considered by Apps Script index functions */var group = "________";
var subject = response[2].getResponse();
var firstLastName = response[1].getResponse();
var tags = response[2].getResponse(); var article = {
"subject": subject,
"reply_to": mail,
"from": firstLastName + " <" + mail + ">",
"body": ticketBody,
"type": "web",
"sender": 2,
"internal": false,
"content_type": "text/html"
}var ticket = {
"title": subject,
"group" : group,
"article" : article,
"customer_id" : "guess:" + mail,
"tags": tags,
// "tags": "gets,ignored,anyway,noreply",
}
var options = {
"method": "post",
"contentType": "application/json",
"headers":{"Authorization":"Token token="+ MY_TOKEN},
"payload": JSON.stringify(ticket),
// "muteHttpExceptions":true
// In case you need a clean response message
} var answer = UrlFetchApp.fetch(POST_URL, options);//Logger.log(answer.getResponseCode()); };
You can get the whole code here
Sources for the Google Form part:
