I'm trying to auto-populate some raw data on a sheet in my google sheets file with a query.
It doesn't look like sheets has any built in functionality to do so like Microsoft Excel does.
Am I missing something? I found one add-on that has since been discontinued and no longer works called data everywhere: https://www.dataeverywhere.com/use-database-sheets
Is there something else that has replaced that?
As referred here, you can use the JDBC services of Google Apps Scripts. You will have to write a script that populates your spreadsheet with data from the JDBC service.
This example demonstrates how to read a large number of records from the database, looping over the result set as necessary.
// Replace the variables in this block with real values.
var address = 'database_IP_address';
var user = 'user_name';
var userPwd = 'user_password';
var db = 'database_name';
var dbUrl = 'jdbc:mysql://' + address + '/' + db;
// Read up to 1000 rows of data from the table and log them.
function readFromTable() {
var conn = Jdbc.getConnection(dbUrl, user, userPwd);
var start = new Date();
var stmt = conn.createStatement();
stmt.setMaxRows(1000);
var results = stmt.executeQuery('SELECT * FROM entries');
var numCols = results.getMetaData().getColumnCount();
while (results.next()) {
var rowString = '';
for (var col = 0; col < numCols; col++) {
rowString += results.getString(col + 1) + '\t';
}
Logger.log(rowString)
}
results.close();
stmt.close();
var end = new Date();
Logger.log('Time elapsed: %sms', end - start);
}
Hope this helps!