GoogleForm to ZenDesk and GoogleDoc

_READMD.md

Purpose of the Code

This code is to ensure sensitive data is not directly contained within a ZenDesk ticket (But instead documented in Google Drive) and to automate the process of handling responses from a Google Form submission, creating a document based on a template, and generating a ticket in Zendesk (a customer service software).

Starting Point - onFormSubmit Function:

• This function is triggered when a form is submitted.
• It first checks if the form submission is valid. If not, it logs a message and stops.
• It then collects the responses from the form and stores them in a structured format (formData).

Processing Form Responses:

• The code loops through each response, categorizing them based on their title (like 'Type of Ticket' and 'Ticket Owner').
• It identifies if the ticket is a 'Change' type and stores the relevant information.

Validation Check:

• It checks if essential information like 'Type of Ticket' and 'Ticket Owner' is present. If not, it logs a message and s

Document Creation and Updating:

• The code then uses a function (createGoogleDocFromTemplateInSharedDriveFolder) to create a Google Document from a template. This document is named and stored in a specific folder.
• It replaces placeholders in the document with the actual form data.
• The document title is updated with details like the type of ticket, ticket owner, and ticket ID.

Zendesk Ticket Creation:

• A Zendesk ticket is created using the createZendeskTicket function.
• This function constructs a ticket with a subject and a message body containing a link to the Google Document.
• If the ticket type is 'Access Request', it sets a specific ticket status.

Spreadsheet Logging:

• The getSpreadsheetUrl function is used to log the URL of a spreadsheet where form responses might be recorded.

Additional Functions:

• updateTicketStatus: Updates the status of a Zendesk ticket.
• updateGoogleDocTitle: Updates the title of the Google Document.
• getTemplateIdByTypeOfTicket: Determines the template ID based on the type of ticket.

Security Note

The code includes sensitive information like a Zendesk token and email addresses, which should be securely managed.

GoogleForm to ZenDesk and GoogleDoc.gs

function onFormSubmit(e) {
    Logger.log('Event object:', e);

    if (!e || !e.response) {
        Logger.log('The function must be triggered by a form response.');
        return;
    }

    var itemResponses = e.response.getItemResponses();
    var formData = {};
    var typeOfTicket = '';
    var ticketOwner = '';
    var isChangeType = false;

    for (var i = 0; i < itemResponses.length; i++) {
        var itemResponse = itemResponses[i];
        var itemTitle = itemResponse.getItem().getTitle();
        var itemValue = itemResponse.getResponse();
        formData[itemTitle] = itemValue;

        if (itemTitle === 'Type of Ticket') {
            typeOfTicket = itemValue;
            if (itemValue === 'Change') {
                isChangeType = true;
            }
        } else if (itemTitle === 'Ticket Owner') {
            ticketOwner = itemValue;
        }
    }

    if (!typeOfTicket || !ticketOwner) {
        Logger.log('Type of Ticket or Ticket Owner not found or is empty');
        return;
    }

    var spreadsheetUrl = getSpreadsheetUrl();
    var templateId = getTemplateIdByTypeOfTicket(typeOfTicket);
    var docUrl = '';
    var ticketId = null; // Initialize ticketId to null

    // Create the Google Doc and get its URL
    var docUrl = createGoogleDocFromTemplateInSharedDriveFolder(formData, templateId, null, typeOfTicket);

    // Create a Zendesk ticket and get the ticketId
    var ticketId = createZendeskTicket(typeOfTicket, ticketOwner, docUrl, formData);

    updateGoogleDocTitle(docUrl, typeOfTicket, ticketOwner, ticketId);

    Logger.log('Zendesk Ticket ID: ' + ticketId);
    Logger.log('Spreadsheet URL: ' + spreadsheetUrl);
    Logger.log('Updated Google Doc URL: ' + docUrl);
}


function createGoogleDocFromTemplateInSharedDriveFolder(formData, templateId, ticketId, typeOfTicket) {
    var sharedDriveFolderId = '[SHAREDDRIVE_FOLDER_ID]';
    var currentDate = Utilities.formatDate(new Date(), Session.getScriptTimeZone(), "yyyy-MMM-dd");
    var docName = currentDate + " - " + formData['Type of Ticket'] + " - " + formData['Ticket Owner'];

    // Get the destination folder
    var destinationFolder = DriveApp.getFolderById(sharedDriveFolderId);

    // Copy the template document
    var template = DriveApp.getFileById(templateId);
    var doc = DocumentApp.openById(template.makeCopy(docName, destinationFolder).getId());

    // Replace placeholders in the document with form responses
    replacePlaceholdersInDocument(doc, formData, typeOfTicket);

    // Add customer name to the subject and file name
    var subject = formData['Type of Ticket'] + " - " + formData['Ticket Owner'];
    doc.setName(docName + " - Ticket #" + ticketId);

    return doc.getUrl();
}


