Synchronize Google Sheets with PostgreSQL

• Scheduled execution (hourly).
• Connects to Google Sheets and PostgreSQL.
• Compares datasets based on specified fields (e.g., first_name).
• Supports inserting new records.
• Supports updating existing records.
• Splits out relevant fields for processing.

Setup Instructions:

1. Credentials: Ensure your Google Sheets and PostgreSQL credentials are set up in n8n.
2. Google Sheets Node: Configure the Retrieve Sheets Data node with your specific documentId and sheetName (or gid).
3. PostgreSQL Nodes: Configure the Select Rows in Postgres, Insert Rows, and Update Rows nodes with your PostgreSQL connection details, table (testing), and schema (public).
4. Field Mapping: In the Split Out Relevant Fields node, ensure the fieldToSplitOut parameter lists all the columns you wish to synchronize (e.g., first_name, last_name, town, age).
5. Comparison Logic: In the Compare Datasets node, define the mergeByFields to specify how records should be matched between the Google Sheet data and PostgreSQL data. The example uses first_name.
6. Insert/Update Logic: The Insert Rows and Update Rows nodes handle the database operations. The Update Rows node specifically uses first_name and last_name as matching keys for updates, and the columns parameter maps data from the input to the database fields.
7. Scheduling: The Schedule Trigger is set to run hourly. Adjust this interval as needed.

Node Descriptions:

• Schedule Trigger: Initiates the workflow on a defined schedule.
• Retrieve Sheets Data: Fetches data from a specified Google Sheet.
• Split Out Relevant Fields: Extracts specific fields from the incoming data.
• Select Rows in Postgres: Retrieves existing data from the PostgreSQL table to be used for comparison.
• Compare Datasets: Compares data from Google Sheets with data from PostgreSQL.
• Insert Rows: Inserts new records into the PostgreSQL table if they are not found during comparison.
• Update Rows: Updates existing records in the PostgreSQL table if changes are detected during comparison.