CODEWITHSUNDEEP
mysqlpush
user3611353
user3611353

Reputation: 7

Push records from Google spreadsheet to MySQL database

I want to dynamically send data to MySQL database from Google spreadsheet I am using Google app scripts but this have not managed to accomplish the task I can manage store records on the variable but unable to pass them and send to the database, below are the scripts I have;

The scripts below only add ,'" + data[i][0] + "' and 1,'" + data[i][] + "' to the database table instead of actual values

Please help me on this

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

}

Upvotes: 0

Views: 869

Answers (1)

Vlam
Vlam

Reputation: 1798

Try the below where I have modified the setString part to use just the variables:

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

Upvotes: 1

Related Questions