Automate MySQL to Google Sheets Data Export with n8n Cron

Saving Time and Effort: How Sarah Automates Weekly Book Data Export

Sarah manages a growing online bookstore, relying on a MySQL database to store her books’ details. Each week, she needs to export all book data to her Google Sheets for reporting and inventory checks. Doing this manually takes her over an hour each week—copying, saving, and formatting data—which often leads to errors and inconsistencies. Sarah wishes there was a way to automate this repetitive task to reduce mistakes and free her time for more important things.

What This Automation Does

This n8n workflow automates Sarah’s task of exporting her MySQL books data to Google Sheets every week at 5 AM. Here is what happens:

• The workflow triggers automatically every week at 5 AM via a Cron node.
• The MySQL node runs a query to select all entries from the books table.
• The retrieved book data is passed to the Google Sheets node, which appends the data as new rows to a specific Google Sheet.
• This process eliminates manual copying and pasting, reducing human errors and saving about an hour of manual work per week.
• The automation ensures data consistency and timely updates to reporting sheets without Sarah lifting a finger.

Overall, this workflow frees Sarah to focus on growing her bookstore rather than tedious data handling.

Prerequisites ⚙️

• n8n account (cloud or self-hosted) 🔑
• MySQL database with accessible credentials 🔐
• Google Sheets account with OAuth access to the target sheet 📊
• Basic understanding of SQL SELECT queries to modify if needed

Step-by-Step Guide to Build This Workflow

Step 1: Create a Cron Trigger to Schedule Weekly Runs

In n8n, start by adding the Cron node.

Navigate to Nodes Panel → Search > Cron → Add Node.

Set it to trigger weekly at 5 AM by configuring:

• Mode: Every Week
• Hour: 5

This sets the workflow to start automatically every week at the specified time. You should see a summary of scheduled triggers after saving.

Common mistake: Forgetting to set the correct timezone in n8n settings can cause unexpected trigger times.

Step 2: Add the MySQL Node to Fetch Book Data

Add a MySQL node after the Cron node.

Set Operation to executeQuery and add the SQL query:

SELECT * FROM books;

This fetches all book records from the database.

Enter your MySQL credentials under the Credentials section. Use an existing MySQL credential or create new ones with hostname, username, password, and database name.

Expected outcome: When executed, the node returns an array of all books as JSON objects.

Common mistake: Incorrect database credentials or network access restrictions can cause connection errors.

Step 3: Append Data to Google Sheets

Add the Google Sheets node and connect it to the output of the MySQL node.

Choose Authentication as OAuth2 and select your Google Sheets OAuth2 credential.

Set operation to append.

Enter the specific Sheet ID where the data should be appended. Example: qwertz (replace with your actual sheet ID).

This node will append the book data rows as new entries in the Google Sheet each time the workflow runs.

Common mistake: Using an incorrect sheet ID or lacking permission will cause write failures.

Customizations ✏️

• Modify SQL Query: In the MySQL node, update the SELECT statement to filter or sort the books, e.g., SELECT * FROM books WHERE in_stock = 1; to export only available books.
• Change Trigger Time: Adjust the hour or day in the Cron node to run the workflow at a time that suits your business operations better.
• Write to Specific Sheet Tab: Add a range or sheet name parameter in the Google Sheets node if you want to write to a specific tab instead of the default.

Troubleshooting 🔧

Problem: “MySQL connection failed”

Cause: Incorrect credentials or database not accessible from n8n host.

Solution: Check and update your MySQL credentials in n8n and ensure the database server allows remote connections from your n8n environment.

Problem: “Google Sheets authentication error”

Cause: OAuth2 token expired or insufficient permissions.

Solution: Re-authenticate the Google Sheets credentials in n8n by removing and re-adding the OAuth2 connection.

Pre-Production Checklist ✅

• Verify that the Cron node triggers at the correct weekly time in your timezone.
• Test the MySQL query standalone to confirm it returns the expected data.
• Ensure Google Sheets credentials have write access to the target sheet.
• Run the workflow manually to verify data correctly transfers from MySQL to Google Sheets.

Deployment Guide

Once verified, enable the workflow in n8n to run automatically on schedule. Monitor initial runs via the n8n executions panel to confirm success.

For long-term stability, consider setting error notifications in n8n to alert you if the workflow fails.

Conclusion

By building this automation, Sarah now saves at least an hour every week she used to spend manually exporting book data. The process is error-free and hands-off, ensuring her Google Sheets always have fresh data for reporting and tracking.

Next, Sarah could automate inventory alerts, sync sales data, or generate PDF reports — all possible with n8n’s versatile nodes.

Give this workflow a try and see how much time you can reclaim from tedious manual exports!