appsScriptForBlog.js

/**
 * @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);
}

@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?