-
-
Save mike-seekwell/c54c62e1ba7560583a84b9fe4c1fd157 to your computer and use it in GitHub Desktop.
/** | |
* @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); | |
} |
Hey just a suggestion - don't publish database credentials, it's a bad idea :)
@XxLilBoPeepsxX it's on purpose. It's a demo database with a demo user specifically for this purpose.
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.
@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 Hey Ramon - Could you send me a note at mike@seekwell.io? Happy to help you get set up with this.
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.
Hi, Thanks for the script - We secure our db by whitelisting ips - do you know where I can find which ips to whitelist to make this work?
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