Skip to content

Instantly share code, notes, and snippets.

@kamaroly
Last active December 1, 2023 01:39
Show Gist options
  • Save kamaroly/30c2f154c5e831b60e264dc3bce5e7dd to your computer and use it in GitHub Desktop.
Save kamaroly/30c2f154c5e831b60e264dc3bce5e7dd to your computer and use it in GitHub Desktop.
This Gits help you connect to your MySql Database from google sheet, Make surey ou whitelist google IPs ( see the list here https://developers.google.com/apps-script/guides/jdbc#creating_other_database_connections) then go to Tools > Scripts Editor
var server = "Your DATABASE HOST";
var databaseName = "DATABASE NAME";
var username = "password";
var password = "Username";
var port = 3306;
/**
* Open network to Database
*/
function openConnection() {
// Connection String
var url = "jdbc:mysql://" + server + ":" + port + "/" + databaseName;
// Open connection
return conn = Jdbc.getConnection(url, username, password);
}
/**
* Run Sql Query to fetch mysql Data
* and populate them in google sheet
*/
function getResults(sqlQuery){
var start = new Date(); // Get script starting time
var connection = openConnection();
var statement = connection.createStatement();
var starttime = new Date(); // Get script starting time
var results = statement.executeQuery(sqlQuery);
// Google Sheets Details
var doc = SpreadsheetApp.getActiveSpreadsheet(); // Returns the currently active spreadsheet
var cell = doc.getRange('a1');
var row = 0;
var getCount = results.getMetaData().getColumnCount(); // Mysql table column name count.
for (var i = 0; i < getCount; i++){
cell.offset(row, i).setValue(results.getMetaData().getColumnName(i+1)); // Mysql table column name will be fetch and added in spreadsheet.
}
var row = 1;
while (results.next()) {
for (var col = 0; col < results.getMetaData().getColumnCount(); col++) {
cell.offset(row, col).setValue(results.getString(col + 1)); // Mysql table column data will be fetch and added in spreadsheet.
}
row++;
}
results.close();
statement.close();
connection.close();
var end = new Date(); // Get script ending time
Logger.log('Time elapsed: ' + (end.getTime() - start.getTime())); // To generate script log. To view log click on View -> Logs.
}
/**
* Get results
*/
function fetchResults(){
var results = getResults("SELECT * FROM tables;");
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment