/build/static/layout/Breadcrumb_cap_w.png
09/10/2019 116 views

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);

}

Answer Summary:
0 Comments   [ + ] Show comments

Comments


Answer Chosen by the Author

0

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?

Answered 09/11/2019 by: chucksteel
Red Belt

  • 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?
  • 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.

Don't be a Stranger!

Sign up today to participate, stay informed, earn points and establish a reputation for yourself!

Sign up! or login

Share