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);
}

Good Morning Mr. Mike, Yes, I know the credentials and observed in the post where it says to use SQL server as the type.  I work for GSA (Federal Agency) and attempting to use Google Sheets to provide real time meter status for our buildings and trending data (up to a year) for all 100+ buildings in our region.   Our IT department has turned off ODBC but I can push data from Niagara (BAS Software) to SQL server via JDBC or MSSQL connector.   In the database I have configured I have 300+ tables (3-4 meters per site).   I don't mind requesting a purchase of the software/script you have, I just have to get it past the sniff test of the IT dept and has to work with what restrictions are in place.  I utilized the script you had but not sure how to set it up for each table (if I need to setup a tab for each, etc).   Please advise at your convenience.  Thanks. Ramon Spradlin On Tuesday, July 20, 2021, 06:57:14 AM CDT, mike-seekwell ***@***.***> wrote: @mike-seekwell commented on this gist. @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/ — You are receiving this because you were mentioned. Reply to this email directly, view it on GitHub, or unsubscribe.

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