I need to create report to some parties in different organization related to my work, and the simplest way to process the data is in a spreadsheet. Lately I explore the Google Script, and read an article about importing data from mySQL or SQL server to Google sheet.
Here is the example of what I write in my script.
function YourFunction() {
var server = ‘[SERVER_ADDRESS_OF_YOUR_SQLSERVER]’;
var port = [YOUR_DB_PORT];
var dbName = ‘[DATABASE_NAME]’;
var username = ‘[YOUR_USERNAME]’;
var password = ‘[YOUR_PASSWORD]’;
var url = ‘jdbc:sqlserver://’+server+’:’+port+’;databaseName=’+dbName
var conn = Jdbc.getConnection(url, username, password)
var stmt = conn.createStatement();
var results = stmt.executeQuery(‘[YOUR_QUERY]’);
var metaData=results.getMetaData();
var numCols = metaData.getColumnCount();
var spreadsheet = SpreadsheetApp.getActive();
var sheet = spreadsheet.getSheetByName(‘[YOUR_SHEET_NAME]’);
sheet.clearContents();
var arr=[];
for (var col = 0; col < numCols; col++) {
arr.push(metaData.getColumnName(col + 1));
}
sheet.appendRow(arr);
while (results.next()) {
arr=[];
for (var col = 0; col < numCols; col++) {
arr.push(results.getString(col + 1));
}
sheet.appendRow(arr);
}
reslts.close();
stmt.close();
sheet.autoResizeColumns(1, numCols+1);
//}
}
You will get your query result in your Sheet, and you can proceed base on your Favorite Spreadsheet Application.
