Google Scripts to Make a Help Desk from a simple form

2,250 views
Skip to first unread message

Randy Damewood

unread,
Jun 22, 2012, 3:40:29 PM6/22/12
to Google Apps K12 Technical Forum
I just started playing around with the scripts in Google Apps. I am Not a programmer or have really done anything much more than batch files. I am looking to create a help desk system and I was looking through the Developers tutorials. They had a sample one that would do pretty much what I would need. It would email the person that made a ticket, but not me.

The sample code is

function formSubmitReply(e) {
var userEmail = e.values[3];
MailApp.sendEmail(userEmail,
"Help Desk Ticket",
"Thanks for submitting your issue. \n\nWe'll start " +
"working on it as soon as possible. \n\nHelp Desk",
{name:"Help Desk"});
}

which I got to work fine. What I would like this to do is email me saying who made the ticket, put in the problem, (say from column B), Location (say from column C).


Have any of you got something like that? Even if all it did was send me an email (I did get that work but not with the users email) that would be a start.

Thanks so much.

RD

Randy Damewood
Technology Director
Coffee County Schools
Direct Number- 931-222-1055
Central Office - 931-723-5150
Fax - 931-723-5195
Cell - 931-273-9299


Anne

unread,
Jun 22, 2012, 6:22:28 PM6/22/12
to k12ap...@googlegroups.com
I would like to see that also.

Bjorn Behrendt

unread,
Jun 22, 2012, 7:44:29 PM6/22/12
to Randy Damewood, k12ap...@googlegroups.com
You should be able to do it pretty easily using FormEmailer: https://sites.google.com/site/formemailer/


Bjorn Behrendt M.Ed ~ Never Stop Learning
   Google Apps For Education Certified Trainer
My Sites
      ~ Google Weekly ~ 34: Teacher Dashboard   (6/21/2012)
      ~ Teaching Change ~ 3: Transferring Google Docs (6/4/2012)
 ~ AskBj.net ~ Online Training and Ed Tech Resources
 ~ VTed.org ~ Vermont's Personal Learning Network

Get The Year Started With Google Apps 3 credit course Aug 13-17: http://getgoogle.edlisten.com



--
You received this message because you are subscribed to the Google
Groups "Google Apps K12 Technical Forum" group.
To post to this group, send email to k12ap...@googlegroups.com
To unsubscribe from this group, send email to
k12appstech...@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/k12appstech?hl=en?hl=en

Brian Kray

unread,
Jun 23, 2012, 9:05:53 AM6/23/12
to k12ap...@googlegroups.com
If you are using Google Help Desk form as a template (I've adapted it and had it working well) the following code stolen from the expense report tutorial to automate an email to a help desk google group.
 
// Main tutorial function:
// For each row (expense report):
//   - if it's new, email the report to a manager for approval
//   - if it has recently been accepted or denied by a manager, email the results to the employee
//   - otherwise (expense reports that have already been fully processed or old expense reports
//     that still have not been approved or rejected), do nothing
// Ideally, this function would be run every time the Approvals Spreadsheet or the Expense Report
// Spreadsheet are updated (via a Form submission) or regularly (once a day).
function onRequestSubmit() {
  // This is the Sets Spreadsheet
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheets()[0];
  // Fetch all the data from the Spreadsheet
  // getRowsData was reused from Reading Spreadsheet Data using JavaScript Objects tutorial
  var data = getRowsData(sheet);
  // For every request
  for (var i = 0; i < data.length; ++i) {
    var row = data[i];
    row.rowNumber = i + 2;
    if (!row.notification) {
      // This is a new Expense Report.
      // Email the manager to request his approval.
      sendReportToManager(row);
      // Update the state of the report to avoid email sending multiple emails
      // to managers about the same report.
      sheet.getRange(row.rowNumber, COLUMN_STATUS).setValue(row.notification);
    }
  }
}

// Sends an email to a manager to request his approval of an employee expense report.
function sendReportToManager(row) {
  var message = "<HTML><BODY>"
    + "<P>" + row.username + " submitted a Tech Request."
    + "<P>" + "<HR>"
    + "<P>" + "Name: " + row.name
    + "<P>" + "Date Submitted: " + row.timestamp
    + "<P>" + "Building: " + (row.building || "")
    + "<P>" + "Room #: " + (row.room || "")
    + "<P>" + "<HR>" 
    + "<P>" + "Computer ID Information: " + (row.computerIdInformation || "")
    + "<P>" + "Description: " + row.description
    + "<P>" + "Urgency: " + row.urgency
    + "<P>" + "<HR>"  
    + '<P>Click to access helpdesk spreadsheet <A HREF="' + HELPDESK_URL + '">here</A>.'
    + "<P>" + '<img src="https://sites.google.com/a/scfschools.com/staff-resources/_/rsrc/1317585072365/home/saints-logo/saintsimages.jpg"/>'
    + "</HTML></BODY>";
  MailApp.sendEmail("xxxxx...@domain.com", "Tech Request - " + row.building +" / "+ row.name, "", {htmlBody: message}); row.notification = STATE_MANAGER_EMAIL
}
If you want I can share a copy of the complete form if interested.  It is nothing earth shattering, just works....
 
Have a great weekend,
bk

HEBDave

unread,
Jul 12, 2012, 9:39:36 AM7/12/12
to k12ap...@googlegroups.com
It sure looks like you could do a lot of little things that could solve this, but I haven't worked with this code before. One simple solution might be to BCC your tech email address, using the information here:
https://developers.google.com/apps-script/class_mailapp#sendEmail 

Or...I believe something like the version of the code below should send a separate, additional email to you, with the information submitted from the form...but I haven't tried it. It's pretty self-explanatory -- the "for" loop steps through each piece of data from the submitted form and adds it to the body of the email that you would receive. (This could theoretically be risky if someone could submit malicious code in the form that your email client would then run.)

----------
function formSubmitReply(e) { 
  var userEmail = e.values[3]; 
  MailApp.sendEmail(userEmail, 
                    "Help Desk Ticket", 
                    "Thanks for submitting your issue. \n\nWe'll start " + 
                    "working on it as soon as possible. \n\nHelp Desk",                     
                    {name:"Help Desk"}); 

var techSupportEmailAddress = "exa...@example.org";
var techSupportMessage = userEmail + " has submitted a Help Desk Ticket issue: \n " ;
for (var i=0;i<e.length;i++)
{
techSupportMessage = techSupportMessage + "\n\n" + e.values[i];
}

  MailApp.sendEmail(techSupportEmailAddress, 
                    "Help Desk Ticket from " + userEmail, 
                     techSupportMessage);  
}  
---------- 




On Friday, June 22, 2012 2:40:29 PM UTC-5, Randy Damewood wrote:
Reply all
Reply to author
Forward
0 new messages