How to Import Data from a MySQL Database to Google Sheets

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!

Table of Contents
  1. Prepare your MySQL server 
  2. Add the script to Google Apps Script
  3. Extra: check the table names
  4. I know an easier way out
Table of contents
Chapter 1
Chapter 2
Chapter 3
  1. Prepare your MySQL server 
  2. Add the script to Google Apps Script
  3. Extra: check the table names
  4. I know an easier way out
5 Minutes

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:

Tools option from toolbar, Script editor tool highlighted.

The Google Apps Script now looks as follows:

Google Apps Script interface.



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.

Google Apps Script, Save button


Step 4: Run the script by clicking the Run button adjacent to the save button. 

Google Apps Script, Run 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:

Execution log messages when the code runs successfully


Once you check the sheet, you will see that the data is now loaded:

Output. The data is loaded from MySQL to Google Sheets.


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


Thank you! Your submission has been received!
Oops! Something went wrong while submitting the form.

November 14, 2021

How to Import Data from a MySQL Database to Google Sheets

Google Sheet spreadsheet

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:

Tools option from toolbar, Script editor tool highlighted.

The Google Apps Script now looks as follows:

Google Apps Script interface.



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.

Google Apps Script, Save button


Step 4: Run the script by clicking the Run button adjacent to the save button. 

Google Apps Script, Run 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:

Execution log messages when the code runs successfully


Once you check the sheet, you will see that the data is now loaded:

Output. The data is loaded from MySQL to Google Sheets.


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


Suscribe to get more data and analytics tips!

Thank you! Your submission has been received!
Oops! Something went wrong while submitting the form.