Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
/**
* @OnlyCurrentDoc
*/
var MAXROWS = 1000
var SEEKWELL_J_SHORT_DATES = { day: "yyyy-MM-dd", month: "yyyy-MM", year: "yyyy", dayNum: "dd", monthNum: "MM", yearNum: "yyyy", week: "W" }
var SEEKWELL_J_TIMEZONE = "UTC"
var HOST = '35.196.130.133'
var PORT = '3306'
var USERNAME = 'apps_script_demo'
var PASSWORD = 'jkSqXKKa2Em7UBCh'
var DATABASE = 'dummy'
var DB_TYPE = 'mysql'
function goToSheet(sheetName) {
var ss = SpreadsheetApp.getActiveSpreadsheet();
ss.setActiveSheet(ss.getSheetByName(sheetName));
};
function runSql(query, options) {
var doc = SpreadsheetApp.getActiveSpreadsheet();
var sheet = doc.getActiveSheet();
var sheetName = sheet.getName();
var cell = doc.getActiveSheet().getActiveCell();
var activeCellRow = cell.getRow();
var activeCellCol = cell.getColumn();
try {
var fullConnectionString = 'jdbc:' + DB_TYPE + '://' + HOST + ':' + PORT
var conn = Jdbc.getConnection(fullConnectionString, USERNAME, PASSWORD);
console.log('query :', query)
var stmt = conn.createStatement();
stmt.execute('USE ' + DATABASE);
var start = new Date();
var stmt = conn.createStatement();
stmt.setMaxRows(MAXROWS);
var rs = stmt.executeQuery(query);
} catch (e) {
console.log(e, e.lineNumber);
Browser.msgBox(e);
return false
}
var results = [];
cols = rs.getMetaData();
console.log("cols", cols)
var colNames = [];
var colTypes = {};
for (i = 1; i <= cols.getColumnCount(); i++) {
var colName = cols.getColumnLabel(i)
colTypes[colName] = { type: cols.getColumnTypeName(i), loc: i }
colNames.push(colName);
}
var rowCount = 1;
results.push(colNames);
while (rs.next()) {
curRow = rs.getMetaData();
rowData = [];
for (i = 1; i <= curRow.getColumnCount(); i++) {
rowData.push(rs.getString(i));
}
results.push(rowData);
rowCount++;
}
rs.close();
stmt.close();
conn.close();
console.log('results', results)
var colCount = results[0].length
var rowCount = results.length
var comment = "Updated on: " + (new Date()) + "\n" + "Query:\n" + query
if (options.omitColumnNames) {
results = results.slice(1)
rowCount -= 1
}
if (options.clearColumns && sheet.getLastRow() > 0) {
var startCellRange = sheet.getRange(startCell)
sheet.getRange(startCellRange.getRow(), startCellRange.getColumn(), sheet.getLastRow(), colCount).clearContent();
}
if (options.clearSheet) {
var startCellRange = sheet.getRange(startCell)
sheet.clear({ contentsOnly: true });
}
sheet.getRange(activeCellRow, activeCellCol, rowCount, colCount).clearContent();
sheet.getRange(activeCellRow, activeCellCol, rowCount, colCount).setValues(results);
var cell = sheet.getRange(activeCellRow, activeCellCol)
cell.clearNote()
cell.setNote(comment);
sheet.setActiveRange(sheet.getRange(activeCellRow + rowCount + 1, activeCellCol))
console.log('query success!, rows = ', rowCount - 1)
}
function runSqlFromSheet() {
var doc = SpreadsheetApp.getActiveSpreadsheet();
var sql = doc.getRange('query!a2').getDisplayValue();
var options = {}
Logger.log('sql;', sql)
runSql(sql, options)
}
function onOpen() {
SpreadsheetApp.getUi()
.createMenu('SeekWell Blog')
.addItem('Run SQL', 'runSqlFromSheet')
.addToUi();
}
function launch() {
var html = HtmlService.createHtmlOutputFromFile('sidebar')
.setTitle('SeekWell');
SpreadsheetApp.getUi()
.showSidebar(html);
}
@mike-seekwell
Copy link
Author

mike-seekwell commented Feb 4, 2021

@remanifest you just need to add an options object to runSql with {clearSheet: true}

@Maxhodges
Copy link

Maxhodges commented Feb 5, 2021

how do I actually run this and pass a query?
Do I make a button and assign the script and hardcode the sql there? I'm not sure how it fits together

image

@Maxhodges
Copy link

Maxhodges commented Feb 5, 2021

do I need a supplemental file?

Exception: No HTML file named sidebar was found.

@Maxhodges
Copy link

Maxhodges commented Feb 5, 2021

Are there docs for this project somewhere?

@mike-seekwell
Copy link
Author

mike-seekwell commented Feb 5, 2021

@Maxhodges here's the post that goes thru it

https://blog.seekwell.io/mysql-to-sheets

@alan345
Copy link

alan345 commented Mar 26, 2021

Thanks for this!
I am facing an issue when I connect to MySQL 8 (https://issuetracker.google.com/issues/119660771)
Anybody found a workaround?

@mike-seekwell
Copy link
Author

mike-seekwell commented Mar 27, 2021

@alan345 I don't think Google's JDBC support MySQL 8 yet.

Shameless plug: You can try https://seekwell.io/

@rahulsinghmzp
Copy link

rahulsinghmzp commented Mar 30, 2021

I have data within my organisation Intranet. I want to bring data on our mobile through Microsoft Power Apps so that we can see parameters from our mobile phones.

I have saved all parameters in excel on my office desktop and it gets continuously updated also.

Now I want all those data in google sheet and want google sheet to update per sec as per the excel sheet on my desktop.

pls guide how to use your code.

Thanks

@andrewhenke
Copy link

andrewhenke commented Jun 25, 2021

Hey just a suggestion - don't publish database credentials, it's a bad idea :)

@mike-seekwell
Copy link
Author

mike-seekwell commented Jun 25, 2021

@XxLilBoPeepsxX it's on purpose. It's a demo database with a demo user specifically for this purpose.

@spra8560
Copy link

spra8560 commented Jul 19, 2021

How do I configure this to use with Microsoft SQL server and on a different server? Is there a way to automate it, so it pulls automatically when the sheet is loaded? Thanks.

@mike-seekwell
Copy link
Author

mike-seekwell commented Jul 20, 2021

@spra8560 Do you know your credentials? i.e. the hostname, username, password for SQL Server?

You could add an apps script trigger to run the script when the Sheet is open. We also offer scheduling (without writing code to do it) thru https://seekwell.io/

@spra8560
Copy link

spra8560 commented Jul 20, 2021

@mike-seekwell
Copy link
Author

mike-seekwell commented Jul 20, 2021

@spra8560 Hey Ramon - Could you send me a note at mike@seekwell.io? Happy to help you get set up with this.

@jade1508
Copy link

jade1508 commented Jun 7, 2022

Hi @mike-seekwell
Thanks for really helpful scripts.
In my case, I want to retrieve only 1 certain table in database. How should I edit the code?
Thanks again Mike.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment