n8n Workflow: Bi-directional Synchronization with Postgres and Microsoft Excel 365

This n8n workflow demonstrates the core components for setting up a bi-directional synchronization process between a PostgreSQL database and Microsoft Excel 365. It provides a foundational structure for reading data from both sources, which can then be extended with logic for comparison, updating, and inserting records to ensure data consistency.

What it does

This workflow is a template that includes the necessary nodes to initiate data retrieval from both a PostgreSQL database and a Microsoft Excel 365 spreadsheet. It's designed to be a starting point for more complex bi-directional synchronization logic.

1. Triggers: The workflow can be initiated in three ways:
   • Manually: By clicking 'Execute workflow' in the n8n editor.
   • On Schedule: Configured to run at specified intervals (e.g., every hour, daily).
   • By Another Workflow: Can be called as a sub-workflow from another n8n workflow.
2. Reads from PostgreSQL: Connects to a PostgreSQL database to fetch data.
3. Reads from Microsoft Excel 365: Connects to Microsoft Excel 365 to fetch data from a spreadsheet.
4. Includes a Code Node: Provides a placeholder for custom JavaScript logic, which would typically be used for data transformation, comparison, and decision-making during synchronization.
5. Includes a Sticky Note: For documenting specific parts or instructions within the workflow.

Prerequisites/Requirements

• n8n Instance: A running n8n instance.
• PostgreSQL Database: Access to a PostgreSQL database with appropriate credentials.
• Microsoft Excel 365 Account: An active Microsoft Excel 365 account with access to the target spreadsheet.
• n8n Credentials: Configured credentials for both PostgreSQL and Microsoft Excel 365 within your n8n instance.

Setup/Usage

1. Import the Workflow:
   • Download the provided JSON file.
   • In your n8n instance, click "New" in the workflows sidebar, then "Import from JSON".
   • Paste the JSON content or upload the file.
2. Configure Credentials:
   • Locate the "Postgres" node and click on the "Credential" field. Select an existing PostgreSQL credential or create a new one, providing your database connection details (host, port, database, user, password).
   • Locate the "Microsoft Excel 365" node and click on the "Credential" field. Select an existing Microsoft account credential or create a new one, authenticating with your Microsoft 365 account.
3. Configure Data Retrieval:
   • Postgres Node: Configure the "Postgres" node with the specific SQL query or table/resource you wish to retrieve data from.
   • Microsoft Excel 365 Node: Configure the "Microsoft Excel 365" node to specify the workbook and worksheet from which to read data.
4. Implement Synchronization Logic:
   • The "Code" node is a placeholder. You will need to add custom JavaScript code here to:
     • Compare data fetched from Postgres and Excel.
     • Identify new records, updated records, or deleted records in either source.
     • Construct payloads for updating/inserting/deleting records in both Postgres and Excel.
   • Add additional n8n nodes (e.g., IF, Item Lists, Set, Write to Excel, Write to Postgres) to implement the bi-directional sync logic based on your specific requirements.
5. Activate the Workflow:
   • Once configured and tested, activate the workflow by toggling the "Active" switch in the top right corner of the n8n editor.
   • If using the "Schedule Trigger", ensure its settings are configured to your desired frequency.