How to generate a ticket in Kace from a Google Form
We have a tech help Google Form that our user community uses to submit their desktop issues.
It generates an email along with populating a Google Sheet with all the information they submitted on the form.
We need to get this Google Form submission to generate a ticket in Kace. I don't know how to do the scripting so that the correct fields are mapped from the form into the Kace ticket. Any help would be appreciated.
My current script in the script editor on the Google Sheet reads as follows.
function formSubmitReply(e) {
var userEmail = e.values[3];
var sheet = SpreadsheetApp.getActiveSheet();
var lastRow = sheet.getLastRow();
// Set the status of the new ticket to 'New'.
// Column F is the Status column
sheet.getRange(lastRow, getColIndexByName("Status")).setValue("New");
// Calculate how many other 'New' tickets are ahead of this one
var numNew = 0;
for (var i = 2; i < lastRow; i++) {
if (sheet.getRange(i, getColIndexByName("Status")).getValue() == "New") {
numNew++;
}
}
MailApp.sendEmail(userEmail,
"Helpdesk Ticket #" + lastRow,
"Thanks for submitting your issue. \n\nWe'll start " +
"working on it as soon as possible. You are currently " +
"number " +
(numNew + 1) + " in the queue. \n\nD75 Tech Team.",
{name:"D75 Tech Help"});
}
function getColIndexByName(colName) {
var sheet = SpreadsheetApp.getActiveSheet();
var numColumns = sheet.getLastColumn();
var row = sheet.getRange(1, 1, 1, numColumns).getValues();
for (i in row[0]) {
var name = row[0][i];
if (name == colName) {
return parseInt(i) + 1;
}
}
return -1;
}
function emailStatusUpdates() {
var sheet = SpreadsheetApp.getActiveSheet();
var row = sheet.getActiveRange().getRowIndex();
var userEmail = sheet.getRange(row, getColIndexByName("Email Address")).getValue();
var subject = "Helpdesk Ticket #" + row;
var body = "We've updated the status of your ticket.\n\nStatus: " + sheet.getRange(row, getColIndexByName("Status")).getValue();
body += "\n\nProblem: " + sheet.getRange(row, getColIndexByName("Please describe the problem you are experiencing")).getValue();
body += "\n\nNotes: " + sheet.getRange(row, getColIndexByName("Notes")).getValue();
body += "\n\nResolution: " + sheet.getRange(row, getColIndexByName("Resolution")).getValue();
MailApp.sendEmail(userEmail, subject, body, {name:"D75 Tech Help"});
}
function onOpen() {
var subMenus = [{name:"Send Status Email", functionName: "emailStatusUpdates"}];
SpreadsheetApp.getActiveSpreadsheet().addMenu("Help Desk Menu", subMenus);
}
Answers (2)
function SubmitTicket() {
//set emailvar email = "kace@help.yourdomain.com";
//grab the response
var s = SpreadsheetApp.getActiveSheet();
var r = s.getLastRow();
//set email subject
var subj = s.getRange(r, 2).getValue();
//begin message block. set owner
var message = "@OWNER=somebody \n" +
//set category
"@CATEGORY=Printing::"+s.getRange(r, 3).getValue() + "\n" +
//set submitter
"@SUBMITTER=helpdesk \n" +
//random comment
s.getRange(r,4).getValue();
// Send the email
GmailApp.sendEmail(email, subj, message)
}
One thing to keep in mind is that the outbound email comes from the form owner's email just like any other email, and will leave a send receipt, etc. One implication is that this isn't a good solution for high volume cases where you're going to hit outbound limits and risk getting the email flagged/suspended. It also means that if that email gets deleted, or staffing arrangements change, etc., that the form might break.
Personally, I use these for niche cases, often where I'm the only one using the form and where the form is just for one thing. It's just a way faster way to make a ticket.
UPDATED 9/24: Corrected carriage returns in code block
Comments:
-
Jesse -
So am I doing this correctly?
The @variable is going to be related to Kace fields?
So am I to understand that
@Category is the Category field in Kace (that I've setup)
@Site is the Site field in Kace (that I've setup)
So in order to map from the Google sheet, I need to match up the sheets headers with the @variables?
This is my first attempt before testing. Please tell me if this is correct...
function SubmitTicket() {
//set email var email = "kaceq@mydomain.org";
//grab the response var s = SpreadsheetApp.getActiveSheet();
var r = s.getLastRow();
//set email subject var subj = s.getRange(r, 2).getValue();
//begin message block. set comment var message = "@COMMENT=Please_describe_the_problem_you_are_experiencing \n" +
//set category "@CATEGORY=Category::"+s.getRange(r, 3).getValue() + "\n" +
//set site "@SITE=School \n" +
//random comment s.getRange(r,4).getValue();
// Send the email
GmailApp.sendEmail(email, subj, message)
} - lbruschuk 5 years ago-
Well, two things. First, it looks like the editor crunched my comments onto the same line as the code that is supposed to execute, and you'll need to separate those back out. For example "//set email" was the comment—"var email = ...." is the code and it should be on the next line.
Second, the @site field will probably fail. If you're using a custom field, it is probably something like @custom_field3 or whatever. - JesseJM 5 years ago-
@Ibruschuk-- Apparently this forum doesn't allow comments more than 4 deep, so I can't reply directly to you. Things looks good except you are still missing the breaks at in the bottom block.
I updated my original post with the correct carriage returns, so it should be a better model now. - JesseJM 5 years ago
Top Answer
When generating a ticket via email you can use @variables to fill in data. For example:
@title = sheet.getRange(row, getColIndexByName("Please describe the problem you are experiencing")).getValue();
You can set other fields if you are collecting them, like category, priority, etc. Keep in mind that the fields must be user settable in order for this to work. Also, unless you can change the from address of the email address then they will have a submitter of the Google Form address, whatever that is.
Does that answer your question?
Comments:
-
Chuck - a little bit. I'll need to figure out where in the script I enter the @variables and then make sure the email that gets sent from the Google form reaches Kace. Any suggestions on where in the script I'd enter this information? - lbruschuk 5 years ago
-
I imagine you would need a new function similar to the emailStatusUpdates() one. That function would be triggered when a new submission is entered. You might have better luck asking about scripting in the Google product forums. - chucksteel 5 years ago