How to Create a Ticketing System with Google Forms

// Place this in cell A2 (assuming A1 is your header)
=ARRAYFORMULA(IF(ISBLANK(B2:B),, 
  "TID-" & TEXT(SEQUENCE(COUNTA(B2:B)), "0000")
))

// Shows all open tickets with their details
=FILTER('Form Responses 1'!A2:J, 
  'Form Responses 1'!H2:H="Open"
)

// Calculates average time to close tickets (in hours)
=AVERAGE(FILTER(
  ('Form Responses 1'!K2:K - 'Form Responses 1'!B2:B) * 24, 
  'Form Responses 1'!H2:H="Closed"
))

function onFormSubmit(e) {
  // Configuration
  const supportEmail = "support@yourcompany.com";
  const formResponse = e.namedValues;
  
  // Generate unique ticket ID
  const ticketId = "TID-" + Utilities.getUuid().substring(0, 8);
  
  // Get submitted data
  const userEmail = formResponse['Email Address'][0];
  const subject = formResponse['Subject'][0];
  const priority = formResponse['Priority Level'][0];
  const description = formResponse['Description'][0];
  
  // Update spreadsheet with ticket ID
  const sheet = SpreadsheetApp.getActiveSpreadsheet()
    .getSheetByName("Form Responses 1");
  const lastRow = sheet.getLastRow();
  sheet.getRange(lastRow, 1).setValue(ticketId);
  
  // Email templates
  const userEmailHtml = `
    <div style="font-family: Arial, sans-serif; max-width: 600px;">
      <h2 style="color: #303adc;">Ticket Received</h2>
      <p>Thank you for contacting support. We've received your request.</p>
      
      <div style="background: #f8f9fa; padding: 20px; border-radius: 10px; margin: 20px 0;">
        <p><strong>Ticket ID:</strong> ${ticketId}</p>
        <p><strong>Subject:</strong> ${subject}</p>
        <p><strong>Priority:</strong> ${priority}</p>
      </div>
      
      <p>We'll respond within 24 hours. For urgent issues, please call our hotline.</p>
    </div>
  `;
  
  // Send emails
  MailApp.sendEmail({
    to: userEmail,
    subject: `Ticket Created: ${ticketId}`,
    htmlBody: userEmailHtml
  });
  
  // Notify support team
  MailApp.sendEmail({
    to: supportEmail,
    subject: `New ${priority} Priority Ticket: ${subject}`,
    htmlBody: `New ticket submitted. <a href="${SpreadsheetApp.getActiveSpreadsheet().getUrl()}">View in Sheet</a>`
  });
}