问题描述
我正在尝试使用查询在Google工作表文件中自动填充工作表中的一些原始数据.
看起来工作表没有像Microsoft Excel那样具有任何内置功能.
我错过了什么吗?我找到了一个自此停产的插件,不再在任何地方称为数据: 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.
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表格连接到数据库的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!