Sheets Sync - Freshdesk to Google Sheets

A Freshworks marketplace app that syncs Freshdesk ticket data to Google Sheets with OAuth authentication.

Features

• Manual Sync - Click to sync current ticket to Google Sheets
• Auto Sync - Automatically syncs on ticket create/update events
• Rich ticket rows - ID, subject, status, priority, requester, assignee, type, source, tags, created & updated
• Upsert Logic - Updates existing entries, inserts new ones
• Full Page Dashboard - Crayons data table with stats and refresh
• OAuth Integration - Secure Google Sheets access via OAuth 2.0

---

Freshworks Platform Features Used

This app demonstrates several key Freshworks platform capabilities:

1. App Locations

• Ticket Sidebar - Compact widget in the ticket details page for quick sync
• Full Page App - Standalone dashboard to view all synced data

2. Product Events

• onTicketCreate - Automatically triggered when a new ticket is created
• onTicketUpdate - Automatically triggered when a ticket is modified
• Events enable real-time sync without manual intervention

3. Serverless Functions

• Backend logic runs on Freshworks infrastructure
• No server management required
• Functions handle sync operations, data transformation, and API calls

4. Request Templates

• Pre-configured API request patterns for Google Sheets
• Support for GET, POST, and PUT methods
• Automatic OAuth token injection via <%= access_token %>
• Template variables for dynamic values like sheet range and data

5. OAuth 2.0 Integration

• Secure third-party API authentication
• Dynamic credential collection via oauth_iparams
• Freshworks manages token storage and refresh
• Users authorize via standard OAuth flow

6. Installation Parameters (iparams)

• Collect configuration during app installation
• Support for secure fields (masked inputs)
• Values accessible throughout the app
• Used for Google Sheet ID configuration

7. Data APIs

• Data Storage API - Access Freshdesk ticket data
• Retrieve ticket information (ID, subject, status, priority)
• No additional API calls needed for basic ticket data

8. Interface APIs

• Show Notifications - Display success/error messages to users
• Enhance user experience with visual feedback

9. Request Invoke

• Call serverless functions from frontend
• Enables manual sync button functionality
• Asynchronous communication between UI and backend

10. Platform Version 3.0

• Modern Freshworks platform architecture
• Module-based configuration
• Enhanced performance and capabilities

---

Folder Structure

sheets-sync-cursor/
├── app/                          # Frontend (React Meta + Crayons)
│   ├── index.html                # Ticket sidebar entry
│   ├── fullpage.html             # Full page dashboard entry
│   ├── components/               # React UI
│   ├── utils/sheets.js           # Payload + sheet parsing helpers
│   ├── public/icon.svg           # Sidebar / marketplace icon
│   └── styles/                   # sidebar.css, dashboard.css
│
├── server/                       # Backend serverless functions
│   └── server.js                 # Event handlers & API logic
│       ├── syncTicketData()      # Manual sync from sidebar
│       ├── getLastEntries()      # Fetch data for dashboard
│       ├── onTicketCreateHandler # Auto-sync on ticket create
│       └── onTicketUpdateHandler # Auto-sync on ticket update
│
├── config/                       # Configuration files
│   ├── iparams.json              # Installation parameters (Sheet ID)
│   ├── oauth_config.json         # OAuth configuration (Google)
│   │   └── oauth_iparams         # Dynamic Client ID/Secret collection
│   └── requests.json             # Google Sheets API templates
│       ├── googleSheetsAppend    # POST - Add new row
│       ├── googleSheetsUpdate    # PUT - Update existing row
│       └── googleSheetsGet       # GET - Read all data
│
├── manifest.json                 # App manifest
│   ├── modules.common            # Shared config (requests, functions)
│   │   └── location.full_page_app
│   └── modules.support_ticket    # Ticket-specific config
│       ├── location.ticket_sidebar
│       └── events (onTicketCreate, onTicketUpdate)
│
├── package.json                  # Node.js dependencies
└── README.md                     # This file

---

Configuration Overview

manifest.json

• Defines app structure, locations, events, and platform version
• Registers serverless functions and request templates
• Configures product-specific modules (common, support_ticket)

config/oauth_config.json

• OAuth 2.0 configuration for Google Sheets API
• Defines authorization and token URLs
• Specifies scopes and credential collection fields

config/requests.json

• API request templates for Google Sheets operations
• Three templates: Append (POST), Update (PUT), Get (GET)
• Uses template variables for dynamic sheet ranges

config/iparams.json

• Collects Google Sheet ID during installation
• Required parameter for targeting the correct spreadsheet

---

Installation

Prerequisites

1. Google Cloud Console project with OAuth 2.0 credentials
2. Google Sheet with columns: Ticket ID, Subject, Status, Priority, Created At
3. Freshworks Developer Kit (FDK) installed

Setup Steps

1. Clone and install dependencies

2. Configure Google OAuth redirect URIs:

   • Local: http://localhost:10001/auth/callback
   • Production: https://oauth.freshdev.io/auth/callback

3. Run locally with FDK

   • Use fdk run command

4. Configure the app:

   • Visit the custom configs page at http://localhost:10001/custom_configs
   • Enter Google Client ID and Client Secret
   • Authorize with Google
   • Enter your Google Sheet ID (the ID from your spreadsheet URL)

5. Test in Freshdesk:

   • Open a ticket with ?dev=true parameter in the URL
   • Click "Sync to Sheet" button in the ticket sidebar
   • View all synced data in the Full Page App

---

Google Sheet Format

Your Google Sheet should have these columns in Sheet1 (row 1 = headers):

A	B	C	D	E	F	G	H	I	J	K
Ticket ID	Subject	Status	Priority	Requester Email	Assignee Email	Type	Source	Tags	Created At	Updated At

Add the header row before syncing. Existing sheets with only columns A–E will still upsert by ticket ID; new columns fill on the next sync.

---

Events

Event	Trigger	Action
onTicketCreate	New ticket created	Auto-sync to sheet
onTicketUpdate	Ticket modified	Auto-sync to sheet

Note: Events only work when the app is installed as a Custom App, not during local ?dev=true testing. Use http://localhost:10001/web/test to simulate events locally.

---

Development Commands

• Validate manifest - Check for configuration errors
• Run locally - Start development server on port 10001
• Pack for submission - Create deployment package for marketplace

---

Security

• OAuth Token Management - All tokens handled securely by Freshworks platform
• Secure Fields - Sensitive credentials collected via masked input fields
• Automatic Token Injection - Access tokens automatically added to API requests
• No Frontend Exposure - Credentials never exposed in client-side code
• Platform-Level Security - Leverages Freshworks built-in security features