Google Sheets to PostgreSQL Data Sync

Name: Google Sheets to PostgreSQL Data Sync
Rating: 5 (5 reviews)
Author: Free N8N

Intermediate

18 nodes connected

detail.loadingPreview

Free N8N Temples

207 views

39 downloads

Data Integrationautomationdata syncdatabasegoogle sheetspostgresql

Automates the process of fetching data from Google Sheets and loading it into a PostgreSQL database.

About This Workflow

This workflow triggers when a Google Sheet file is updated. It then fetches the data from a specified sheet, dynamically generates SQL CREATE TABLE and INSERT statements based on the sheet's content and inferred data types, and executes these statements against a PostgreSQL database. This allows for efficient synchronization of spreadsheet data into a structured database.

Key Features

Google Sheets Integration: Reads data directly from specified Google Sheets.
Dynamic Table Creation: Automatically generates CREATE TABLE SQL statements, including data type inference (e.g., currency, dates, numbers).
Dynamic Data Insertion: Creates INSERT SQL statements with parameterized queries for safe and efficient data loading.
Schema Inference: Infers column types (TEXT, DECIMAL, TIMESTAMP) based on data content.
Currency Handling: Detects and formats currency values to appropriate database types.
Date Formatting: Recognizes and converts MM/DD/YYYY date strings to a TIMESTAMP format.
Table Existence Check: Prevents errors by checking if a table already exists before attempting creation.
Data Normalization: Cleans and formats data (e.g., removing currency symbols, percentage signs) before insertion.

How To Use

Google Drive Trigger: Configure the Google Drive Trigger node to monitor your specific Google Sheet file. Ensure the Google Drive credentials are set up.
Set Variables: Use the change_this node to define the table_url of your Google Sheet and the sheet_name you want to process.
Fetch Sheet Data: The fetch sheet data node will retrieve the contents of the specified Google Sheet.
Table Existence Check: The table exists? node checks if a table with the generated name (ai_table_<sheet_name>) already exists in the PostgreSQL database.
Conditional Logic: The is not in database IF node determines the next steps:
- If the table does NOT exist, it proceeds to create the table.
- If the table DOES exist, it will skip table creation and proceed to data insertion (though the current logic in the remove table node suggests it might be intended to drop the table if it exists before creating it. This should be reviewed based on desired behavior - e.g., update existing data or recreate table).
Remove Table (Optional but present): The remove table node is configured to DROP TABLE IF EXISTS. This node will execute if the IF condition is false (meaning the table does exist). Review this behavior: If you intend to update existing data or simply add new data without dropping, this node might need modification or removal.
Create Table Query: The create table query node dynamically generates the CREATE TABLE SQL statement based on the fetched sheet data. It infers column types and generates a table name like ai_table_<sheet_name>.
Create Insertion Query: The create insertion query node prepares the INSERT SQL statement, mapping sheet columns to database columns and formatting data according to inferred types.
Execute SQL Commands: The create table and perform insertion nodes execute the generated SQL queries against your PostgreSQL database using the provided credentials.
PostgreSQL Credentials: Ensure your PostgreSQL credentials are correctly configured in the credentials section of the relevant nodes.

Apps Used

automation

data sync

database

google sheets

postgresql

Workflow JSON

{
  "id": "4a3142b1-104a-427d-a0fd-09296bb8d0aa",
  "name": "Google Sheets to PostgreSQL Data Sync",
  "nodes": 18,
  "category": "Data Integration",
  "status": "active",
  "version": "1.0.0"
}

Note: This is a sample preview. The full workflow JSON contains node configurations, credentials placeholders, and execution logic.