Get Data from SQL to Google Sheet - Trainocate Blog

Get Data from SQL to Google Sheet

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.

Posts created 1

Leave a Reply

Your email address will not be published. Required fields are marked *

Begin typing your search term above and press enter to search. Press ESC to cancel.

Back To Top