function replacePlaceholdersInDocument(doc, formData, typeOfTicket) {
    var body = doc.getBody();
    
    if (typeOfTicket === 'Change') {
        // Replace placeholders specific to 'Change' type
        body.replaceText('{{Ticket Owner}}', formData['Ticket Owner'] || '');
        body.replaceText('{{Type of Change}}', formData['Type of Change'] || '');
        body.replaceText('{{Customer Name}}', formData['Customer Name'] || '');
        body.replaceText('{{Customer ID}}', formData['Customer ID (CID)'] || '');
        body.replaceText('{{Notes}}', formData['Notes'] || '');
    } else if (typeOfTicket === 'Access Request') {
        // Replace placeholders specific to 'Access Request' type
        body.replaceText('{{Name}}', formData['Name'] || '');
        body.replaceText('{{Email Address}}', formData['Ticket Owner'] || '');
        body.replaceText('{{Department}}', formData['Department'] || '');
        body.replaceText('{{Direct Supervisor Email}}', formData['Direct Supervisor Email'] || '');
        body.replaceText('{{System}}', formData['System'] || '');
        body.replaceText('{{Access Level}}', formData['Access Level'] || '');
        body.replaceText('{{Duration of Access}}', formData['Duration of Access'] || '');
        body.replaceText('{{Reason for Access}}', formData['Reason for Access'] || '');
        body.replaceText('{{User Agreement}}', formData['User Agreement'] || '');
    }
    // Add more 'else if' conditions for other types if needed
}

function getSpreadsheetUrl() {
    var spreadsheetId = '[SPREADSHEET_ID]';
    var sheet = SpreadsheetApp.openById(spreadsheetId).getSheets()[0];
    var lastRow = sheet.getLastRow();

    if (lastRow) {
        var sheetUrl = "https://docs.google.com/spreadsheets/d/" + spreadsheetId;
        return sheetUrl + "#gid=" + sheet.getSheetId() + "&range=A" + lastRow;
    } else {
        return "No response found";
    }
}

function createZendeskTicket(typeOfTicket, ticketOwner, docUrl, formData) {
    var zendeskUrl = 'https://[SUBDOMAIN].zendesk.com/api/v2/tickets/' + ticketId + '.json';
    var username = '[EMAIL]/token';
    var token = '[ZENDESK_API_PASSWORD_TOKEN]';

    // Construct the subject based on the type of ticket
    var subject;
    if (typeOfTicket === 'Access Request') {
        var requesterName = formData['Name'] || 'Unknown';
        subject = typeOfTicket + " - " + requesterName;
    } else {
        subject = typeOfTicket + " - " + ticketOwner;
    }

    var messageBody = "Please refer to the linked Google Document for more details: " + docUrl;

    var payload = JSON.stringify({
        "ticket": {
            "subject": subject,
            "comment": { "body": messageBody },
            "type": typeOfTicket === 'Access Request' ? 'task' : 'other_type' // Set the type to 'task' for Access Request
        }
    });

    var options = {
        "method" : "post",
        "contentType" : "application/json",
        "headers" : {
            "Authorization" : "Basic " + Utilities.base64Encode(username + ':' + token)
        },
        "payload" : payload
    };

    try {
        var response = UrlFetchApp.fetch(zendeskUrl, options);
        var responseData = JSON.parse(response.getContentText());
        var ticketId = responseData.ticket.id;

        // Check if the ticket is for 'Access Request' and then update its status
        if (typeOfTicket === 'Access Request') {
            var customStatusId = [CUSTOM_STATUS_ID]; // Custom status ID for 'Waiting for Access' Example: "19099011365655"
            updateTicketStatus(ticketId, customStatusId);
        }

        return ticketId;
    } catch (error) {
        Logger.log(error.toString());
        return null;
    }
}


function updateTicketStatus(ticketId, customStatusId) {
    var zendeskUrl = 'https://[SUBDOMAIN].zendesk.com/api/v2/tickets/' + ticketId + '.json';
    var username = '[EMAIL]/token';
    var token = 'ZENDESK_API_PASSWORD_TOKEN]';

    var payload = JSON.stringify({
        "ticket": {
            "custom_status_id": customStatusId
        }
    });

    var options = {
        "method" : "put",
        "contentType" : "application/json",
        "headers" : {
            "Authorization" : "Basic " + Utilities.base64Encode(username + ':' + token)
        },
        "payload" : payload
    };

    try {
        UrlFetchApp.fetch(zendeskUrl, options);
        // Log success or handle as needed
    } catch (error) {
        Logger.log('Error updating ticket status: ' + error.toString());
    }
}


function updateGoogleDocTitle(docUrl, typeOfTicket, ticketOwner, ticketId) {
    var currentDate = Utilities.formatDate(new Date(), Session.getScriptTimeZone(), "yyyy-MMM-dd");
    var newDocTitle = currentDate + " - " + typeOfTicket + " - " + ticketOwner + " - Ticket #" + ticketId;

    var docId = DocumentApp.openByUrl(docUrl).getId();
    var doc = DriveApp.getFileById(docId);
    doc.setName(newDocTitle);
}

function getTemplateIdByTypeOfTicket(typeOfTicket) {
    var templateId;
    switch (typeOfTicket) {
        case 'Access Request':
            templateId = '[TEMPLATE_ID]';
            break;
        case 'Incident':
            templateId = '[TEMPLATE_ID]';
            break;
        case 'Change':
            templateId = '[TEMPLATE_ID]';
            break;
        default:
            templateId = '[TEMPLATE_ID]'; // Default template
    }
    return templateId;
}