Last Updated on 3 years by Nishant
Hello friends You have landed at this article because you might be struggling to Connect Google Sheet To MySQL Server. Let me tell you that your are at the right place and you don’t need to buy any Licensed drivers to do this.
Lets Discuss how we can connect MySQL with Google Sheets using Google scripts which is a free integration and you do not have to pay . Before directly jumping onto connecting my SQL with Google Sheets we will first go through some basic topics so that it will be easier to understand technicality of this topic.
What is Google spreadsheet?
Google spreadsheet is basically in excel kind of sheets which can be used freely for personal use. Like Microsoft Excel is Google Sheets can be used to maintain data and do some number planning. It also supports some data validations, filters, drop downs, some basic formulas etc. The main advantage of using Google spreadsheet is you can save it in Google Drive share it with anyone and it is free to use without any paid licenses.
What is apps script in Google spreadsheets ?
Google their basic features available in Google spreadsheet for everyone but still some developers may find features lacking in the spreadsheet so they can use app script to extend the features of Google spreadsheet. Using app script one can extend Google spreadsheet and add custom menus validations or extending features in two sheets. Mostly people use these types of scripts for automating some functionalities to make their life easier.
Prerequisites for connecting Google sheet to MySQL Server
1. My Sql Server
Create local SQL Server you can install my SQL on your local desktop and start a server. If you are not want to host any SQL server on your laptop or desktop , then you can use https://www.freemysqlhosting.net for creating free sql server.
Starting server you have to know down following information so that you can connect it through Google spreadsheet.
- Database Host
- Database Name
- Database username
- Database password
- Port number
2. Google spreadsheet
Create a new Google spreadsheet you must log into your Google Drive then click on new spreadsheet as mentioned in the picture.
3. Basic Knowledge about scripting
You may require some basic knowledge of scripting if you want to lose some advance work or if you want to do some basic enhancement in Google spreadsheet using my SQL bend over sample code really suffice your need.
Now your database is started and you have all the credentials to connect with the database .
How To Connect Google Sheet To MySQL Server using Apps Scripts
Open Script editor in google sheets
Go to Tools-> script editor
It will open a script editor in New Tab with sample function myFunction() in it. You can declare global Variables in this file or you can write your logic in the same function. It may ask some permissions from your google account. Don’t worry just allow it.
Establish JDBC connection with newly created mysql server
Copy Given code and replace variables values like username password and host etc.
var Database_Host = “sql6.freemysqlhosting.net”
var Database_Name = “sql6427474”
var Database_username = “sql6427474”
var Database_password = “LUDpJ7LXAJ”
var Port_number = “3306”
function getConnection() {
var url = ‘jdbc:mysql://’+Database_Host+’:’+Port_number+’/’+Database_Name
var conn = Jdbc.getConnection(url, Database_username, Database_password);
Logger.log(conn);
We Have used JDBC connection here and for more information you can read Create JDBC Connection
Run and Check logs for connecting google sheets with MySQL.
Before running it may ask to provide Authentication Access for running scripts. So just provide access and proceed further.
Now you are ready to run Any Sql query on your database. It could be Select , Create table, insert or anything.
Run Query and Create Table in Mysql
Run a sample query to check if google sheet is able to read data from my sql server using sql queries. Cortana school query first we have to create a statement from connection did we have to call statement.execute() to execute any sql query. Hello is the sample code given to execute SQL query if Google Apps script using JDBC connection.
var stmt = conn.createStatement();
stmt.execute(“CREATE TABLE CUSTOMERS(ID INT NOT NULL, NAME VARCHAR (20) NOT NULL,AGE INT NOT NULL);”)
stmt.close()
Do not forget to close the statement after execute because it may cause some issues or data discrepancy
People Also Liked :
Best Premium True Wireless Earbuds for Both IOS and Android
Plus Size Alternative Clothing for women
Conclusion
In conclusion we can say that app script is very powerful scripting to be provided in Google spreadsheets using which we can enhance or extend the Google Sheets capability and do lot of automations my SQL is one of the extended feature which can be used using apps script. Using this article and examples new and you can night Lord of SQL scripts and do lot of stuff with Google Sheets. hope you have like our particle how to connect Google Sheets with my SQL Server. Do provide your comments and suggestions.
happy learning!!
