How to connect Google Sheets to Database

Question

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?

Answer 1

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.

Read from the database

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!