Google Sheets to PostgreSQL Data Sync
detail.loadingPreview
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 TABLESQL statements, including data type inference (e.g., currency, dates, numbers). - Dynamic Data Insertion: Creates
INSERTSQL 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 Triggernode to monitor your specific Google Sheet file. Ensure the Google Drive credentials are set up. - Set Variables: Use the
change_thisnode to define thetable_urlof your Google Sheet and thesheet_nameyou want to process. - Fetch Sheet Data: The
fetch sheet datanode 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 databaseIF 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 tablenode 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 tablenode is configured toDROP 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 querynode dynamically generates theCREATE TABLESQL statement based on the fetched sheet data. It infers column types and generates a table name likeai_table_<sheet_name>. - Create Insertion Query: The
create insertion querynode prepares theINSERTSQL statement, mapping sheet columns to database columns and formatting data according to inferred types. - Execute SQL Commands: The
create tableandperform insertionnodes execute the generated SQL queries against your PostgreSQL database using the provided credentials. - PostgreSQL Credentials: Ensure your PostgreSQL credentials are correctly configured in the
credentialssection of the relevant nodes.
Apps Used
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.
Get This Workflow
ID: 4a3142b1-104a...
About the Author
Free n8n Workflows Official
System Admin
The official repository for verified enterprise-grade workflows.
Statistics
Related Workflows
Discover more workflows you might like
Automated Daily Weather Data Fetcher and Storage
Fetches daily weather data from OpenWeatherMap API and stores it in Airtable.
Chomp Food Database API MCP Server
Exposes the Chomp Food Database API as an MCP-compatible interface for AI agents.