Bidirectional Google Sheets and MySQL Data Synchronization Workflow

Google Sheets and MySQL Bidirectional Synchronization Workflow

This n8n workflow template implements a robust bidirectional synchronization system between Google Sheets (typically fed by Google Forms) and a MySQL database. Designed for managing event or concert inquiries, it ensures data consistency across both systems while enabling automated status tracking, conditional notifications, and field mapping. The workflow runs on a scheduled basis during business hours and can also be triggered manually for immediate execution.

What the Workflow Does

The core purpose of this workflow is to:

• Fetch new or updated inquiry records from a Google Sheet (e.g., responses from a Google Form).
• Retrieve corresponding records from a MySQL table (ConcertInquiries) where source_name = 'GoogleForm'.
• Compare the two datasets based on shared keys (timestamp and source_name), ignoring auto-managed fields like id, record_created, and record_updated.
• Upsert (insert or update) any new or changed Google Sheet records into the MySQL database.
• Detect changes in the DB Status field within the comparison results and propagate those updates back to the Google Sheet.
• Identify stale inquiries (older than 4 hours) that haven’t received a response and trigger a notification.
• Maintain synchronization flags in the database when records are confirmed as in sync.

Key Features and Capabilities

• Bidirectional Sync: Changes in either Google Sheets or MySQL can be reflected in the other system under specific conditions.
• Field Normalization: Automatically renames and reformats Google Form column names to standardized database-friendly field names (e.g., converting date formats to YYYY-MM-DD).
• Conditional Logic: Uses multiple IF nodes to handle different synchronization scenarios:
  • Stale inquiry detection
  • Status field updates
  • Sync confirmation
• Scheduled & Manual Triggers: Runs automatically every 30 minutes between 6 AM and 10 PM on weekdays, and supports on-demand execution.
• Data Comparison: Leverages the Compare Datasets node to intelligently detect differences while excluding system-managed columns.
• Documentation Support: Includes sticky notes with setup instructions for both the Google Form and the required MySQL table schema.

Main Nodes and Their Purposes

Use Cases and Benefits

Primary Use Case: Managing event or concert booking inquiries submitted via Google Forms, ensuring they are reliably stored in a structured MySQL database while allowing staff to update status directly in the sheet or database.

Benefits:

• Data Integrity: Prevents duplication and ensures consistent field naming across platforms.
• Operational Efficiency: Automates data entry from forms into a queryable database.
• Status Tracking: Enables team members to update inquiry status in either system, with changes reflected in both.
• Alerting Readiness: Identifies stale inquiries for follow-up (notification system can be extended from the noOp node).
• Low Maintenance: Scheduled runs reduce manual intervention; manual trigger supports debugging.

Step-by-Step Workflow Logic

1. Trigger: The workflow starts either via the Schedule Trigger (every 30 min on weekdays 6 AM–10 PM) or the Manual Trigger.
2. Data Fetch:
   • Google Sheet Data retrieves all form responses.
   • SQL Get inquiries from Google fetches all MySQL records tagged with source_name = 'GoogleForm'.
3. Normalization: The Rename GSheet variables node transforms Google Form fields into standardized JSON with proper date formatting and adds source_name = "GoogleForm".
4. Comparison: The Compare Datasets node aligns the two datasets using timestamp and source_name, outputting four streams:
   • Items to upsert into MySQL (Add MySQL records)
   • Items needing stale-check (No reply too long?)
   • Items with updated DB Status (DB Status assigned?)
   • Items ready for sync confirmation (DB Status in sync?)
5. Database Upsert: New or changed records are inserted/updated in MySQL via Add MySQL records (upsert on timestamp).
6. Conditional Processing:
   • If a record is older than 4 hours (No reply too long? → true), it flows to Send Notifications (currently a placeholder).
   • If DB Status was modified in MySQL (DB Status assigned? → true), Update GSheet status writes the new status back to the Google Sheet.
   • If the record is valid (DB Status in sync? → true), Sync MySQL data updates the source_name to "GoogleFormSync" as a sync confirmation flag.
7. Completion: All paths conclude without error, maintaining data parity between systems.

This workflow provides a scalable foundation for integrating user-submitted form data with relational databases, ideal for customer support, event management, or lead tracking systems.