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.
- How to connect to a MySQL data base
- Common errors when connecting
- Basic SQL queries for beginners
- Syncing your google sheets mysql integration
- 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.
You might find some of our other database tutorials helpful as well:
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
MySQLand you’ll get an option to use our
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
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.
- 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 internetso 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:
Once you’ve entered your connection parameters you can test the connection by hitting the
Test connectionbutton and SyncWith will make sure we can connect to your database and that the credentials provided are correct.
Incorrect Username- MySQL returned an error: Access denied for user 'doadmi'@IP Address (using password: YES) (ER_ACCESS_DENIED_ERROR)
- Showing access for user
doadmiis denied because the proper username in this example is
Incorrect Password- MySQL returned an error: Access denied for user 'doadmin'@'IP' (using password: YES) (ER_ACCESS_DENIED_ERROR)
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:
Security PermissionsThe most common reason is that the security settings on your host need to be updated to allow SyncWith to connect.
- 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
- As an example if your database is hosted in Digital Ocean you would add these two IPs to your trusted sources:
Incorrect PortBy 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
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
defaultdentered by accident
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
Userswe 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.
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.
Many tables have record created at timestamps, typically these are columns like
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'
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
limitoperator 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
select * from Users order by "createdAt" desc limit 5000
This get the users in descending order, hence the
order by "createdAt"
descwe’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"
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
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.