SyncWith

Get started

MySQL to Google Sheets

Want to move MySQL data into Google Sheets so you can use pivot tables, filter, chart, and leverage other useful features of Google Sheets, connecting mysql and Google Sheets makes a lot of sense.
This way you can avoid writing and modifying complex sql statements, or users who can’t write complex sql statements can instead use the power of Google Sheets. Sheets lets many users look at and analyze data using tools they’re comfortable with.

MySQL to Google Sheets - What We’ll Cover

  • How to connect to a MySQL data base
  • Common errors when connecting
  • Basic SQL queries for beginners
  • Syncing your google sheets mysql integration

Don’t have SyncWith?

  • SyncWith is a Google Sheets Add On
  • SyncWith allows you to move data from any API into Google Sheets including databases like MySQL
  • ⭐️⭐️⭐️⭐️⭐️ Reviewed by hundreds of Google businesses
  • Check out our Google Workspace Listing and see why we’re the highest rated API add on with over 100,000 installs. Install today and get your data into Sheets in minutes.

How Does SyncWith Work?

notion image
You might find some of our other database tutorials helpful as well:

Setting up your MySQL to Google Sheets Integration

To connect MySQL to Google Sheets we’ll be using the SyncWith plugin. SyncWith makes it super easy to connect 1000s of APIs and databases to Google Sheets. It’s trusted by over 100,000 businesses and it’s easy to use.
Once you have the SyncWith addon installed you can search for MySQL and you’ll get an option to use our MySQL Connector
In the gif to the left you can see a search for database - which brings up many options:
  • BigQuery to Google Sheets
  • Postgres to Google Sheets
  • Notion database to Google Sheets
  • Etc.
If we just search for MySQL then we quickly get the connector we want, click and we’re ready to create our connection
Connecting to a MySQL database is super easy but if you’ve never connected to a database before and your dev team is opening up the resource to you to use in a Google Sheets it’s worth an overview of what the connection requires.
 
notion image

Configuring you Google Sheets MySQL Connector

  • Give the connection a name - for instance if you’re connecting to your users table to do some analysis of your users in Sheets you might call your connection Customer Records - because syncwith supports multiple connections this helps you distinguish between each connector. For instance you might have a users table as well as leads table and you could pull both of them into a Google Sheet for analysis
  • Input your username and password - You’ll probably need to talk to your IT / Devops / Database Administrator to get a username and password with read permissions to the database and table you want to access
  • Host is the location of the database - if your host is on your local machine SyncWith cannot connect to it currently it needs to be accessible to the internet so you would need to punch through a port or your router and map it to your machine (which in a corporate setting isn’t likely feasible)
  • Configure your Port which for MySQL is typically 3306
  • Finally you’ll need to provide the database you’re connecting to, it’s possible you’ll want to connect to multiple databases in your Google Sheet so this allows each connection your create to work with a specific database.
 
PRO TIP - You can use the connection string to enter in all the above information in one shot. Often you can simply ask your database administrator or dev team for a connection string and simply enter it here. It leaves less room for error when connecting your MySQL database to Google Sheets.
Connection strings will look like:
mysql://username:password@host.site.com:25060/defaultdb?ssl-mode=REQUIRED

Testing you MySQL and Google Sheet Connection

Once you’ve entered your connection parameters you can test the connection by hitting the Test connection button and SyncWith will make sure we can connect to your database and that the credentials provided are correct.
notion image
 
notion image
 

Common Errors when testing your MySQL and Sheets Connection

ER_ACCESS_DENIED_ERROR

  1. Incorrect Username - MySQL returned an error: Access denied for user 'doadmi'@IP Address (using password: YES) (ER_ACCESS_DENIED_ERROR)
    1. Showing access for user doadmi is denied because the proper username in this example is doadmin
  1. Incorrect Password - MySQL returned an error: Access denied for user 'doadmin'@'IP' (using password: YES) (ER_ACCESS_DENIED_ERROR)
    1. Here we can verify that the user name is correct but access is still denied
    2. In this instance it was because the password was incorrect

      3. ETIMEDOUT

       
You may get the message Timeout trying to connect, please ensure network security is not blocking the connection (ETIMEDOUT) - this indicates that while trying to connect we timeout - meaning we kept trying and could not connect to the host. This could be happening for a few reasons:
  1. Security Permissions The most common reason is that the security settings on your host need to be updated to allow SyncWith to connect.
    1. Typically databases will specify trusted sources - a list of ip addresses or ranges that are allowed to access the database. In the addon you’ll see the SyncWith Static IP Addresses you’ll need to register
        2. notion image
    2. As an example if your database is hosted in Digital Ocean you would add these two IPs to your trusted sources:
        3. notion image
  1. Incorrect Port By default most MySQL instillations will use port 25061 but that could be different for your server or you may have accidentally deleted or missentered the port

ER_BAD_DB_ERROR

If you get the message MySQL returned an error: Unknown database 'defaultd' (ER_BAD_DB_ERROR) then you’ve specified a database that doesn’t exist. In this example the proper name of the database is defaultdb versus defaultd entered by accident

Basic SQL Queries for Beginners

Now that you’ve successfully connected to your database it’s time to write a simple SQL query to get MySQL data into Google Sheets. If you’re going to be using Sheets to filter your database data then we just really need to get all the data from a table. If we have a table called Users we would do that as follows:
Select * from "Users"
If your Users table is relatively small relative to the limits of Sheets - eg maybe Users is 5000 rows big with 20 Columns - 100k cells in total - this is completely fine for Google Sheets - dump the data in as is and use Pivot Tables, Filter Views and Slicers to play with the data.

Basic Filtering

But some data sets are quite large and it isn’t reasonable to get all the records from all time into a Google Sheet from MySQL. In this instance you likely want to filter.

Filtering by Timestamp

Many tables have record created at timestamps, typically these are columns like createdAt, created_at, CreatedAt. For big datasets where you only want to say look at Users created in the last year we can change the query. As an example lets find users created after Sept 1, 2022.
select * from Users where "createdAt" >= '2022-09-01  00:00:00'

Filtering by # of Records with Ordering

Maybe you just want to get the last 1000 or 10,000 records and you’re more concerned with getting as much in as possible for Sheets to analyze, you know you can strip out old records. To limit the number of records coming back we can use the limit operator in SQL.
select * from Users limit 5000
Now we’ll only get 5000 users, BUT WAIT - which 5000 users will we get, we want to set an ordering, because maybe we want the 5,000 most recent users, for that we’ll use the order by operator.
select * from Users order by "createdAt" desc limit 5000
This get the users in descending order, hence the order by "createdAt" desc we’re sorting the list of users in descending order based on their createdAt timestamp, aka newest users first. If you want the oldest users first you’d change it to ascending order, order by "createdAt" asc.
By filtering, ordering and limiting your query you can dump in the data that is important to you from a MySQL database table into Google Sheets and then use Sheets to do more advanced filtering and analysis - perfect for folks not skilled with SQL

Syncing your Google Sheets MySQL Integration

Now that we have the data we want coming in we need to set a sync schedule - eg maybe you want today’s orders or new users sync’d every hour. SyncWith allows you to set your sync schedule.
notion image