WHD 2

whd2

// 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 = "";
  var sheets = SpreadsheetApp.getActiveSpreadsheet().getSheets();
  var firstRow = sheets[0].getRange("1:1").getValues();
  for(var t in firstRow[0]) {
    if(typeof e.namedValues[firstRow[0][t]] != 'undefined') {
      ticketBody += '[b]' + firstRow[0][t] + ':[/b] ' + e.namedValues[firstRow[0][t]].toString() + "\n\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;
  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), 
                       { replyTo : ScriptProperties.getProperty('emailReplyTo') });
  }
  Browser.msgBox("Ticket Created");
}

function getActiveCells() {
  var url = ScriptProperties.getProperty('apiURL') + '?apiKey=' + ScriptProperties.getProperty('apiKey');
  var values = SpreadsheetApp.getActiveRange().getValues();
  var sheets = SpreadsheetApp.getActiveSpreadsheet().getSheets();
  var firstRow = sheets[0].getRange("1:1").getValues();
  
  for(var v in values) {
    var ticketBody = '';
    var userEmail = '';
    for(var t in firstRow[0]) {
      if(firstRow[0][t] != "" && typeof values[v][t] != 'undefined') {
        ticketBody += '[b]' + firstRow[0][t] + ':[/b] ' + values[v][t] + "\n\n";
        if(firstRow[0][t] == 'Username') {
          userEmail = values[v][t];
        }
      }
    }
    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;
    sheets[0].getRange(parseInt(SpreadsheetApp.getActiveRange().getRow()) + parseInt(v), 
                       SpreadsheetApp.getActiveRange().getLastColumn(), 
                       1, 
                       1).setValue(ticket_id);
    
    if(ScriptProperties.getProperty('emailSubject').length) {
       MailApp.sendEmail(userEmail, 
                         ScriptProperties.getProperty('emailSubject'), 
                         ScriptProperties.getProperty('emailBody').replace('###', ticket_id), 
                         { replyTo : ScriptProperties.getProperty('emailReplyTo') });
    }
  }
}

function onOpen() {
  var menuOptions = [ {name:'Ticket Settings', functionName : 'formTicketSettings' }, 
                      {name:'Email Settings', functionName : 'formEmailSettings' },
                      {name:'Re-do selected row', functionName : 'getActiveCells' }];
  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 emailReplyTo = formApp.createTextBox().setName('emailReplyTo').setText(ScriptProperties.getProperty('emailReplyTo')).setWidth('400px');
  var emailReplyToformPanel = formApp.createVerticalPanel().setSpacing(10);
  var emailReplyToDescription = formApp.createLabel().setText('Enter the reply-to email address.');  
  
  handler.addCallbackElement(emailReplyTo);
  emailReplyToformPanel.add(emailReplyToDescription);
  emailReplyToformPanel.add(emailReplyTo);
  formApp.add(emailReplyToformPanel);
  
  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('emailReplyTo', e.parameter.emailReplyTo);
  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;
  
}