Simple HelpDesk using Google Forms and Apps Script

Image Source

We can easily create a form/survey using Google Forms including setup the HelpDesk Form (to report issues or ask for support).

However, we expect the form can generate unique ticket numbers or send notifications to the reporter/assignee. We can cover this by writing a simple Google App Script.

The article with provide guidelines/examples of how to achieve it (Note: This is a simple project for learning purposes, you may still need to modify it for real use case).

For those who prefer read code (rather than the article), you can check the GitHub gist.

1. Setup the Form

Create the google form with the following field: Email Address, Name, Summary, Description, Booking Code, and Customer Code. Click here for the example.

At the “Responses” tab, click “View in Sheets” to generate Google Sheet

Add new columns in the Google Sheets: Ticket No (Column B), Assignee (Column I), Status, Customer Remark, and Internal Remark. Click here for the example.

2. Open Apps Script

In Google Sheets, go to the “Extensions” menu then click “Apps Script”. If you beginner at the apps script, you may get started with this tutorial. Spreadsheet.dev have complete tutorial and articles collection for apps script.

3. Send Email Notification

Apps Scripts already gear up with send email function MailApp.sendEmail()

function sendNofication(p){
  // Learn more: https://spreadsheet.dev/send-email-from-google-sheets
  MailApp.sendEmail({
    to: p.recipient,
    subject: p.subject,
    htmlBody: getHTMLBody(p),
    replyTo: SUPPORT_EMAIL, // set reply to support email instead dev email
    name: SUPPORT_NAME // set sender name to support name
  })
}

function getHTMLBody(p){
  // Consider to use html template for better style
  // Learn more: https://spreadsheet.dev/send-html-email-from-google-sheets
  return p.message + "<br \><br \>"
    + "Incident Details<br \>"
    + "<table border=1>"
    + "<tr>" + "<td>Customer Email</td><td>" + p.incident.email +"</td></tr>"
    + "<tr>" + "<td>Customer Name</td><td>" + p.incident.name +"</td></tr>"
    + "<tr>" + "<td>Incident Summary</td><td>" + p.incident.summary +"</td></tr>"
    + "<tr>" + "<td>Incident Description</td><td>" + p.incident.description +"</td></tr>"
    + "<tr>" + "<td>Booking Code</td><td>" + p.incident.bookingCode +"</td></tr>"
    + "<tr>" + "<td>Customer Code</td><td>" + p.incident.custCode +"</td></tr>"
    + "</table>"
    + "<br \><br \>"
    + "Regards,<br \>"
    + SUPPORT_NAME; 
}

Please be aware of the limitation and quota restrictions.

4. On Form Submit

On the form submit trigger, we will generate a unique ticket number and send a notification to the customer/reporter.

function onFormSubmit(e)
{
  let ticketNo = generateTicketNo();
  let email = e.namedValues["Email Address"].toString();
  
  sendNofication({
    ticketNo: ticketNo,
    recipient: email,
    subject: "You have create incident report #" + ticketNo,
    message: "Thank you for reporting the incident. Our support team will take a look at this soon and come back to you soon.", 
    incident: {
      email:  email,
      name: e.namedValues["Name"].toString(),
      summary: e.namedValues["Summary"].toString(),
      description: e.namedValues["Description"].toString(), 
      bookingCode: e.namedValues["Booking Code"].toString(),
      custCode: e.namedValues["Customer Code"].toString()
    },
  })

  // set ticketNo to column 2 (column B)
  let sh = e.range.getSheet();
  sh.getRange(sh.getLastRow(),2,1).setValues([[ticketNo]]);
}

function generateTicketNo () {
  // OG: https://gist.github.com/clayperez/0d689b02693b2e94a7d1ddea98a0571c
  var rtn = '';
  for (var i = 0; i < TICKET_NO_LENGTH; i++) {
    rtn += TICKET_NO_CHARSET.charAt(Math.floor(Math.random() * TICKET_NO_CHARSET.length));
  }
  return rtn;
}

5. On Edit Cell

On the edit trigger, we would like to notify the assignee when the ticket is assigned and notify the reporter of any status change.

function onEdit(e)
{
  let activeSheet = e.source.getActiveSheet();

  if (activeSheet.getName() === FORM_SUBMIT_SHEETNAME){
    
    let col = e.range.columnStart;
    let row = e.range.rowStart;

    // get raw data from column A to K (11 column)
    let data = activeSheet.getRange(row,1,1,11).getValues()

    let curr = data[0][col-1].toString();
    let ticketNo = data[0][1].toString();        // column B
    let custEmail = data[0][2].toString();       // column C
    let incident = {
      email:  custEmail, 
      name: data[0][3],         // column D
      summary: data[0][4],      // column E
      description: data[0][5],  // column F
      bookingCode: data[0][6],  // column G
      custCode: data[0][7]      // column H
    }

    switch(col){
      case 9:  // edit triggered on column I: Assignee
        sendNofication({
          ticketNo: ticketNo,
          recipient: curr,
          subject: "You have assign to Incident #" + ticketNo,
          message: "You have assign to Incident #" + ticketNo, 
          incident: incident,
        })

        info("Incident #" + ticketNo + ": Notify assignee " + curr)
        break;   
      case 10: // edit triggered on column J: Status
        let status = curr;
        let remarkToCust = data[0][10];

        let message = "Your Incident Report (#" + ticketNo + ") have been update to " + status + ".";
        if (remarkToCust !==  ""){
          message = message + " Remark: " + remarkToCust;
        }

        sendNofication({
          ticketNo: ticketNo,
          recipient: custEmail,
          subject: "Status Update on Incident #" + ticketNo + ": " + status,
          message: message, 
          incident: incident,
        })

        info("Incident #" + ticketNo + ": Notify reporter  " + custEmail)
        break;   
    }

  }
}

Conclusion

We can enhance the default Google Form to have more capability with Apps Script to be a more HelpDesk-like solution. Nevertheless, it can’t compete with the mature helpdesk product in the market. It can be a good choice for a temporary solution (before adopting the expensive one) or an internal division inquiry request management.