如何将Google表格连接到数据库 [英] How to connect Google Sheets to Database

问题描述

我正在尝试使用查询在Google工作表文件中自动填充工作表中的一些原始数据.

看起来工作表没有像Microsoft Excel那样具有任何内置功能.<​​/p>

我错过了什么吗?我找到了一个自此停产的插件，不再在任何地方称为数据: https://www.dataeverywhere.com/use-database-sheets

还有其他替代品吗?

解决方案

JDBC服务Google Apps脚本.您将必须编写一个脚本，该脚本使用JDBC服务中的数据填充电子表格.

从数据库中读取

此示例演示如何从数据库读取大量记录，并根据需要遍历结果集.

// 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);
}

希望这会有所帮助！

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.

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!

这篇关于如何将Google表格连接到数据库的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！