google-sheetsgoogle-sheets-api

How to connect Google Sheets to Database


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?


Solution

  • 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!