mysql - 将记录从 Google 电子表格推送到 MySQL 数据库

Question

我想将数据从 Google 电子表格动态发送到 MySQL 数据库我正在使用 Google 应用程序脚本，但这尚未完成任务我可以管理变量上的存储记录，但无法传递它们并发送到数据库，下面是我的脚本；

下面的脚本仅将 ,'"+ data[i][0] + "' 和 1,'"+ data[i][] + "' 添加到数据库表中，而不是实际值

请帮我解决这个问题

function writeManyRecords() {

  //var sheet = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = SpreadsheetApp.openByUrl("https://docs.google.com/spreadsheets/d/1e4o3m5F2sWvNFlMk2MN8tHYtX_WYFmFJyOjW0_AHk/edit#gid=648128284");
  var data = sheet.getDataRange().getValues();   
  //var stmt = conn.createStatement(); 

  var conn = Jdbc.getConnection('jdbc:mysql://IP_address:3306/database_name','user','password');
  conn.setAutoCommit(false);

  var start = new Date();
  var stmt = conn.prepareStatement('INSERT INTO _user_granted_authority' + '(_URI, _CREATOR_URI_USER) values (?, ?)');


  for (var i = 0; i < 5; i++) {
   stmt.setString(1,'" + data[i][0] + "');
   stmt.setString(2,'" + data[i][1] + "');
   stmt.addBatch();
 }

  var batch = stmt.executeBatch();

  Logger.log(sheet.getDataRange().getValues());

  conn.commit();
  conn.close();

  var end = new Date();

  Logger.log('Time elapsed: %sms for %s rows.', end - start, batch.length);
}

}

Answer 1

尝试下面的操作，我修改了 setString 部分以仅使用变量:

function writeManyRecords() {

  //var sheet = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = SpreadsheetApp.openByUrl("https://docs.google.com/spreadsheets/d/1e4o3m5F2sWvNFlMk2MN8tHYtX_WYscFmFJyOjW0_AHk/edit#gid=648128284");
  var data = sheet.getDataRange().getValues();   
  //var stmt = conn.createStatement(); 

  var conn = Jdbc.getConnection('jdbc:mysql://IP_address:3306/database_name','user','password');
  conn.setAutoCommit(false);

  var start = new Date();
  var stmt = conn.prepareStatement('INSERT INTO _user_granted_authority' + '(_URI, _CREATOR_URI_USER) values (?, ?)');


  for (var i = 0; i < 5; i++) {
   stmt.setString(1, data[i][0]);
   stmt.setString(2, data[i][1]);
   stmt.addBatch();
 }

  var batch = stmt.executeBatch();

  Logger.log(sheet.getDataRange().getValues());

  conn.commit();
  conn.close();

  var end = new Date();

  Logger.log('Time elapsed: %sms for %s rows.', end - start, batch.length);
}