CODEWITHSUNDEEP
javascriptmysqlgoogle-apps-scriptgoogle-sheetsjdbc
ZeMatteis
ZeMatteis

Reputation: 49

JDBC connection in Google Sheet

I am using the script made by Mike Seekwell [Link] for connecting a MySQL database to a Sheet, and it works.

My problem is that I need it to run always from the first cell of the first sheet, while actually it can run from every active cell of every active sheet.

How can I modify this thing?

Here is the script:

/**
 * @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 = '//host'
var PORT = '//port'
var USERNAME = '//username'
var PASSWORD = '//password'
var DATABASE = '/database'
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);
}

Upvotes: 0

Views: 205

Answers (1)

ZeMatteis
ZeMatteis

Reputation: 49

I solved the problem. Not in the most elegant way, but it is a solution...

In order to run the script always on the same sheet, I substitute two variables. The first variable to substitute is the following one:

var sheet = doc.getActiveSheet();

With this one (so it is always the first tab)

var sheet = doc.getSheets()[0];

Then the following one:

 var cell = doc.getActiveSheet().getActiveCell();

With this one (so it is possible to define from which cell the script has to start, in this example from A1)

var cell = sheet.getRange('A1');

Upvotes: 1

Related Questions