Imagine that you really want to maximize the spreadsheet formulas in Google Sheets, but the data is from MySQL. Worry not, as here is a way you can do so! If you are wary of third-party add-ons, you can use this code in order to import data from MySQL to Google Sheets. Are you ready?
Prepare your MySQL server
Importing data from MySQL server to Google Sheets is done via the JDBC. A list of IP addresses must be whitelisted so that the JDB service will work. The list is available here. Additionally, the port number should be 1025 and above.
To learn more about the requirements, check the documentation page of JDBC.
Add the script to Google Apps Script
Step 1: Access the Script editor by clicking Tools on the main menu:
The Google Apps Script now looks as follows:
Step 2: Paste the following code, taken from Actiondesk:
var server = 'server_url';
var port = port_number;
var dbName = 'database_name';
var username = 'username';
var password = 'password';
var url = 'jdbc:mysql://'+server+':'+port+'/'+dbName;
function readData() {
var conn = Jdbc.getConnection(url, username, password);
var stmt = conn.createStatement();
var results = stmt.executeQuery('SELECT * FROM table_title’);
var metaData=results.getMetaData();
var numCols = metaData.getColumnCount();
var spreadsheet = SpreadsheetApp.getActive();
var sheet = spreadsheet.getSheetByName('Sheet1');
sheet.clearContents();
var arr=[];
for (var col = 0; col < numCols; col++) {
arr.push(metaData.getColumnName(col + 1));
}
sheet.appendRow(arr);
while (results.next()) {
arr=[];
for (var col = 0; col < numCols; col++) {
arr.push(results.getString(col + 1));
}
sheet.appendRow(arr);
}
results.close();
stmt.close();
sheet.autoResizeColumns(1, numCols+1);
}
Copy-paste this code, changing the following:
- server_url is for the url of the MySQL server
- port_number is the port number for the MySQL server
- database_name is the name of the MySQL database
- username is the username for access
- password is the corresponding password for the given username
- The url has the following syntax: jdbc:mysql://server_url+port/database_name, but in the case of the example used here, there is no need to add the port number, so the following syntax may work: jdbc:mysql://server_url/database_name
- table_title is the name of the table you want to load
The listed information will help you connect to the MySQL server.
Step 3: Save the script by clicking this button.
Step 4: Run the script by clicking the Run button adjacent to the save button.
An authorization request will pop up the first time you run it. Simply allow the script.
If everything goes well, you should get the following messages in the box below the code:
Once you check the sheet, you will see that the data is now loaded:
Extra: check the table names
The script is perfectly designed to allow you to run single-line SQL queries. If you want to check all the table names in the database, replace the query inside stmt.executeQuery with the following:
SELECT * FROM INFORMATION_SCHEMA.TABLES
This will retrieve the list of tables and their associated databases, and you can check the table name there.
An Easier Option
Do you find this requiring too much of your time? Lido provides a simple and easy solution to importing data from your e-Commerce and marketing platforms. Automatically import data from your favorite e-Commerce and marketing platforms such as Shopify, Facebook, and Google Analytics and apply Lido’s software to extract meaningful metrics from them. Get started here.
Related Articles
-Filter SQL Data Before Importing to Google Sheets
-Import PostgreSQL Database to Google Sheets
-Export SQL Data to Google Sheets
Recommended articles
More resources
