Web Help Desk post to Google Docs

whd_google_docs.js

// INSTRUCTIONS:
// 1. Start a new form in Google docs
// 2. Go to Tools > Script editor from the spreadhseet
// 3. Paste in the below code
// 4. Go to Triggers > Current script's triggers
// 5. Add a new trigger
// 6. Select "onFormSubmit" then "On Form Submit" from the third select box. Click save.
// 7. Save the script and go back to your spreadsheet. You should see a "WHD Options" menu.
// 8. Click WHD Options > Ticket settings and enter the API key (from WHD's user setting screen) and the URL to your "Create Ticket" post. The script will automatically append "?apiKey=xxx" to the end of this URL.
// 9. Use the email settings in the WHD Options if you want to email users with their WHD ticket. 

function onFormSubmit(e) {
  var url = ScriptProperties.getProperty('apiURL') + '?apiKey=' + ScriptProperties.getProperty('apiKey');
  var ticketBody = "";
  for(var field in e.namedValues) {
    ticketBody += field + ': ' + e.namedValues[field].toString() + "\n";
  }
  
  var ticket = { "room"          : "",
                "emailClient"    : true,
                "emailTech"      : true,
                "isPrivate"      : false,
                "sendEmail"      : false,
                "subject"        : ScriptProperties.getProperty('ticketSubject'),
                "problemtype"    : { "type": "ProblemType", "id" : ScriptProperties.getProperty('ticketProblem') },
                "location"       : { "type": "Location", "id": 1 },
                "clientReporter" : { "type": "Client", "id": 1 },
                "detail"         : ticketBody
  };

  var response = UrlFetchApp.fetch(url, {method : 'post', payload : Utilities.jsonStringify(ticket) });
  var responseBody = response.getContentText();
  var ticket = Utilities.jsonParse(responseBody);
  var ticket_id = ticket.id;
  var sheets = SpreadsheetApp.getActiveSpreadsheet().getSheets();
  sheets[0].getRange(sheets[0].getLastRow(), sheets[0].getLastColumn(), 1, 1).setValue(ticket_id);
  
  if(ScriptProperties.getProperty('emailSubject').length) {
     MailApp.sendEmail(e.namedValues['Username'].toString(), ScriptProperties.getProperty('emailSubject'), ScriptProperties.getProperty('emailBody').replace('###', ticket_id));
  }
}

function onOpen() {
  var menuOptions = [ {name:'Ticket Settings', functionName : 'formTicketSettings' }, 
                      {name:'Email Settings', functionName : 'formEmailSettings' } ];
  SpreadsheetApp.getActiveSpreadsheet().addMenu('WHD Options', menuOptions);
}

function formEmailSettings() {
  var formDoc = SpreadsheetApp.getActiveSpreadsheet();
  var formApp = UiApp.createApplication().setTitle('Web Help Desk Settings');
  var submitButton = formApp.createButton('Save Settings');
  var handler = formApp.createServerClickHandler('saveFormEmailSettings');
  
  var emailSubject = formApp.createTextBox().setName('emailSubject').setText(ScriptProperties.getProperty('emailSubject')).setWidth('400px');
  var emailSubjectformPanel = formApp.createVerticalPanel().setSpacing(10);
  var emailSubjectDescription = formApp.createLabel().setText('Enter the subject of the email sent to the client. Leave blank for no email.');
  
  handler.addCallbackElement(emailSubject);
  emailSubjectformPanel.add(emailSubjectDescription);
  emailSubjectformPanel.add(emailSubject);
  formApp.add(emailSubjectformPanel);
  
  var emailBody = formApp.createTextArea().setName('emailBody').setText(ScriptProperties.getProperty('emailBody')).setSize('400px', '150px');
  var emailBodyformPanel = formApp.createVerticalPanel().setSpacing(10);
  var emailBodyDescription = formApp.createLabel().setText('Enter the body of the email sent to the client. Leave blank for no email. Use "###" for ticket number.');
  
  handler.addCallbackElement(emailBody);
  emailBodyformPanel.add(emailBodyDescription);
  emailBodyformPanel.add(emailBody);
  formApp.add(emailBodyformPanel);

  
  submitButton.addClickHandler(handler);
  emailBodyformPanel.add(submitButton);
  
  formDoc.show(formApp);
}

function saveFormEmailSettings(e) {
  formApp = UiApp.getActiveApplication();
  ScriptProperties.setProperty('emailSubject', e.parameter.emailSubject);
  ScriptProperties.setProperty('emailBody', e.parameter.emailBody);
  formApp.close();
  return formApp;
  
}


function formTicketSettings() {
  var formDoc = SpreadsheetApp.getActiveSpreadsheet();
  var formApp = UiApp.createApplication().setTitle('Web Help Desk Settings');
  var submitButton = formApp.createButton('Save Settings');
  var handler = formApp.createServerClickHandler('saveFormTicketSettings');
  var apiKey = formApp.createTextBox().setName('apiKey').setText(ScriptProperties.getProperty('apiKey')).setWidth('400px');
  var formPanel = formApp.createVerticalPanel().setSpacing(10);
  var keyDescription = formApp.createLabel().setText('Enter the API key from your Web Help Desk user account settings.');
  
  handler.addCallbackElement(apiKey);
  formPanel.add(keyDescription);
  formPanel.add(apiKey);
  formApp.add(formPanel);
  
  var apiURL = formApp.createTextBox().setName('apiURL').setText(ScriptProperties.getProperty('apiURL')).setWidth('400px');
  var apiURLformPanel = formApp.createVerticalPanel().setSpacing(10);
  var apiURLDescription = formApp.createLabel().setText('Enter the URL of the create ticket callback.');
  
  handler.addCallbackElement(apiURL);
  apiURLformPanel.add(apiURLDescription);
  apiURLformPanel.add(apiURL);
  formApp.add(apiURLformPanel);
  
  var ticketSubject = formApp.createTextBox().setName('ticketSubject').setText(ScriptProperties.getProperty('ticketSubject')).setWidth('400px');
  var ticketSubjectformPanel = formApp.createVerticalPanel().setSpacing(10);
  var ticketSubjectDescription = formApp.createLabel().setText('Enter the subject of the ticket.');
  
  handler.addCallbackElement(ticketSubject);
  ticketSubjectformPanel.add(ticketSubjectDescription);
  ticketSubjectformPanel.add(ticketSubject);
  formApp.add(ticketSubjectformPanel);
  
  var ticketProblem = formApp.createTextBox().setName('ticketProblem').setText(ScriptProperties.getProperty('ticketProblem')).setWidth('400px');
  var ticketProblemformPanel = formApp.createVerticalPanel().setSpacing(10);
  var ticketProblemDescription = formApp.createLabel().setText('Enter the number of the problem type.');
  
  handler.addCallbackElement(ticketProblem);
  ticketProblemformPanel.add(ticketProblemDescription);
  ticketProblemformPanel.add(ticketProblem);
  formApp.add(ticketProblemformPanel);
  
  submitButton.addClickHandler(handler);
  ticketProblemformPanel.add(submitButton);
  
  formDoc.show(formApp);
}

function saveFormTicketSettings(e) {
  formApp = UiApp.getActiveApplication();
  ScriptProperties.setProperty('apiKey', e.parameter.apiKey);
  ScriptProperties.setProperty('apiURL', e.parameter.apiURL);
  ScriptProperties.setProperty('ticketSubject', e.parameter.ticketSubject);
  ScriptProperties.setProperty('ticketProblem', e.parameter.ticketProblem);
  formApp.close();
  return formApp;
  
}